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