Good morning,

I am setting up a spreadsheet wherein I have a row for each day of a month,
with numeric values. I need to be able to setup a formula similar to:

IF(DAY(TODAY())=1,A1,"")

So that another field will automatically reference the correct cell each day
(each day has it's own cell). The problem I have is that I cannot embed
enough "IF" statements to encompass all 31 days within a month. I am not
experienced with Macros/VBA at all, but if there is an easier method to do
this using those tools, I am open.

Any advice is appreciated. Thank you in advance.

Say you have *legal* XL recognized dates in A1 to A31 (7/1/2009 to
7/31/2009).

You have values in B1 to B31.

This formula will return the value in Column B corresponding to today's
date:

=INDEX(B1:B31,MATCH(TODAY(),A1:A31,0))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Stacy C" <Stacy wrote in message
...
Good morning,

I am setting up a spreadsheet wherein I have a row for each day of a month,
with numeric values. I need to be able to setup a formula similar to:

IF(DAY(TODAY())=1,A1,"")

So that another field will automatically reference the correct cell each day
(each day has it's own cell). The problem I have is that I cannot embed
enough "IF" statements to encompass all 31 days within a month. I am not
experienced with Macros/VBA at all, but if there is an easier method to do
this using those tools, I am open.

Any advice is appreciated. Thank you in advance.

Thank you very much! This works wonderfully.

The only question I have is this:

I am setting up sheets for each month; and I have noticed that when the date
ranges are not for the current month, I get a display of "#N/A" in the
destination cell.

Is there anyway to have the cell display "0" instead?

Thank you again!

"Stacy C" wrote:

Good morning,

I am setting up a spreadsheet wherein I have a row for each day of a month,
with numeric values. I need to be able to setup a formula similar to:

IF(DAY(TODAY())=1,A1,"")

So that another field will automatically reference the correct cell each day
(each day has it's own cell). The problem I have is that I cannot embed
enough "IF" statements to encompass all 31 days within a month. I am not
experienced with Macros/VBA at all, but if there is an easier method to do
this using those tools, I am open.

Any advice is appreciated. Thank you in advance.

Try this:

=IF(ISNA(MATCH(TODAY(),A1:A31,0)),0,INDEX(B1:B31,M ATCH(TODAY(),A1:A31,0)))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Stacy C" wrote in message
...
Thank you very much! This works wonderfully.

The only question I have is this:

I am setting up sheets for each month; and I have noticed that when the date
ranges are not for the current month, I get a display of "#N/A" in the
destination cell.

Is there anyway to have the cell display "0" instead?

Thank you again!

"Stacy C" wrote:

Good morning,

I am setting up a spreadsheet wherein I have a row for each day of a
month,
with numeric values. I need to be able to setup a formula similar to:

IF(DAY(TODAY())=1,A1,"")

So that another field will automatically reference the correct cell each
day
(each day has it's own cell). The problem I have is that I cannot embed
enough "IF" statements to encompass all 31 days within a month. I am not
experienced with Macros/VBA at all, but if there is an easier method to do
this using those tools, I am open.

Any advice is appreciated. Thank you in advance.

Hi,

I'm not clear if you want to return the result for only the current date in
one cell or you want this to appear on every row. Also, I'm not sure whether
you want to return the entry in column A or really the entry in another
column.

For example, the following returns the day of the current date without
referencing any cells:

=DAY(NOW())

and the following returns the item from column B for the row of the current
day, where the entries in column are 1, 2, ...31:

=LOOKUP(DAY(NOW()),A1:A31,B1:B31)

If I wanted a column which would have the current day on the row of the
current day not just in a single cell then and still using 1, 2, 3,... in
column A

=IF(DAY(NOW())=A1,A1,"")

Now lets suppose the dates in column A are entered as legal Excel dates then
if you just want one result you would again use

=TODAY()

If you wanted it for each cell in a column (this does not need to be
adjusted for each month:

=IF(TODAY()=A1,A1,"")

If you want to return the entry from another an adjacent column based on a
legal data

=LOOKUP(TODAY(),A1:A31,B1:B31)

This needs to be modified for each month sheet:

=IF(MONTH(NOW())=MONTH(A$1),LOOKUP(TODAY(),A1:A31, B1:B31),"")
This is only entered once.

In 2007 you can handle this more elegently:

=IFERROR(LOOKUP(TODAY(),A1:A31,B1:B31),"")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Stacy C" wrote:

Good morning,

I am setting up a spreadsheet wherein I have a row for each day of a month,
with numeric values. I need to be able to setup a formula similar to:

IF(DAY(TODAY())=1,A1,"")

So that another field will automatically reference the correct cell each day
(each day has it's own cell). The problem I have is that I cannot embed
enough "IF" statements to encompass all 31 days within a month. I am not
experienced with Macros/VBA at all, but if there is an easier method to do
this using those tools, I am open.

Any advice is appreciated. Thank you in advance.