indirect w/ vlookup = #REF error

[jatman]

i have a new purchase order and have most of it working now. i have one
known issue left. in order to follow along, you'll need three sheets/tabs
(minimum,) name them: TAB1, TAB2, TAB3

TAB2 has a list of suppliers (Column A will have the supplier's full name.
Column B will have the supplier's short name, which will also be the name of
a TAB.)
For TAB2, set up as follows: A1 = SUPPLIER NAME 1, B1 = TAB3, A2 = SUPPLIER
NAME 2, B2 = TAB4, ...... (as more suppliers are added, this allows for the
sheet to just build up continously.)

TAB3 will have the products/services for the appropriate supplier in A1
(SUPPLIER NAME 1). here Column A represents the produt code, and Column B
will be the description of the product. TAB3, A1 = ITEM1, B1 = DESCRIPTION
OF ITEM 1, A2 = ITEM 2, B2 = DESCRIPTION OF ITEM 2.... (as more products are
added, just keep adding it on...)

TAB1 is the actul PO. cell I5 is a drop down list of the suppliers (from
TAB2)
A12 is the product code and b12 is the description of the product code. in
B12, i have the following formulae entered:

=IF(A12="","",VLOOKUP(A12,INDIRECT("'"&VLOOKUP(I5,TAB2!A:B,2,FALSE)&"'!$a:$b"),2,FALSE))

i have a silimar formulae entered for the price to look up on the
appropriate supplier tab. this works, and everything up to this point is
good.

When i go to make a copy of the PO to save it (without the extra tabs,) the
value in B12 (description of item) now shows #REF. [to make a copy, i right
click on the tab and select make copy, in a new workbook)] the formulae that
shows up in B12 changes to:

=IF(A12="","",VLOOKUP(A12,INDIRECT("'"&VLOOKUP(I5,'[Purchase Order
(test).xlsm]Suppliers'!A:B,2,FALSE)&"'!$a:$b"),2,FALSE))

i understand that it is referencing the original workbook to get the data,
but why it does not bring back the value.

any suggestions to fix this error?

thank you,

 

[Max]

When i go to make a copy of the PO to save it (without the extra tabs,) ..

Consider freezing the copy? Copy n paste special as values. Think the error
is returned because INDIRECT requires the file to be open simultaneously.