+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    Missouri, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #2
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    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. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

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

    This is my best guess at what you want.
    Attached Files Attached Files
    Last edited by newdoverman; 07-25-2013 at 08:25 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    07-23-2013
    Location
    Missouri, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.

    Good luck with your report.

  7. #7
    Registered User
    Join Date
    07-23-2013
    Location
    Missouri, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #8
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

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

    Quote Originally Posted by erj4443 View Post
    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. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto Collapse/Expand a Date Pivot table based on current date
    By Dave27 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-03-2013, 12:24 PM
  2. [SOLVED] Reference of Previous sheet in current sheet cell.
    By ranjeet.bhagat57 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-25-2012, 11:37 AM
  3. Changing reference sheet based on current quarter?
    By GarnetBlack32 in forum Excel General
    Replies: 2
    Last Post: 02-05-2012, 05:19 PM
  4. Reference current date and compare it to multiple cells
    By ieatbred in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 01-31-2011, 06:54 PM
  5. Reference current date with desired number
    By frank26003 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2010, 03:28 PM

Bookmarks

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