Philip Sterling

Q: Prevent references from updating when deleting/replacing sheets

Howdy!  I have a large Numbers spreadsheet with numerous identical sheets containing billing info for independent jobs.  I also have a summary sheet which references (and aggregates) the values computed on each individual sheet.

 

I've been making incremental improvements to the individual job sheets, which are easy to duplicate x 20, however that breaks the references contained on the summary sheet, forcing me to update all 20 individual sheets with the exact same info.  I'd like to be able to make changes to a single sheet, delete the other 19, then duplicate the new sheet 19 times, without the summary references breaking.  Here is what I've tried:

 

Delete 19 of the 20 individual sheets; update one sheet as necessary, then duplicate x 19;  References on summary sheet are broken, even if the new sheets are named exactly as the old.

 

I also tried renaming the old sheets, then replacing with a new sheet, named identically to the old, but the reference on the summary sheet is updated to point to the renamed sheets (it's too smart for me!).

 

Any ideas are greatly appreciated!  Is there some way to turn off the automatic "follow me" updates so I can rename sheets without Numbers dynamically updating the formulas?  This is typically a nice feature, but it's the very thing I DON'T want in this situation!

Posted on Oct 2, 2016 12:38 PM

Close

Q: Prevent references from updating when deleting/replacing sheets

  • All replies
  • Helpful answers

  • by Barry,

    Barry Barry Oct 2, 2016 2:08 PM in response to Philip Sterling
    Level 7 (32,502 points)
    iWork
    Oct 2, 2016 2:08 PM in response to Philip Sterling

    Hi Philip,

     

    This worked in my test using a pair of tables with a single formula on one table (the 'summary' table for purposes of the test) doing VLOOKUP with MATCH on the other ('job') table, but I'm not sure it'll save you any work.

    1. Duplicate one of the individual job tables
    2. On the Summary table:
      1. double click a cell containing a formula referencing one of the job tables
      2. click the v beside ƒx in the formula editor
      3. choose 'convert formula to text in cell'
      4. repeat with other formulas in the table
    3. With all formulas referencing the original job tables now deactivated
      1. Delete the old job tables
      2. Revise, then duplicate the copied table
      3. Rename the new tables, using the names of the deleted tables
    4. On the Summary table:
      1. double click a cell containing the text of a formula
      2. insert a = at the beginning of the text/formula
      3. wait for Numbers to open the Formula editor and restore the formula
      4. click the green checkmark to confirm the restored formula.
      5. repeat with the other formulas on the table.

     

    Regards,

    Barry

  • by Barry,

    Barry Barry Oct 2, 2016 2:25 PM in response to Philip Sterling
    Level 7 (32,502 points)
    iWork
    Oct 2, 2016 2:25 PM in response to Philip Sterling

    Other thoughts:

     

    Instead of deleting and duplication the job table, would it work to revise one, then paste the revision into the other 19?

     

    Can you set up the Summary sheet so that the formulas on it's main table use indirect addressing to obtain the name of the table used in the summary formulas from a list on a second tabe on the summary sheet.

    When it came time to revise the job tables, you would:

    • Duplicate one job table
    • Change one of the table names in the list to match this table (to check that formulas in the revision are working)
    • Make the revisions
    • Duplicate this table 19 times
    • Delete the old tables (This should cause one or mores error message in the summary table)
    • Rename the new duplicates using the names of the old tables. (The error messages should clear)

    I have NOT tested this approach. Use it on a COPY of the file to test!

     

    Regards,

    Barry

  • by Philip Sterling,

    Philip Sterling Philip Sterling Oct 3, 2016 7:42 PM in response to Barry
    Level 1 (59 points)
    iWork
    Oct 3, 2016 7:42 PM in response to Barry

    Hi Barry,

     

    Firstly, thank you very much for the time you took to create the very detailed responses.  I've posted a screenshot of one of the "source sheets" that contain the "source tables."  I have 12 identical sheets, each with multiple tables.  The "summary sheet" contains a table which aggregates the various tables from the source sheets.

     

    Your first solution doesn't really save me any legwork.  Obviously when making improvements to my source sheets, I'd prefer just change a single sheet then duplicate it 11 times.  Right now, when I do so, I have to manually update every cell in the summary sheet table.  It's easy to duplicate the formulas on the summary sheet, however I need to update every reference (i.e. Sheet 1::Table 1::A1 can be copied, though it needs to reference the same table/cell on sheets 2-12 as appropriate).  So your first solution is just a different method of accomplishing what I've already been doing, albeit still involving lots of typing/clicking for small changes on source sheets :-)

     

    I've tried a renaming approach, as in your second solution.  Unfortunately, Numbers is too smart, and "follows" the renaming.  If I could just make the program close its eyes for a moment while I do my editing, then open them again it wouldn't notice anything changed and all references would be valid :-)  Unfortunately, if I delete "Sheet 3" (for example), then immediately replace it with a duplicate "Sheet 3," all the references were already broken upon the initial delete, and they can't be automatically repaired once the referenced sheet is available again.

     

    I'm gonna keep trying for a solution, but I really appreciate your willingness to try and help me find a solution!

     

    Philip

     

    Screen Shot 2016-10-03 at 10.29.56 PM.png

  • by Philip Sterling,

    Philip Sterling Philip Sterling Oct 3, 2016 7:56 PM in response to Philip Sterling
    Level 1 (59 points)
    iWork
    Oct 3, 2016 7:56 PM in response to Philip Sterling

    Barry,

     

    I started wondering if there's maybe a way to fill/copy formulas (via dragging) in which the Sheet is incremented (rather than the row/column).  If it's possible to lock the row and column, but then drag downward, for instance to increment the sheet but leave row/column unchanged?  Seems like I'm grabbing at straws, but maybe this is possible?  As an example, in the screenshot formula, I want to fill this formula down 12 rows, but the ONLY thing I want changed is "Trip 1", "Trip 2", Trip 3", Trip 4"..."Trip 12".  I would like the table name and row/column to remain fixed.  Thanks!

     

    Screen Shot 2016-10-03 at 10.51.26 PM.png

  • by Philip Sterling,

    Philip Sterling Philip Sterling Oct 3, 2016 9:21 PM in response to Philip Sterling
    Level 1 (59 points)
    iWork
    Oct 3, 2016 9:21 PM in response to Philip Sterling

    Or I'm thinking maybe I can do a find/replace all command on the summary sheet (finding "Sheet 2", renaming to sheet "Sheet 2temp", et al).  Then duplicate "Sheet 1" to "Sheet 2", etc.  Finally, reverse the process via find all "2temp", replacing with Sheet 2", etc.  Let me try this...

  • by Philip Sterling,

    Philip Sterling Philip Sterling Oct 3, 2016 9:22 PM in response to Philip Sterling
    Level 1 (59 points)
    iWork
    Oct 3, 2016 9:22 PM in response to Philip Sterling

    Hmm, can't figure out how to search formulas, so maybe that won't work either :-)