Change formula to reference cell in another worksheet

[LJP85]

Hello all


I have a question about how to edit the formula below so that it looks up a result in an external workbook (rather than in another tab).


=INDIRECT("'" & $D$8 & "'!"&ADDRESS(ROW(H95),COLUMN(H95),4))


A spreadsheet that I completed with a bit of help from this forum (thank you again!!) is getting far too many tabs (currently around 30 and likely to at least double). I thought that it may be better than rather having each tab as a point of reference, separate Excel worksheets would easier to navigate between/edit/add to.


For example if cell D8 contained the word "ITEM-A", the cell will then return the result from ROW(H95),COLUMN(H95) on the worksheet named ITEM-A.xlsx


All of these external workbooks could be saved in the same file as the 'master' spreadsheet, however if they were stored in a sub-folder that would also be fine.


I have been trying to work this out myself but my Excel abilities are still somewhat lacking.


Any help would again be really appreciated!

Thank you for your time.


LJP

 

[JLGWhiz]

You put the other workbook name in square brackets
example
=INDIRECT("'" &[OtherWorkbook] & $D$8 & "'!"&ADDRESS(ROW(H95),COLUMN(H95),4))

 

[LJP85]

Hello JLGWhiz

Thanks for the quick reply.

This seems like it would work if I always wanted to reference the same external worksheet. However if I had 30 different worksheets and the one I wanted to reference was based on the result in cell D8, how could I include this in the formula?

Thanks again!

Lawrence

 

[LJP85]

Does anybody else know if this is possible to do?

Thank you.

LJP

 

[Jonmo1]

Concatenate the book name the same way you did the sheet name. And the bookname goes in [brackets]

=INDIRECT("'[" & $D$1 & "]" & $D$8 & "'!"&ADDRESS(ROW(H95),COLUMN(H95),4))

D1 = Book name
D8 = Sheet name


FYI, that external book must be open for the formula to work. Indirect will not be able to reference a closed external workbook.

 

[LJP85]

Thank you Jonmo1. Hmm... I didn't realise that the other workbooks have to be open for the Indirect formula to work. That might be a bit of a problem because there might be about 60 external files and each time I use the main spreadsheet it will use a different 10 - 15 workbooks as sources of reference.

Is there a similar formula which I can use that doesn't require all the other workbooks to be open?

Thanks again.

LJP

 

[Jonmo1]

There is an addon called MoreFunc (see the recommended addins thread in this forum)

However, I honestly recommend going back to your original setup and keep it all in one book.
Further, I would recommend not even having separate sheets.
Keep ALL the data on 1 sheet (row limitations withstanding of coarse)
And Add a column where you indicate a key name (what used to be the tab names).