indirect w/ vlookup = #REF error


J

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,
 
Ad

Advertisements

M

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.

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top