Displaying cell depending on Date

**Beast777** · 04-24-2007, 11:05 AM

My head hurts from working on this all day. I have a large spreadsheet and in the top left corner I need to show the percent of vehicles that are not working out of our whole pool. I have a function that detects the color of the cell (Red is broke) and counts the total of red cells and then divides it by the total giving the percent and it is in G118 for Jan 1/2007, H118 for Jan 2/2007 etc. I made a function that will count Julian days from Jan 1, so for today I get 114. I know I need to display G + 114 columns but have no idea how to get that column name from this and always display the current one in the corner. Help please.

**NBVC** · 04-24-2007, 11:14 AM

If you want the address of the cell with the result you want, then perhaps?

=ADDRESS(118,COLUMN(G1)+114)

you can replace 114 with the function to calculate your Julian Date....

**Beast777** · 04-24-2007, 11:35 AM

I tried what you said and just put it in as 114 for now to see if works and I get this $DQ$118 displayed in the cell. ANy ideas?

**NBVC** · 04-24-2007, 11:46 AM

That gives you the address of the cell in row 118 that is 114 columns to the right of column G. Is that not what you wanted? Maybe I misinterpreted?

Please elaborate on your needs, indicating expected results.

Thanks.

**Beast777** · 04-24-2007, 11:51 AM

In the cell G118 is 37.5%, in cell H118 is 23.2%. What I want is to display the percent in cell A1 but which one depends on the date of today. So on Jan 1st it would show 37.5% in A1 and on Jan 2 it would show 23.2% and so on. I have figured out how to get the percent and I have figured out how to get a number that represents the amount of days past, since Jan 1. I do not know how to show the percent in the corner. What you gave me, seems to show the address of the cell, not the contents. Thanks for the help so far.

**NBVC** · 04-24-2007, 11:53 AM

How about?

=INDEX(118:118,COLUMN(G1)+114)

again, replacing 114 with the appropriate function to get 114.

**Beast777** · 04-24-2007, 01:08 PM

For now I am leaving the 114 as not to further complicate this. I tried the index statement and it says 0 for a result. If you try this on a worksheet does it work for you? I mean put a value in G118 and use that index statement?

**NBVC** · 04-24-2007, 01:12 PM

The formula I gave you should return the value found in $DQ$118 which is 114 cells right of G118. That is what I interpret your request to be. Am I getting it wrong?