Can you reference cells on a different sheet based on the current date?

1. Can you reference cells on a different sheet based on the current date?

Sheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.

For example:

My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.

A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2
A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2
...

A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2
A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2

With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:

A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2
A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2
...

A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2
A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2

Is this at all possible? If so, please teach me your ways Thanks in advance for the help!

2. Re: Can you reference cells on a different sheet based on the current date?

Enter in Sheet1 cell A5...

=INDEX(Sheet2!B:B,\$B\$2-DATE(YEAR(\$B\$2),1,0)+ROW()-5)

...and copy (A5:B94).

3. Re: Can you reference cells on a different sheet based on the current date?

This is my best guess at what you want.

4. Re: Can you reference cells on a different sheet based on the current date?

Thanks guys! I really appreciate the help! My report should be up and running in no time

5. Re: Can you reference cells on a different sheet based on the current date?

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

6. Re: Can you reference cells on a different sheet based on the current date?

Great to hear that we were in some way able to help you.

7. Re: Can you reference cells on a different sheet based on the current date?

I really appreciate all the help and advice. If anyone would like to explain how these functions work in layman's terms I would be happy to read about it! I am fairly new to excel so I would like to understand how these formulas work in case I need to use them again in the future. Thanks again!

8. Re: Can you reference cells on a different sheet based on the current date?

Originally Posted by erj4443
I really appreciate all the help and advice. If anyone would like to explain how these functions work in layman's terms I would be happy to read about it! I am fairly new to excel so I would like to understand how these formulas work in case I need to use them again in the future. Thanks again!
Here's the short of my formula...

=INDEX(Sheet2!B:B,\$B\$2-DATE(YEAR(\$B\$2),1,0)+ROW()-5)

You can read up on the INDEX function in help.

The "Sheet2!B:B" part specifies the array the INDEX function will use, Column B of "Sheet2". When you copy over to column B on "Sheet1" it changes to "Sheet2!C:C" because the "B:B" part is a relative reference.

The "\$B\$2-DATE(YEAR(\$B\$2),1,0)+ROW()-5" sets the relative row number within the INDEX array. Note \$B\$2 is an absolute reference. It does not change when you copy to other cells. This part—"\$B\$2-DATE(YEAR(\$B\$2),1,0)"—returns the day number of year (because B2 is a date; Excel stores dates as serial numbers... you can look this up in Help). The "ROW()-5" part returns the application row number for whichever cell it is located and subtracts 5. Since you are starting in A5, you want to index your array by the day number of the year for the first index, then plus one for every row down. In cell A5, "ROW()-5" returns zero (0), but in cell A6 it returns 1, in cell A7 it returns 2, and so forth... and gets added to the day number of the year (of cell B2's date).

9. Re: Can you reference cells on a different sheet based on the current date?

If you click on a cell that has a formula, click on one of the functions in the formula then click on fx that is on the left of the formula bar. A dialogue will open giving the arguments that have been entered for that function in the formula. At the bottom of the dialogue box is a hyperlink that will take you to help for that function.

If you have further need of assistance, post the formula and indicate what it is that you need more help with.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1