# Help with updating formula based on TODAY

#### Kakatal

##### New Member
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

### 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

##### Board Regular
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

=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
Thanks Barry!

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

Replies
0
Views
142
Replies
1
Views
350
Replies
2
Views
132
Replies
5
Views
380
Replies
0
Views
441