Formulas for Linked Spreadsheets

Thanks very much in advance for your time and attention to my problem.
I may be approaching this from the wrong angle:
I have two spreadsheets, Source.xlsx and Destination.xlsx. Both spreadsheets will grow over time.
I have written a formula which is located in Destination.xlsx, column J, “Selling Price”. The formula references specific cells in Source.xlsx depending on the selection from a dropdown located in Destination.xlsx, column I, “Select, Print, Matted, Framed”. Corresponding columns in Source.xlsx are; columns L “price-print”, M “price-mat”, N “price-frame” respectively. “Select” in the dropdown in Destination.xlsx needs no corresponding column in Source.xlsx:
Dropdown selections in Destination.xlsx, column I are:
IF I6=Select, THEN I7=$0.00
IF I6=Print, THEN I7=the value in a specific cell in Source.xlsx, L2, L3, L4, L5 … etc.
IF I6=Matted, THEN I7=the value in a specific cell in Source.xlsx, M2, M3, M4, M5 ... etc.
IF I6=Framed, THEN I7=the value in a specific cell in Source.xlsx, N2, N2, M4, M5 ... etc.
The row in Source.xlsx is different for each entry as is the sheet and row in Destination.xlsx.
The formula I have written “works” but has to be manually edited for each entry in Destination.xlsx in order for it to point to the correct columns in Source.xlsx. Which means that it really doesn’t work the way it should. This suggests that the formula should be located in Source.xlsx but Excel dynamically changes the formula when located there and cannot be referenced from Destination.xlsx. There may be a way to write the formula so that it can be located in Source.xlsx then referenced in Destination.xlsx but this escapes me. As I said, I may be approaching this rom the wrong angle.
Please note: Source.xlsx is the main spreadsheet for a few other “destination” spreadsheets and is used for merging data into Photoshop images.