Results 1 to 5 of 5

Thread: Insert New Worksheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert New Worksheet

    Hi,

    I have worksheet 1 and worksheet 2. Worksheet 2 has formulas referencing to worksheet 1.

    When I make a copy of worksheet 2 and re-name to worksheet 3, in the same workbook, the formulas are still referencing to worksheet 1.

    Manually changing the formulas in worksheet 3 is tedious and I also do not want to use the find and replace. Cause it might change fields I don't want to change.

    Is there a way to have the new worksheet referencing to worksheet 2 automatically? And when I make a copy of worksheet 3, the referencing changes to worksheet 3?

    Thanks in advance.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Insert New Worksheet

    Jonathan

    I don't know of any way to do that automatically, other than using some sort of find/replace.

    Why don't you want to use find/replace?

    What 'fields' don't you want to change?
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Feb 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Worksheet

    Thanks Norie for your prompt reply.

    Unfortunately my users are not excel users. The find and replace maybe too complicated for them. Need something simpler...

  4. #4
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Worksheet

    Try using the INDIRECT function to look at the sheet before the current one. Find a cell in all sheets (say $A$1) that you can reference to give you the sheet number of this sheet, then use something like this to reference the previous sheet:

    Code:
    =INDIRECT("Sheet"&SHEET($A$1)-1&"!Z99")
    My formula isn't doing anything except referencing Z99 in the previous sheet. You would need to adapt this for all cell references to the previous worksheet, so it would take a lot of setting up, but should be OK once you have. Or use Replace: if you select the column(s) with the formulas you want to change, it will limit the changes to the selected cells.

    Good luck!

    Helping you to Excel

  5. #5
    New Member
    Join Date
    Feb 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Worksheet

    Thanks Claries.

    I expanded the formula as follows:

    =INDIRECT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)+1,1024)-1&"!n19")

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •