Merging two cells which can link to an another Spreadsheet

[Gr8smoke]

Hi All,

I was given the task of trying to link several cells from a protected pricing spreadsheet created by another company for our use into an existing spreadsheet. The company is unwilling to unprotect the spreadsheet to make it easier citing proprietary customer info.

To further complicate things, my management wants to set certain attributes in the pricing spreadsheet so that I would have to have 7 or more different pricing spreadsheets to handle the pricing depending on which product is desired.


I ended up creating a drop down box which is linked to vlookup table and some VBA. When the macro is executed it will open another spreadhseet depending on what was chosen in the drop down box. Took me some surfing to figure this one out

Now what I am trying to do is two combine two cells (1 which changes and the other that doesn't) so that they can link to the other spreadsheet above to get the info I want.

cell 1 = pricing.xls
cell 2 = Sheet1!$C$8

so here I would want to link to CELL c8 in sheet1 of pricing.xls

and if Cell 1 changes to pricing07.xls

then I would want to link to CELL c8 in sheet1 of pricing07.xls

TX in advance

 

[BigC]

Try the following:
=INDIRECT(cell1&cell2) which will return the value in the location identified by concatenating cell1 and cell2.
returns the value in "pricing.xlsSheet1!$C$8"

You may need to include other symbols (eg. [ &/or ] &/or !) enclosed in "" within the first argument to pick up the full path & workbook file location, and this may vary if the file is not currently open.
Lookup INDIRECT in the function Help

HTH

 

[Gr8smoke]

TX that worked perfectly