[Solved] Referencing Different Workbook with Variable File..

Discuss the spreadsheet application

[Solved] Referencing Different Workbook with Variable File..

Postby Theondorian » Sat May 25, 2019 9:31 pm

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!
Attachments
Top 500_GB_glass mirror_All Categories_filters.csv
(180 Bytes) Downloaded 13 times
Comparison Sheet.ods
(12.38 KiB) Downloaded 14 times
Last edited by robleyd on Wed May 29, 2019 12:32 pm, edited 2 times in total.
Reason: Add green tick
Apache OpenOffice 4.1.3 - AOO413m1(Build:9783) - Rev. 1761381 - Windows 10 Pro x64
Theondorian
 
Posts: 2
Joined: Sat May 25, 2019 9:07 pm

Re: Referencing Different Workbook with Variable File Name

Postby coray80 » Wed May 29, 2019 7:46 am

Try
Code: Select all   Expand viewCollapse view
=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))
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
coray80
Volunteer
 
Posts: 355
Joined: Thu Mar 01, 2012 6:41 am

Re: Referencing Different Workbook with Variable File Name

Postby Theondorian » Wed May 29, 2019 11:50 am

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.
Apache OpenOffice 4.1.3 - AOO413m1(Build:9783) - Rev. 1761381 - Windows 10 Pro x64
Theondorian
 
Posts: 2
Joined: Sat May 25, 2019 9:07 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests