# 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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,168,117
Messages
5,857,483
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back