Formula for linked workbooks that toggles different worksheet names to reference versions of prices

dannydanger

New Member
Joined
Aug 1, 2018
Messages
2
I have 2 workbooks; QuoteWorkbook which there will be thousands of copies, and a MasterWorkbook which will hold an inventory of prices for the QuoteWorkbook to reference. MasterWorkbook has multiple copies of PricingWorksheets for QuoteWorkbook to reference one of. The goal is to be able to update any of my QuoteWorkbooks to the new (or old) PricingWorksheets that are in MasterWorkbook by simple means of changing a validation drop-box inside of one QuoteWorkbook. The PricingWorksheets inside of MasterWorkbook would be called “MasterPrice2018.1” and then additional copies called “MasterPrice2018.2” and so on. The MasterWorkbook will always be closed.

In addition, there will be some cases where I imbed the current PricingWorksheet into the QuoteWorkbook for external uses where our server is not available.

See flow diagram for reference here: https://drive.google.com/open?id=1jLoXsOIa2K9iFbNuZF0JpksFA1LwLj84

open

open

I can’t seem to figure out how to separate the FilePath, WorksheetName and the Cell it references into 3 parts. I’ve been playing with this on and off for a month, searching a variety of forums for similar subjects.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

dannydanger

New Member
Joined
Aug 1, 2018
Messages
2
I’ve gotten one approach to work but it doesn’t allow the ease of accessibility to change on-the-go as I’d like. What currently works with a closed MasterWorkbook is the formula as follows:

=INDEX('O:\Sales\MasterInventory\[MasterWorkbook.xlsx]MasterPrice2018.08.01'!B:B,G6,1) with “B:B,G6,1” referencing the cell location.

I can’t get it to work when splitting up the file path, worksheet and cell reference using concatenate or indirect functions. Ideally, the file path and worksheet are both drop-downs I can switch and the rest of the formulas update given they are referenced to the singular file path and worksheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,293
Messages
5,600,773
Members
414,405
Latest member
Zaurb

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