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.

Welcome to the forum.

I realized that you are trying to find the correct value according to job type, as defined in column I drop down and job size, as defined in columns B & C.
In this formula, I use dynamic ranges, which adjust to those values as defined in column I and either column B or C. In J6 and copy down.

Formula:

Please Login or Register  to view this content.

The data validation values in column J are not the same as in the headings of columns B & C in Destination sheet or columns I, J, L, M & N in the Source sheet. So I use functions LEFT and MID to trim the values in those columns headings to match the first 3 characters of the value selected in the drop down. Also, the data validation list had some extra spaces that were causing errors, so I removed them.
The formula contemplates Framed and Printed only, because those headings are in columns B & C of Destination tab and I & J of Source tab. I found no column for Matted. If you decide to add a column for that, I guess the formula will work also.

I hope this helps.

Good luck!