[Solved] Referencing Different Workbook with Variable File..

[Theondorian]

Hi there,

I really hope someone is able to help out here, I have spent hours searching online for a solution to this problem but I just can't seem to get it to work.

I am looking to compare the cells from a column of two separate workbooks and if they match to output the value of a third cell on one of the workbooks. I have managed to do this using INDEX and MATCH however it becomes more complicated as one of the workbooks has a variable file name.

I have uploaded two files, one .ods and one .csv. The ods is the one which I am working on and the csv is the workbook which I am trying to reference. Essentially, what I am trying to do is compare Column A of the ods with Column A of the csv and if they match to display the contents of Column C of the csv in Column C of the ods.

Where this becomes more complicated is that ongoing the csv files will have variable names. “Top 500_GB_” and “ All_Categories_filters” will remain consistent, it is just the part between them which will change, so I have inserted this into Column B of the ods. When referencing the other workbook, I need the contents of Column B of the ods placed between the beginning and end of the file name of the csv. I have managed to do this using & and CONCATENATE and the formula works for cells C2 and C3 of the ods (as in they output the expected value) however from C4 onward the output becomes nonsensical.

Typically, when my formulas don't work they don't work at all however the very confusing part for this one is that it seems to work initially and then stop.

I have included Column D in the ods file as I have managed to make this work, but without the variable file name. This one is giving the exact results which I am looking for in Column C of the ods, but the variable file name part is critical for this spreadsheet to work ongoing.

Any information or tips would be thoroughly appreciated!

[coray80]

Try

Code: Select all

=INDEX(INDIRECT("'file:///C:/Users/Jonathan/Desktop/To Upload/Top 500_GB_"&B2&"_All Categories_filters.csv'#$Sheet1.$C$1:$C$11");MATCH(A5;INDIRECT("'file:///C:/Users/Jonathan/Desktop/To Upload/Top 500_GB_"&B2&"_All Categories_filters.csv'#$Sheet1.$A$1:$A$10000");0))

[Theondorian]

Hi coray80,

Thank you for taking the time to respond, this is much appreciated. I am very pleased to say that your suggestion worked!

I had to change "MATCH(A5;INDIRECT" to "MATCH(A2;INDIRECT" to suit the cell which I was placing the formula in, and when I did this it worked perfectly. I can see that CONCATENATE has been removed and replaced with &, I gave this a go myself before posting this topic but I left the bracket immediately before the second INDIRECT which caused chaos.

Again, thank you for your help here. This is the first time I have had to post online requesting help with a formula and I am so glad this has been sorted. You're a credit to the community

I will now mark this topic as solved.