I am aware of the indirect function in excel which allows you to reference different sheets of the same workbook by changing the text in one cell, but can it reference cells which are in a different workbook.
For example, I produce management accounts using a spreadsheet model which imports data using msquery.
Once we have finished the accounts, I copy the sheets to another file and break the links.
I want to be able to reference part of one of the sheets in every month to build up a historical analysis which forms part of our notes pack.
I would like to make it easy for myself and not have to reference each worksheet in turn by copying and pasting and then changing the filename in the formula.
Any comments will be greatfully received.
Thanks.
Christopher Lee
Related resources
Replies (3)
Please login or register to join the discussion.
Outcome
Thanks to you both for your feedback, as steven quotes, my spreadsheets have to be open to make the indirect method work. This will mean me having to open more and more workbooks each month. So I think I will have to start copying the relevant sheet to my analysis workbook each month and renameing that.
Thanks again for your help.
CHRIS LEE
Yes, but make sure syntax is correct
I have used INDIRECT() to reference external spreadsheets, but getting the quotes, brackets and syntactic details right took a while and didn't seem to be well explained in the help.
Here is what worked for me (Excel 2000): Opening SINGLE quote, followed by opening square bracket, followed by name of external spreadsheet, INCLUDING the .xls extension, followed by close square bracket, followed by the name of the sheet that you want to refer to on the external spreadsheet, followed by another single quote (the 2 single quotes represent a matched pair), followed by an exclamation mark, followed by the name of the field to which you want to refer. (You could presumably use a field reference instead of a name but you might need to write it in absolute form, eg. $C$3 or whatever).
If the external spreadsheet is in a different folder, the path must also be specified. If you wish you can string the various components together from the contents ofother fields using &.
Hope this helps - good success!
Yes
Quote from Excel help file:
"If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value."
Seems clear enough. Why not give it a try?