On Aug 14, 5:50 am, "Trevor via OfficeKB.com" <u30135@uwe wrote:
Try: =VLOOKUP(A2,INDIRECT("[Ceramicas.xls]!"&MID(A2,2,3)),16,FALSE)

..but I think this will only work if 'APA' is a named range...which I guess
it must be if your example works? If APA is actually a worksheet name then
you will need to add the range / range name after the worksheet name.





wrote:
I need help using excel. I have an inventory divided into different
workbooks according to product type, each workbook contains multiple
worksheets for the different providers. What I need is to be able to
access the prices of the different products I have, from a different
workbook, I would like a formula to acces the price according to the
type of product I have and be able to use CTRL+D to propagate the
formula to similar items.
For example:
In cell A2 I have CAPA0003 which is the product code and is stored in
worksheet APA in workbook ceramics.xls and to get the price I have
the
formula
=VLOOKUP(A2,[Ceramicas.xls]!APA,16,FALSE)
all worksheets have the same format and the prices are store in
column
16
The formula works perfectly but with this formula I have to type the
worksheet name of each product every time. Since the worksheet name
is
embedded into the product code is there a way to get the worksheet
name from the product code automatically so that I can copy the
formula to all items in the same worksheet to get their prices?

I hope the question is clear enough.
Thank you

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200708/1- Hide quoted text -

- Show quoted text -

Thank you for your help. I took away the brackets [] and it works!!!