Excel Reference External Sheet

1
votes

An excel question for you gurus. I've tried searching high and low and haven't come up with an effective solution.

I'm trying to create a formula that will lookup a value in an external sheet. I'm using the SUMPRODUCT formula and it works perfectly. Formula is below:

=SUMPRODUCT(--('File\Path\[file.xlsx]SheetName!$D$1:$D$1000=$B3), --('File\Path\[file.xlsx]SheetName'!$O$1:$O$1000=$A3), 'File\Path\[file.xlsx]SheetName'!$Q$1:$Q$1000)

The issue I'm running into, however, is that the source file is updated every day. Although the workbook name stays the same, the sheet name changes. A random string gets assigned to the source sheet name each time it is updated. As such SheetName becomes SheetName ase341.

Is there a way to have the formula read the external sheet number instead of the name? I want the formula to update regardless of the sheet name. If there's no way to read the sheet position is there a way to change the sheet name via a formula in an external workbook?


Usage Example

I have a workbook (analysis) and it pulls data from another workbook (source). Source is updated every day with new data. The data in Source is updated by downloading a report from the internet and saving over the old source file. As such, the file name stays the same but whatever is inside the file is always different (including the sheet name). There is always only ever one sheet in the Source with the same number of columns, always in the same position.

2
vbaexcel
stackoverflow.com/questions/16428189/… might help. - ZAT

2 Answers

0
votes

There is a really neat way to refer to a block of cells in an external workbook in which the sheetname or even the block address may vary. Say we have:

=SUM('C:\Users\James\Desktop\[Book1.xlsx]Sheet1'!$B$2:$B$9)

however the sheetname may vary. First assign a Defined Name to the block in Book1 (say XXX)

Then we can use:

=SUM('C:\Users\James\Desktop\Book1.xlsx'!XXX)

It does not matter if the sheetname changes, the Defined Name will change with it!

0
votes

Your issue would be most efficiently solved with VBA, but if you're just getting started this might not be the best route.

You can get the sheetname or filename with just a formula, though:

http://www.ozgrid.com/VBA/return-sheet-name.htm