Change references from one sheet to another

[nobiai]

Hello there,

I have an issue and couldn't find a related argument already solved, kindly ask if somebody can help.

On a Worksheet I have to update datas everyday, creating every day a new sheet that compares data to the previous sheet (created the day before). On some cells I have formulas that calculate the difference between the new sheet and the previous one so that I can see what happened from one day to the next one.

Example:
On Sheet <02.10.2020>

line 3, the formula is:

=B28-'01.10.2020'!B28

line 4, the formula is:

=B37-'01.10.2020'!B37

The cells where containing the formulas are consequential, but the references are not.

The 2 questions I have:

1) Is there a way, once I make a copy of the last sheet to create a new one, that the formula picks form itself the references from the previous sheet?

Something like: 

='Current sheet'!B28-'Previous Sheet'!B28

2) Is there a way, once I insert manually the formula on the first cell, to drag that formula to the following cells but maintain the skip of line references?

Something like:

="formula on cell B3" (refer to previous sheet / line 28)

-DRAG DOWN FORMULA to cells B4, B5, B6, ecc....-

B4: (refer to previous sheet / line 37)

B5: (refer to previous sheet / line 48)

ecc...

Thanks a lot to those that will answer.

[#2]

@nobiai 

You know, I can't help but wonder if there isn't another way altogether to go about this reporting.

Are you, for example, familiar with Excel Pivot Tables? A Pivot Table can report on data over time, showing results day-by -day, or week-by-week, or month-by-month in the same category.

You'd keep the data for each day all in the same database, just making sure there's a column for each row that contains the relevant date.

Not knowing exactly what happens with the data you're working with, once you've done the comparisons on a row-by-row basis, it's difficult to be certain that a different approach would even work. But let me ask if you'd even be open to that? If you were, I think you'd find it less complicated, and possibly more informative to those that need the results of the analysis.

Finally, is it possible to post a copy of the spreadsheets? Only do so if they don't contain confidential info.

[#3]

@mathetes thanks for answering.

I'll attach the file.

I'm quite confident with pivot tables, but not enough to produce them in order to give me the result I need, for example to compare one sheet to the other.

I might gonna give it a try..

[#4]

@nobiai ,,,

Q1: Is there a way, once I make a copy of the last sheet to create a new one, that the formula picks form itself the references from the previous sheet?

Copied version is just an identical to the source and if the source sheets does have any FORMULA LINKED with sheet/sheets or Workbook, will remain as previous. 

Q2: 2) Is there a way, once I insert manually the formula on the first cell, to drag that formula to the following cells but maintain the skip of line references?

 For alternate row reference, you need to work with MOD(Row(Range),Rowtopick)=0  for example,,,

=SUM(IF(MOD(ROW($A$1:$A$15),2)=0,$A$1:$A$15,0))

This will SUM value from every second Row /SUM every second value in Range A1:A15

[#5]

@nobiai 

I'm sorry, but I can't figure out how to navigate those. Part of that is language, of course, and I have to apologize for my limitations in that regard. But beyond that, we'd need to sit together face-to-face, unless you can really provide a good description of what all those sections of the various sheets are about.

[#6]

@Rajesh_Sinha 

Thanks for answering.

I'm not that smart to understand the whole hint, but I will sit on that for a while and make some practice. Hopefully I will get through that.

Thanks again

[#7 · Accepted solution]

@nobiaiIt's probably best to use "Find and Replace" (Ctrl-H) to update the links to the previous sheet. For example, when you create a new sheet for "04.10" from sheet "03.10", select all cells in the new sheet, Find "02.10!' and replace with '03.10!'. Takes about three seconds to accomplish. 

[#8]

@Riny_van_Eekelen HERO!