Help with updating formula based on TODAY

[Kakatal]

Is there a way in Excel to do the following?

I have a spreadsheet that is used to track people, their monthly weights & calculate their Body Mass Index (BMI). Each month is a new column for their weights.

ColumnA B C D E F
Name BMI Height DEC05 JAN05 FEB06
user1 23.7 70.0 165.00

The BMI formula is as follows: =(D3/(C3*C3))*703
Each month I have to modify the BMI formula to point the corresponding weight value.

Is there a way I can modify this formula to compare TODAY with the value in the column heading to automatically have the BMI formula point to the correct month? Thanks for your help,

Kevin

 

[tmcfadden]

If your date headers are formated as DATE (with the first of the month as the day), the following might work for your BMI function:

=(HLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),2:256,2,FALSE)/(C3*C3))*703

The "2:256" in the formula means "All cell in rows 2 through 256". This will work as long as you don't have more than 254 clients (rows).

You probably want to narrow that down a bit.

It's a tad awkward though. What if you don't get the weigh-in's at 12:00am on the first of the month? The data will all show '0' until you enter in a weight.

I think a better way would be to choose the month and/or reconfigure the table so it would make more sense.

-Tim

 

[barry houdini]

I make the same assumption about your date headers as tmcfadden, assuming these headers go on until the end of next year....

=LOOKUP(TODAY(),$D$2:P3)/(C3*C3)*703

copy down column

....or.....ignoring the date this formula would just take the last value in the row (your future months need to be blank not zero)

=LOOKUP(9.99999999999999E+307,D3:P3)/(C3*C3)*703

 

[Kakatal]

Thanks Barry!

The 2nd formula was exactly what I was looking for.
You help is much appreciated!