SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-307888%22%20slang%3D%22en-US%22%3ELooking%20for%20a%20way%20to%20use%20a%20drop%20down%20menu%20to%20change%20cell%20references%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307888%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20scratching%20my%20head%20for%20a%20few%20days%20and%20have%20been%20googling%20for%20a%20few%20hours%20to%20try%20to%20figure%20out%20a%20solution%2C%20but%20to%20no%20avail.%20My%20situation%20is%20that%20I%20have%20a%20curtain%20store%20that%20makes%20custom%20curtains.%20As%20an%20example%20I%20have%20four%20tables%20with%20width%20and%20height%20on%20the%20X%20an%20Y%20axis%20which%20when%20meeting%20at%20a%20cross%20point%20give%20a%20price%20for%20a%20certain%20size%20of%20curtain.%20Each%20of%20the%20four%20tables%20represent%20a%20type%20of%20curtain%20such%20as%20pinch%20pleat%2C%20grommet%20and%20etc.%20Now%20I%20already%20have%20an%20estimate%20sheet%20which%20when%20I%20input%20a%20width%20and%20height%20give%20me%20a%20price%20from%20the%20first%20table%20of%20curtain%20style.%20What%20I%20am%20trying%20to%20figure%20out%20next%20is%20if%20its%20possible%20to%20use%20a%20drop%20down%20menu%20to%20change%20the%20pricing%20table%20used%20in%20the%20estimate%20sheet%20to%20a%20different%20style%20of%20curtain.%20Is%20this%20solution%20I%20am%20looking%20for%20possible%20through%20using%20formulas%20or%20would%20I%20be%20looking%20at%20using%20VBA%20in%20order%20to%20achieve%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S%20I%20have%20attached%20an%20example%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-307888%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308698%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20way%20to%20use%20a%20drop%20down%20menu%20to%20change%20cell%20references%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308698%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Yury%20and%20Rich%20for%20your%20response.%20Both%20solution%20are%20very%20helpful%20and%20solved%20my%20issue.%20I%20am%20a%20little%20surprised%20I%20didn't%20come%20across%20this%20type%20of%20solution%2C%20but%20I%20believe%20I%20was%20just%20over%20complicating%20my%20situation.%20Again%20I%20appreciate%20your%20time%20for%20finding%20a%20solution%20to%20my%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308266%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20way%20to%20use%20a%20drop%20down%20menu%20to%20change%20cell%20references%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308266%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20sharing%26nbsp%3Byour%20example.%20INDIRECT%20works%20well%20with%20the%20solution%20(I%2C%20normally%2C%20try%20avoiding%20using%20this%26nbsp%3Bfunction%20due%20to%20its%20volatile%20nature%2C%20however%20your%20model%20is%20small%2C%20therefore%2C%20volatility%20is%20not%20an%20issue).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20top%20of%20your%20solution%20I%20have%20created%20named%20ranges%20for%20each%20of%20the%20price%20groups%2C%20and%20used%20them%20in%20the%20lookup%20formula%20of%20the%20'Cost'%20column.%20Using%20this%20approach%20allowed%20me%20to%20remove%20the%20hard-coded%20references%20to%20the%20price%20groups%2C%20which%2C%20otherwise%2C%20would%20require%20adjustments%20after%20possible%20addition%2Fdeletion%20of%20rows%20in%20the%20price%20group%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%26nbsp%3Bmoved%20the%20'Ref'%20columns%20functionality%20into%20the%20lookup%20formula%20(I%20used%20INDEX%2FMATCH%20as%20my%20preferred%20approach%20over%20V%2FHLOOKUP)%2C%20since%20the%20formula%20seems%20to%20be%20short%26nbsp%3Benough%20and%20is%20relatively%20easy%20to%20read.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20is%20beneficial%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307949%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20way%20to%20use%20a%20drop%20down%20menu%20to%20change%20cell%20references%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307949%22%20slang%3D%22en-US%22%3E%3CP%3EHad%20a%20look%20at%20your%20spreadsheet%20and%20have%20attached%20an%20example%20that%20may%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307890%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20way%20to%20use%20a%20drop%20down%20menu%20to%20change%20cell%20references%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307890%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20my%20solution.%20I%20have%20added%20'Group'%20and%20'Group%20%26amp%3B%20Inch'%26nbsp%3Bcolumns%20in%20each%20of%20the%20price%20tables%2C%20as%20well%20as%20a%20'Price%20Group'%20column%20in%20the%20'Estimate%20Sheet'%20table.%20I%20have%20also%20modified%26nbsp%3Bthe%20following%20formulas%20of%20the%20Estimate%20Sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Formula%20height'%20-%20included%20a%20reference%20to%20the%20'Price%20Group'%3C%2FP%3E%3CP%3E'MSRP%24'%20-%20extended%20the%20lookup%20range%20to%20include%20all%20price%20tables.%20I%20have%20also%20removed%20'%2B1'%26nbsp%3Bafter%20the%20'Match'%20functions%20to%20make%20sure%20the%20price%20is%20pulled%20from%20the%20correct%20Width%2Fheight%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ray167
New 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
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.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies