Forum Discussion
Looking for a way to use a drop down menu to change cell references in a formula
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.
