copy information from a new workbook.

I'm sure this would have been answered previously, but after a few days looking around here and other places on the internet, I've decided to ask the experts.

So... to link to another document isn't to taxing and here is an example of what I mean:

='[test file for information.xlsx]Sheet1'!$A$2

Now what that's doing is looking for the file "test file for information" It then goes to cell A2 on sheet one and displays the value in my current document.

This is all great and works exactly how I want. However, I would like to be able to change the file name and sheet name in one place In the current workbook.

so for example.... I have a column of cells pulling data from a different workbook (all cells referencing the same external workbook)... rather than going into that formula each time and changing the document name. I would like the document name to be a cell in the current workbook.

I would like to get to the point where I enter the workbook, I type in the next documents name, then assuming the information is in the same place in that document the file is populated automatically.

I've tried to explain this the best I can but I'm sure its still confusing. If you are able to help or have any questions don't hesitate to ask.

Thanks in advance! Andy

After looking around a bit this is what I'm currently working on,

=INDIRECT("'["&A2&".xlsx]"&B2&"'!"&$D$28)
=INDIRECT("'["&A2&".xlsx]"&B2&"'!"&C2)

This does not work. Is there anything obvious I'm doing wrong?

What I would like to know... A2 = document name. BUT how does it know the documents location? It doesn't appear to be in any of the code.

I know this doesn't seem to matter if I do a direct copy from workbook 2 to workbook 1 (or link directly across spreadsheets).

But when doing a manual line of code I'm unsure how to tell it where to look.

Its possible for me to link manually through.. however, as I'm sure you can appreciate, I would like to get this as automated as possible.

Thanks again!

Andrew

This works perfectly mate, thank you very much. sorry to be a pain but there's one more way I would like to try.

=INDIRECT("'["&A2&".xlsx]"&B2&"'!$C$2")

!$C$2 <-- where this is I would like to link to link to a different sheet in the same document,

=Sheet2!H12

I'm unsure how to do the formatting of the code properly and I don't know what I'm looking for to learn these specifics. I get my code close each time.

anyway, if you could help me with this last issue I would really appreciate it.

so basically I have the option of changing which cell it pulls information from using a table on a different sheet. Its a little more complex than that, but I can achieve the rest once I have passed this issue.

Thanks once again for the brilliant replies.

This works perfectly mate, thank you very much. sorry to be a pain but there's one more way I would like to try.

=INDIRECT("'["&A2&".xlsx]"&B2&"'!$C$2")

!$C$2 <-- where this is I would like to link to link to a different sheet in the same document,

=Sheet2!H12

I'm unsure how to do the formatting of the code properly and I don't know what I'm looking for to learn these specifics. I get my code close each time.

anyway, if you could help me with this last issue I would really appreciate it.

so basically I have the option of changing which cell it pulls information from using a table on a different sheet. Its a little more complex than that, but I can achieve the rest once I have passed this issue.

Thanks once again for the brilliant replies.

Ideally I would like B2 to remain the same. I know this is confusing but ill try explaining it,

a2 = Document to gather information from (document test)
B2 = Sheet on that document (sheet test)
and then the changing part of the formula is where to look for the cell in (document test). This information would be pulled from sheet 2 on the current document.

a2 and b2 remain the same every time I use the formula but the last part is a variable from sheet 2.

I hope I've explained this better, maybe you have a better way of doing it if I cant link from multiple sheets at once,

thanks Andy!