Merging two cells which can link to an another Spreadsheet

Gr8smoke

New Member
Joined
Sep 19, 2002
Messages
10
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 :cool:

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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 

Forum statistics

Threads
1,144,208
Messages
5,723,022
Members
422,476
Latest member
beck85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top