Discussion Change references from one sheet to another in Excel
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1734283#M76609
<P>Hello there,</P><P>I have an issue and couldn't find a related argument already solved, kindly ask if somebody can help.</P><P>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.</P><P> </P><P>Example:<BR />On Sheet <02.10.2020></P><P>line 3, the formula is:</P><P><STRONG>=B28-'01.10.2020'!B28</STRONG></P><P>line 4, the formula is:</P><P><STRONG>=B37-'01.10.2020'!B37</STRONG></P><P> </P><P>The cells where containing the formulas are consequential, but the references are not.</P><P> </P><P>The 2 questions I have:</P><P> </P><P>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?</P><P>Something like: </P><P><STRONG>='Current sheet'!B28-'Previous Sheet'!B28</STRONG></P><P> </P><P>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?</P><P>Something like:</P><P><STRONG>="formula on cell B3" (refer to previous sheet / line 28)</STRONG></P><P><STRONG>-DRAG DOWN FORMULA to cells B4, B5, B6, ecc....-</STRONG></P><P><STRONG>B4: (refer to previous sheet / line 37)</STRONG></P><P><STRONG>B5: (refer to previous sheet / line 48)</STRONG></P><P><STRONG>ecc...</STRONG></P><P> </P><P>Thanks a lot to those that will answer.</P><P> </P>Thu, 01 Oct 2020 12:29:49 GMTnobiai2020-10-01T12:29:49ZChange references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1734283#M76609
<P>Hello there,</P><P>I have an issue and couldn't find a related argument already solved, kindly ask if somebody can help.</P><P>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.</P><P> </P><P>Example:<BR />On Sheet <02.10.2020></P><P>line 3, the formula is:</P><P><STRONG>=B28-'01.10.2020'!B28</STRONG></P><P>line 4, the formula is:</P><P><STRONG>=B37-'01.10.2020'!B37</STRONG></P><P> </P><P>The cells where containing the formulas are consequential, but the references are not.</P><P> </P><P>The 2 questions I have:</P><P> </P><P>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?</P><P>Something like: </P><P><STRONG>='Current sheet'!B28-'Previous Sheet'!B28</STRONG></P><P> </P><P>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?</P><P>Something like:</P><P><STRONG>="formula on cell B3" (refer to previous sheet / line 28)</STRONG></P><P><STRONG>-DRAG DOWN FORMULA to cells B4, B5, B6, ecc....-</STRONG></P><P><STRONG>B4: (refer to previous sheet / line 37)</STRONG></P><P><STRONG>B5: (refer to previous sheet / line 48)</STRONG></P><P><STRONG>ecc...</STRONG></P><P> </P><P>Thanks a lot to those that will answer.</P><P> </P>Thu, 01 Oct 2020 12:29:49 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1734283#M76609nobiai2020-10-01T12:29:49ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1736201#M76654
<P><LI-USER uid="816305"></LI-USER> </P><P> </P><P>You know, I can't help but wonder if there isn't another way altogether to go about this reporting.</P><P> </P><P>Are you, for example, familiar with Excel <STRONG>Pivot Tables</STRONG>? 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.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>Finally, is it possible to post a copy of the spreadsheets?<EM> Only do so if they don't contain confidential info.</EM></P>Thu, 01 Oct 2020 17:49:24 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1736201#M76654mathetes2020-10-01T17:49:24ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1741740#M76827
<P><LI-USER uid="425987"></LI-USER> thanks for answering.</P><P>I'll attach the file.</P><P>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.</P><P>I might gonna give it a try..</P>Sat, 03 Oct 2020 10:14:18 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1741740#M76827nobiai2020-10-03T10:14:18ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1741826#M76835
<P><LI-USER uid="816305"></LI-USER> ,,,</P><P> </P><P><EM>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?</EM></P><P> </P><P>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. </P><P> </P><P><EM>Q2: <SPAN>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?</SPAN></EM></P><P> </P><P><SPAN> For alternate row reference, you need to work with <EM>MOD(Row(Range),Rowtopick)=0 </EM> for example,,,</SPAN></P><P> </P><P><SPAN>=SUM(IF(MOD(ROW($A$1:$A$15),2)=0,$A$1:$A$15,0)) </SPAN></P><P> </P><P><SPAN>This will SUM value from every second Row /SUM every second value in Range A1:A15</SPAN></P>Sat, 03 Oct 2020 11:00:03 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1741826#M76835Rajesh-S2020-10-03T11:00:03ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1742208#M76845
<P><LI-USER uid="816305"></LI-USER> </P><P> </P><P>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.</P>Sat, 03 Oct 2020 16:39:04 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1742208#M76845mathetes2020-10-03T16:39:04ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1743081#M76899
<P><LI-USER uid="93699"></LI-USER> </P><P>Thanks for answering.</P><P>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.</P><P>Thanks again</P>Sun, 04 Oct 2020 11:02:15 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1743081#M76899nobiai2020-10-04T11:02:15ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1743617#M76912
<P><LI-USER uid="816305"></LI-USER>It'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. </P>Sun, 04 Oct 2020 18:42:28 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1743617#M76912Riny_van_Eekelen2020-10-04T18:42:28ZRe: Change references from one sheet to another
https://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1744721#M76950
<P><LI-USER uid="403176"></LI-USER> HERO!</P>Mon, 05 Oct 2020 08:59:17 GMThttps://techcommunity.microsoft.com/t5/excel/change-references-from-one-sheet-to-another/m-p/1744721#M76950nobiai2020-10-05T08:59:17Z