2

I have a document where I create a new sheet which represents days (has to be that way). Each sheet has a reference to the same cell in the sheet that represents the previous day. So sheet "Tuesday" pulls some values from sheet "Monday" and so on. The formula is:

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33

and the result I see is something like Saldo from 1 June 2016, Amount $55.00

If I create a new sheet and name it Day05 and paste the content of the sheet Day04, is there a way to update the formula that references Day03 to Day04? Right now, the content still pulls data from sheet Day03.

In short, when I copy the content of sheet Day04, I want to see the formula update to:

            Description                              Amount

="Saldo from "&TEXT($'Day04'.C4,"DD.MM.YYYY.")        =$'Day04'.F33

Right now, the sheet name reference does not update and my Day05 sheet still pulls data from Day03

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33 //<-- WRONG is 03 ref!
5
  • This is really not possible?
    – JoeM
    Jul 13, 2016 at 12:05
  • which version of Excel do you use? The answer depends on that Jul 13, 2016 at 13:05
  • I am using Excel 2010
    – JoeM
    Jul 13, 2016 at 14:52
  • 1
    See HERE how to get the sheet name of the cell, then use Right or Mid to get the number and add 1 then concatenate it back together and use INDIRECT(). Jul 14, 2016 at 19:41
  • You are referencing cells with something like $'Day03'.C4 but I only knew about something like Day03!C4 . Is it your locale that makes you use dollar sign ($) and dot (.)? Jul 19, 2016 at 20:15

4 Answers 4

3
+25

The problem is to create a new sheet, copy the content of an existing sheet, and change references to for cells to a second existing sheet. As Satya Mishra says, that could be done manually by changing 'Day03'! to 'Day04'! after copying to a new sheet, Day05.

To make the change automatically when you copy from the previous sheet to a new one, let's restate the problem: Create a new sheet and copy the content of the previous sheet. Use the name of the new sheet to make references to the previous named sheet.

Here's a formula in the current sheet that gets the contents of cell F33 from the previous named sheet. For example, if the current sheet is Day05, the formula gets F33 from sheet Day04.

=INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33"))

How the formula works

The formula uses INDIRECT to get a cell value from the previous sheet. If the current sheet is Day05 and we want cell F33, INDIRECT needs a string that references cell F33 in sheet Day04. The string we need is 'Day04'!F33.

  1. Get the sheet name for the current sheet.
Day05         =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)
  1. Modify the formula to get the number at the end of the sheet name by changing +1 to +4. This skips the 3 characters "Day".
05            =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)
  1. Subtract 1 to get number portion of the previous sheet.
4            =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1
  1. Use TEXT to pad the number with a leading zero.
04           =TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#")
  1. Use CONCATENATE to make the new sheet name with a reference to cell F33.
'Day04'!F33  =CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33")

Using the formula

Wherever you want a cell value from the previous sheet, use the formula in place of the cell reference, and change the last string in the formula to the cell reference. For example, to use cell C4 to show the Description (notice C4 in the formula):

="Saldo from "&TEXT(INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","C4")),"DD.MM.YYYY.")

The result is Saldo from 01.06.2016.

Conditions and caveats

  • Each sheet name must end with two numbers, e.g., Day02, Day03, etc.
  • The formula won't work until the new sheet has its correct name. A wrong sheet name causes #VALUE! errors.
0

I haven't found any way to have this done automatically. But a "Search and Replace" (Ctrl+H) seems to work.

0

I found the solution thanks to Scott Craner's comment. I warn you it's gonna be a long formula.

Everywhere you got 'DayXX' with XX number you can change te XX to

&MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("y";MID(CE("filename",A1),FIND("]";CEL("filename";A1))+1,255))+1,2)-1

This finds the filename and searches for the y from day and the extracts the nummer from the filename ands subtracts 1 from it. I can explain it more if you want, just add a comment.

0

Excel adapts the references to a sheet when we change this sheet's name. All the references are renamed automatically. Let's try to solve the problem with this feature: instead of renaming the references, we will rename the sheet.

  1. Open your workbook with the sheets of the days.
  2. Create the sheet with the data for the new day (say, "Day05") as you were doing previously. At the end, copy the formulas from the Day04 sheet, no matter they still refer to Day03 after the copy, and save it. Don't close the workbook yet!
  3. Open another workbook, which may be empty, to hold a temporary reference, and move the Day05 sheet (to do this, right click Day05, choose 'Move or Copy...' and select the other workbook as destiny).
  4. Delete sheet "Day04" and change the name of sheet "Day03" to "Day04". This is the tricky part!

You can now see that the "Day05" sheet, in the new workbook, will have its references set to Day04 (from the first workbook), instead of Day03, because of the renaming. Next, let's move this sheet back to the original workbook.

  1. Close the original workbook, discarding the changes made after the last save.
  2. Reopen the workbook again and move back the sheet Day05 that is in the temporary workbook. The references are still to Day04 (from the current workbook), because we discarded the changes instead of renaming back the sheet.

Voilà! This is a trick to make Excel rename the references for us.

It may not seem easy to do at first glance, but I think it is easier to go with than using formulas or VBA, because you can control the steps without programming knowledge. Also, it is easier than renaming each reference.

Hope it helps!

EDIT 1

Of course, you can record a macro with this activity, and even adapt it, to be able to call it faster.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .