+ Reply to Thread
Results 1 to 6 of 6

Reference of Previous sheet in current sheet cell.

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    delhi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Exclamation Reference of Previous sheet in current sheet cell.

    Hi everyone,

    I've a monthly sale data in a workbook. The figures in Sale column is YTD(cumulative). Therefore I have to deduct previous month YTD sale in current YTD figures to get the current month sale. Is there any formula so that I can get it easily.

    When I use (=C6-Jan!D6) in the d6 column in MAR sheet It still takes the reference of Jan sheet But I want to take reference of FEB sheet.

    Sample file is enclosed herewith.

    Thanks.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Reference of Previous sheet in current sheet cell.

    This is how Excel works when copying from SHEET to SHEET - how is it to know you want the reference/sheet address to change? If it's too inconvenient to change the sheet reference and copy it down 10 times (once for each month from March to December) you could put each month's data on the same sheet -that way, when you copy your formula across using relative references, it will copy it in the way you want it to.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Registered User
    Join Date
    11-24-2012
    Location
    delhi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Reference of Previous sheet in current sheet cell.

    Hi,
    thanks for your time.
    But it was only sample data, Actually I have 52 sheets containing YTD figures for Sale, Purchases, exp., zones etc.

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Reference of Previous sheet in current sheet cell.

    hi ranjeet.bhagat57, you can have a list somewhere to do a VLOOKUP.
    Feb Jan
    Mar Feb
    Apr Mar
    May Apr
    Jun May
    Jul Jun
    Aug Jul
    Sep Aug
    Oct Sep
    Nov Oct
    Dec Nov

    i placed this in the "Jan" worksheet, G1:H11. you can place it anywhere else. so if my current worksheet is Feb, it'll read Jan on the 2nd column.
    =C6-INDIRECT(VLOOKUP(RIGHT(CELL("filename",$A$1),3),Jan!$G$1:$H$11,2,0)&"!D"&ROW(D6))

    you can also choose not to put the list anywhere & make it fixed like this:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-24-2012
    Location
    delhi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Reference of Previous sheet in current sheet cell.

    Hi benishiryo,

    Thanks.

  6. #6
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Reference of Previous sheet in current sheet cell.

    Quote Originally Posted by ranjeet.bhagat57 View Post
    Hi,
    thanks for your time.
    But it was only sample data, Actually I have 52 sheets containing YTD figures for Sale, Purchases, exp., zones etc.
    No problem, ranjeet - glad you got sorted out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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