[Solved] Referencing Different Workbook with Variable File..
Posted: 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!
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!
