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$1 06,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

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))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Air-ron" wrote:

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$1 06,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

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


"Luke M" wrote:

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))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Air-ron" wrote:

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$1 06,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

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

On original sheet before copying do an EditReplace

What: =

With: ^^^

Replace all

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


Gord Dibben MS Excel MVP

On Wed, 25 Feb 2009 12:04:01 -0800, Air-ron
wrote:

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