Help with updating formula based on TODAY

Kakatal

New Member
Joined
Dec 8, 2005
Messages
4
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
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:
Code:
=(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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Dec 8, 2005
Messages
4
Thanks Barry!

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

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top