Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Some wierd and wonderful macro required
Hi all, sorry about the subject line but I don't know how to define the
problem in a short way. Basically, a work buddy has a spreadsheet with the following criteria Column C contains a manually input date which displays a maturity date of a service in the following format dd/mm/yyyy Column D COntains a value for the service in a two decimal currency Assume there are 10 rows of each When I run the macro, I would like to have a cell display the sum of the total values. Now the interesting bit. If I run the macro and the date value of colum C is less than TODAY's date, I would like to miss leave out the value field for that particular row. Ideally, turning that row RED also would be fantastic as it would immediately show matured services without having to look to closely. Thanks in advance Mal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Some wierd and wonderful macro required
=SUMIF(C:C,"="&TODAY(),D:D)
to turn it red , select all the rows (assuming starting at 2) and use conditional formatting with a condition of Is Formula, and a formula of =D2<TODAY() -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "malycom" wrote in message ... Hi all, sorry about the subject line but I don't know how to define the problem in a short way. Basically, a work buddy has a spreadsheet with the following criteria Column C contains a manually input date which displays a maturity date of a service in the following format dd/mm/yyyy Column D COntains a value for the service in a two decimal currency Assume there are 10 rows of each When I run the macro, I would like to have a cell display the sum of the total values. Now the interesting bit. If I run the macro and the date value of colum C is less than TODAY's date, I would like to miss leave out the value field for that particular row. Ideally, turning that row RED also would be fantastic as it would immediately show matured services without having to look to closely. Thanks in advance Mal |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Some wierd and wonderful macro required
Mal,
No macro needed. =SUMIF(C:C, "=" & TODAY(),D:D) will return the sum you want. Select all of column C, and Use Format / Conditional Formatting.... with the option Cell Value is.. Between, and use 1 as the lower value and a cell reference (let's say cell E2) where that cell (E2) has the formula =TODAY()-1 Set your background as red, and you'll get the matured dates highlighted. HTH, Bernie MS Excel MVP "malycom" wrote in message ... Hi all, sorry about the subject line but I don't know how to define the problem in a short way. Basically, a work buddy has a spreadsheet with the following criteria Column C contains a manually input date which displays a maturity date of a service in the following format dd/mm/yyyy Column D COntains a value for the service in a two decimal currency Assume there are 10 rows of each When I run the macro, I would like to have a cell display the sum of the total values. Now the interesting bit. If I run the macro and the date value of colum C is less than TODAY's date, I would like to miss leave out the value field for that particular row. Ideally, turning that row RED also would be fantastic as it would immediately show matured services without having to look to closely. Thanks in advance Mal |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Some wierd and wonderful macro required
Thanks Bob & Bernie
Solution worked brilliantly - I wish I was good at this stuff. Regards Mal "malycom" wrote: Hi all, sorry about the subject line but I don't know how to define the problem in a short way. Basically, a work buddy has a spreadsheet with the following criteria Column C contains a manually input date which displays a maturity date of a service in the following format dd/mm/yyyy Column D COntains a value for the service in a two decimal currency Assume there are 10 rows of each When I run the macro, I would like to have a cell display the sum of the total values. Now the interesting bit. If I run the macro and the date value of colum C is less than TODAY's date, I would like to miss leave out the value field for that particular row. Ideally, turning that row RED also would be fantastic as it would immediately show matured services without having to look to closely. Thanks in advance Mal |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Some wierd and wonderful macro required
Mal,
Solution worked brilliantly - I wish I was good at this stuff. You'll get better - we all started from scratch... Bernie MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro acting wierd with Zip Codes | Excel Programming | |||
Email is a wonderful thing? | Excel Programming | |||
Email is a wonderful thing? | Excel Programming | |||
I have a wonderful Template but no way to use it | Excel Discussion (Misc queries) |