formulas change when the current month changes

Hi,
I'm using Excel 2003.

I have a spreadsheet with the following columns:

A: Number
B: Name of measure
C: Jan
D: Feb
E: Mar
F: Apr
G: May
...etc until
N: Dec
O: Target
P: % Var From Target
Q: Traffic Light

Column O has a fixed number. P and Q have formulas:
P: =IF(O5>0,(C5-O5)/O5,"")
Q: =IF(C5>=80%,"G",IF(C5<70%,"R","Y"))

Users will fill in the spreadsheet each month. I want the red cells in the formulas for column P (% Var From Target) and column Q (Traffic Light) to change monthly: to use data from one month behind the current month.

For example, C5 is the column for Jan. C5 is the cell in the formulas above (in red). That is fine for this month (Feb) since the spreadsheet is showing January's data. But next month (Mar), I would like the red cells in the two formulas above to become D5 (in Mar, I am looking at Feb's data and D5 is the data for Feb). Then in Apr, I want the red cells to change to E5 (E5 is the data for Mar). In May, I want the cell C5 to change to F5 (F5 is the data for Apr), etc.

Anyone know how I can change the 2 formulas to be one month behind the current month?

Thanks,

Thanks for writing. I guess I could update the spreadsheet every year...

So, for cell P5, instead of:
=IF(O5>0,(C5-O5)/O5,"")
I should try:
=IF(O5>0,((INDEX(C5:N5,MONTH(TODAY()-1))-O5)/O5,"")?

But I get a message: 'the formula you typed contains an error'

sorry for the delay in writing.

wow, you are amazing! thank you so much. it works! wow. very clever!!!!!

more questions if i may:

1). it's strange, but sometimes i find if i change a value in the spreadsheet, the formulas don't always update. I try clicking Save and even closing and re-opening the document, but the formulas don't refresh. the only way to force the formulas to refresh is by putting my mouse in the formula's cell and hitting Enter on the keyboard. I have to do this in each formula's cell to make each one refresh. This only happens sometimes. weird...

2). i described a spreadsheet in my first post above. there are actually 10 spreadsheets similar to that spreadsheet (different data owners fill them out--there are different measures in each spreadsheet). then i have a master spreadsheet which is a compilation of the measures in the 10 spreadsheets. the master spreadsheet doesn't contain all the months though, it only contains 2 month columns: previous month (which is column D) and current month (which is column E).

is there a way to create a link in the master spreadsheet that says, 'if the current month is Feb, go to the data owner spreadsheet and show me the value in the cell for the correct row, and put Dec's data in the master spreadsheet, column D (previous month) and put Jan's data in the master spreadsheet, column E (current month).

Also, the master spreadsheet has a graph tab. Is there a way to auto-populate the graph tables as well?

I have attached 3 spreadsheets as an example: 2 data owner spreadsheets and the master spreadsheet.

thank you so much for your help! you're an angel!

For my #1 above regarding the formulas not automatically calculating, I realized in Excel, calculations were set to 'Manual' for some reason. So I went to Tools, Options, Calculation and put it back to Automatic.

So now I'm just trying to figure out #2 above.Can a link contain a formula?

For example, in the Master spreadsheet, I could put the following in cell E6:
='[TEST_Data Owner-HA.xls]Measures - HA '!$E$5.
But instead of $E$5, I would like the formula: (INDEX(C5:P5,MONTH(TODAY())+1))

So the final would be: (this doesn't work, but it's something of what I'm looking for):
='[TEST_Data Owner-HA.xls]Measures - HA '! & (INDEX(C5:P5,MONTH(TODAY())+1))

Any ideas for me?

Thanks,

I got the link to work, but now I'm getting a #REF! error in my Master spreadsheet.

Here is an example of my link in the Master spreadsheet (both documents are on Sharepoint):

=(INDEX('http://op.ds.com/sites/DocumentLibrary1/[Data Owner-HA.xls]Measures-HA'!$C$6:'http://op.ds.com/sites/DocumentLibrary1/[Data Owner-HA.xls]Measures-HA'!$P$6,MONTH(TODAY())+1))

That formula is saying: in the Data Owner-HA.xls spreadsheet, return the value of the third column in the range of C6:P6.

Everything is fine with the formula, except I get a #REF! message in the Master spreadsheet. The only way to avoid the #REF! message is if I also have the Data Owner-HA.xls spreadsheet open. If both spreadsheets are open, then everything works great. But if I close the Data Owner-HA spreadsheet, the #REF! comes back in the Master spreadsheet.

I have other cells in the Master spreadsheet that are linked to Data Owner-HA.xls and they work fine, even if the Data Owner-HA is not open.
Ex:
='http://op.ds.com/sites/DocumentLibrary1/[Data Owner-HA.xls]Measures-HA'!$R$5.

This works fine.

Any ideas why my first link gives the #REF! error? Is it because of the Index function contained within the link?

Thanks,

Bentley Bob helped me and showed me how to use HLOOKUP.

i was told that using INDEX in a link requires the other spreadsheet to also be open.

thanks