ExcelBanter - Getting data from different worksheets in another workbook

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Getting data from different worksheets in another workbook (https://www.excelbanter.com/excel-programming/395424-getting-data-different-worksheets-another-workbook.html)

Getting data from different worksheets in another workbook

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

Getting data from different worksheets in another workbook

Yes it can be done, but you'll need to use a different key stroke rather than
Cntl + D.

1) You need to make a list of filename in a new column the code below uses
column B
2) You need to add this macro into the VBA code. go to Tools Menu - Macro -
Visual Basic editor. On VBA menu - Insert - Module.. Paste Macro below into
VBA window.
3) Select cell immediately below cell you want to copy just like when you
used Cntl _ D.
4) You can activate macro by three methods.
a) In VBA window, clik any line of code and then press F5
b) From Excel worksheet, go to Tools Menu - Macro - Macro and select
macro name.
c) Create an new shortcut key for macro. repeat step b above, but
select options instead of run. Pick new key to run macro. I wouldn't
recommend using Cntl D becaue original copy down macro would be replaced with
new code.

Sub copydown()
Const workbook_columns = "B"

Oldformula = ActiveCell.Offset(-1, 0).Formula

Filename = Mid(Oldformula, InStr(Oldformula, "[") + 1)
Filename = Left(Filename, InStr(Filename, "]") - 1)

Newformula = Replace(Oldformula, Filename, "")

Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set FileNameRange = _
Range(Cells(1, workbook_columns), _
Cells(Lastrow, workbook_columns))

Set c = FileNameRange. _
Find(what:=Filename, LookIn:=xlValues)
If Not c Is Nothing Then
NewFileName = c.Offset(1, 0)
Newformula = Replace(Newformula, _
"[", "[" & NewFileName)
ActiveCell.Formula = Newformula
End If

End Sub

" 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

Getting data from different worksheets in another workbook

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!!!