Multiple Excel References from Multiple Workbooks.

[KJ318]

Hello all,

I would be grateful for any help with the following scenario:


I have a Summary workbook and need to reference information for multiple workbooks which are detailed below:

S:\InvoiceData\E-Invoice Arbitration\Financial Year 2019-2020A\provider1(01-04-19to28-04-19)\Provider1[Shell.xlsx]Recon'!$C$2

S:\InvoiceData\E-Invoice Arbitration\Financial Year 2019-2020A\provider2(01-04-19to28-04-19)\Provider2[Shell.xlsx]Recon'!$C$3

S:\InvoiceData\E-Invoice Arbitration\Financial Year 2019-2020A\provider3(01-04-19to28-04-19)\Provider3[Shell.xlsx]Recon'!$C$4

S:\InvoiceData\E-Invoice Arbitration\Financial Year 2019-2020A\provider4(01-04-19to28-04-19)\Provider4[Shell.xlsx]Recon'!$C$5

S:\InvoiceData\E-Invoice Arbitration\Financial Year 2019-2020A\provide5(01-04-19to28-04-19)\Provider5[Shell.xlsx]Recon'!$C$6

The file directory structure needs to remain in place for work purposes.
I have over 100 workbooks (each located in their own folder) and wanted to know if there’s any way to automate the references of the above records i.e. without having to click on the Summary Workbook and open each workbook individually and link the sheet and cell reference.

As you can imagine, this would be very time consuming; I did some research but cannot find anything which relates to what I need. The closest thing I have found was creating a ‘Query’ in Excel but wanted to know if there’s anything easier. I have tried copying and pasting the above file paths in the formula bar hoping Excel would be okay with referencing the workbooks in this manner, but this does not seem to work.

Any help would be much appreciated.
Thank you.

 

[GlennUK]

Quote: "I have tried copying and pasting the above file paths in the formula bar hoping Excel would be okay with referencing the workbooks in this manner, but this does not seem to work."

Your file paths/name/sheet/ref strings look incorrect. I would open one of those, link using the usual method, then close it, and see what the link looks like. Convert to a string to copy and manipulate into other versions, and then convert back to a formula afterwards. That should work.

 

[KJ318]

Hi Glenn,

Thanks for your help.

The correct path is:

S:\InvoiceData\E-Invoice Arbitration\Financial Year 2019-2020A\provider1(01-04-19to28-04-19)\Provider1[Shell.xlsx]Recon'!$C$2 ​

Sorry to be dim, how would I convert the string back to a

formula, I've looked online but all I can see is examples about writing code in VBA.

Thanks again.​

 

[GlennUK]

Did you get that path by creating a link in a cell to the Shell.xls Recon sheet? Because it still does not look valid to me.

 

[KJ318]

I've re-created the directory on my own computer as my work directory is quite long but the premise is the same:

='C:\Financial Year 2019-20\Period(01-04-19to28-04-19)\Provider1\[Provider1.xlsx]Recon'!$C$2

I copied the directory directly from Excel. I hope this enables me to get help on this...Thank you for your assistance so far.

 

[GlennUK]

Great. Change it to a string, like this:
="='C:\Financial Year 2019-20\Period(01-04-19to28-04-19)\Provider1\[Provider1.xlsx]Recon'!$C$2"

Now you can replicate, edit, or change the string by string manipulation formulas ... for example like this: (say in A1)
="='C:\Financial Year 2019-20\Period(01-04-19to28-04-19)\Provider"&ROW()&"\[Provider"&ROW()&".xlsx]Recon'!$C$"&ROW()+1

... copied down 100 rows (assuming the logic you have in your examples).

When the strings of the links are correct, copy the cells, and paste over them as values. Then to convert the string equivalent of links into real links, select the range, and do Edit/Replace, = with = Replace All, to refresh every cell.

Let me know if that makes sense.

 

[KJ318]

Glen it worked! I can't thank you enough, you've saved me from hours of tedious work! Much appreciated

 

[GlennUK]

Glen it worked! I can't thank you enough, you've saved me from hours of tedious work! Much appreciated

Hooray! Thanks for the feedback ... I've done this kind of thing hundreds of times, so I knew it work for you.