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

[dannydanger]

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

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.

 

[dannydanger]

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.