Reference of Previous sheet in current sheet cell.

**ranjeet.bhagat57** · 11-25-2012, 09:40 AM

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.

**BB1972** · 11-25-2012, 10:10 AM

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.

**ranjeet.bhagat57** · 11-25-2012, 10:28 AM

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.

**BB1972** · 11-25-2012, 11:37 AM

Originally Posted by ranjeet.bhagat57

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.

**benishiryo** · 11-25-2012, 10:30 AM

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:

=C6-INDIRECT(VLOOKUP(RIGHT(CELL("filename",$A$1),3),{"Feb","Jan";"Mar","Feb";"Apr","Mar";"May","Apr";"Jun","May";"Jul","Jun";"Aug","Jul";"Sep","Aug";"Oct","Sep";"Nov","Oct";"Dec","Nov"},2,0)&"!D"&ROW(D6))