SOLVED

Looking for a way to use a drop down menu to change cell references in a formula

Copper Contributor

Hi, 

 

I have been scratching my head for a few days and have been googling for a few hours to try to figure out a solution, but to no avail. My situation is that I have a curtain store that makes custom curtains. As an example I have four tables with width and height on the X an Y axis which when meeting at a cross point give a price for a certain size of curtain. Each of the four tables represent a type of curtain such as pinch pleat, grommet and etc. Now I already have an estimate sheet which when I input a width and height give me a price from the first table of curtain style. What I am trying to figure out next is if its possible to use a drop down menu to change the pricing table used in the estimate sheet to a different style of curtain. Is this solution I am looking for possible through using formulas or would I be looking at using VBA in order to achieve this. 

 

Thanks 

 

P.S I have attached an example file.

4 Replies
best response confirmed by Ray167 (Copper Contributor)
Solution

Hello,

 

Please see attached my solution. I have added 'Group' and 'Group & Inch' columns in each of the price tables, as well as a 'Price Group' column in the 'Estimate Sheet' table. I have also modified the following formulas of the Estimate Sheet:

 

'Formula height' - included a reference to the 'Price Group'

'MSRP$' - extended the lookup range to include all price tables. I have also removed '+1' after the 'Match' functions to make sure the price is pulled from the correct Width/height cell.

 

Hope this helps

Yury

 

Had a look at your spreadsheet and have attached an example that may help.

Thanks for sharing your example. INDIRECT works well with the solution (I, normally, try avoiding using this function due to its volatile nature, however your model is small, therefore, volatility is not an issue).

 

On top of your solution I have created named ranges for each of the price groups, and used them in the lookup formula of the 'Cost' column. Using this approach allowed me to remove the hard-coded references to the price groups, which, otherwise, would require adjustments after possible addition/deletion of rows in the price group sheet.

 

I have also moved the 'Ref' columns functionality into the lookup formula (I used INDEX/MATCH as my preferred approach over V/HLOOKUP), since the formula seems to be short enough and is relatively easy to read. 

 

Hope this is beneficial

Thank you Yury and Rich for your response. Both solution are very helpful and solved my issue. I am a little surprised I didn't come across this type of solution, but I believe I was just over complicating my situation. Again I appreciate your time for finding a solution to my problem.

1 best response

Accepted Solutions
best response confirmed by Ray167 (Copper Contributor)
Solution

Hello,

 

Please see attached my solution. I have added 'Group' and 'Group & Inch' columns in each of the price tables, as well as a 'Price Group' column in the 'Estimate Sheet' table. I have also modified the following formulas of the Estimate Sheet:

 

'Formula height' - included a reference to the 'Price Group'

'MSRP$' - extended the lookup range to include all price tables. I have also removed '+1' after the 'Match' functions to make sure the price is pulled from the correct Width/height cell.

 

Hope this helps

Yury

 

View solution in original post