Copying Sheets between workbooks without links

[Air-ron]

Hello all -

In my job, we use multi-sheet workbooks to keep track of electrical loads
and panels. These workbooks are regularly copied from one project to another
as a starting point, and sheets are copied from one workbook to another when
needed as well.

To help automate part of my work, I use a vlookup function to return a load
from a list on a different sheet within the workbook. Several of the panel
Sheets can refer to this list to get their loads, but the master list can
change from project to project.

My problem is that if I copy a worksheet with my vlookup function:

=IF(G10="",0,VLOOKUP(G10,Master_Schedule!$A$1:$E$106,5,FALSE))

into another file, I want the Master_Schedule!... reference to refer to the
one in the new file, but it creates a link to the original file unless I copy
the Master_Schedule sheet along with the panel sheet.

I have been trying to find a workaround for this issue, as I really don't
want files from one project linking to another, and we're discouraged from
linking excel files between each other anyway.

Is it possible to have a cell that contains a reference to the range on the
master schedule, similar to using a pointer in "C"? or is there a more
elegant solution than that?

Thanks,
aaron

 

[Luke M]

you can use the INDIRECT function to force the formula to look at a specific
sheet/range. Note that if you move the cells in Master Schedule around, this
formula will not adjust, like XL normally does. Since you had absolute
references placed, I assumed this was not an issue.

=IF(G10="",0,VLOOKUP(G10,INDIRECT("Master_Schedule!$A$1:$E$106"),5,FALSE))

 

[Air-ron]

Thanks!!!

Way more elegant than the convoluted solutions I was envisioning!
Yes, the range on the fixture schedule is oversized to allow for some
specialty fixtures that wouldn't be exported to autocad, but we would want in
our load totals; so the absolute reference isn't a problem, so long as any
updated schedule is pasted in correctly.

Thanks again!
Aaron

 

[Gord Dibben]

One method....................

On original sheet before copying do an Edit>Replace

What: =

With: ^^^

Replace all

Copy the sheet to other workbook and reverse the edit>replace on both
workbooks or just the new one and close the original without saving.


Gord Dibben MS Excel MVP