Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Duplicate sheets with formulas

I am trying to duplicate 2 sheets within my workbook and have them work together in the way the original two sheets do.


The duplicate sheets' formulas are always referring to the original in which it was duplicated from. It seem that Find and Replace does not work in formulas so I do not know a quick way to change the name of the sheet in the formulas of the duplicate sheet, to reference the other new duplicate sheet.


If I have SHEET1 and SHEET2, and SHEET1 contains a lookup formula for data in SHEET2, I want to duplicate these two sheets so that SHEET3 contains a lookup formula for data in SHEET4. The problem I am having is that the lookup formula in SHEET3 is looking in SHEET2, instead of SHEET4, and I dont want to have to change all of the formulas manually.


Thanks

MacBook Air, iOS 11.2.6

Posted on

Reply

Mar 5, 2018 6:05 PM in response to nels195 In response to nels195

Hi Nels,


Formulas in Numbers do not 'look in Sheets'


Formulas reference Cells (or ranges of Cells) in Tables. If you have more than one Table with the same name in a Document, then Numbers needs the Sheet name of the Sheet containing the Table whose Cell(s) the formula references.


When you duplicate a Sheet, the only difference between the copy and the original is the Sheet name.


If you instead, duplicate a pair of tables, you make a copy of both tables (each with a new name) and you copy the relationships between the two tables. The original pair continue to contain only references to each other, and the duplicate tables also contain only references to each other.


An example:


Original tables, both on Sheet 1, and both selected:

User uploaded file

After pressing command-D, then dragging the copies into an empty space below the originals, and after editing the content of columns A and B of Table 1 - 1:

User uploaded file

Last steps:

  • Select and Copy the two duplicate tables.
  • Click the + sign to add a new Sheet.
  • On the new Sheet:
    • (Select and) Delete the default Table 1 that is provided automatically by Numbers.
    • Paste the duplicate tables onto the empty sheet.
    • Drag them into the positions your want.
  • On the original sheet

    (select and) Delete the duplicate tables.

Done.


Regards,

Barry

Mar 5, 2018 6:05 PM

Reply Helpful

Mar 5, 2018 7:04 PM in response to nels195 In response to nels195

HI nels,

"I figured out a similar way, thanks for the help."


Good! I had started examining further possibilities, but saw notification of this post while I was at an impasse.


For the benefit of anyone else looking for the answer to a similar question, could you please describe the solution you arrived at, including illustrations, if possible, then mark the post containing that answer as "this solved my issue" (green checkmark).


The checkmark brings the marked post to the top of the discussion, where a later searcher will see it right after your question.


Regards,

Barry

Mar 5, 2018 7:04 PM

Reply Helpful
User profile for user: nels195

Question: Duplicate sheets with formulas