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
 

Excel Facts

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

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:
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top