I'm trying to create a dynamic price proposal document, so depending on the inputs (from a drop down menu), the cost will change.
For instance, let's say I'm trying to find the cost to a club for hosting a football match. across my x axis I've got Day Game / Night Game as two separate columns
I then have 4 columns along the Y axis for stadium capacity being 80,000 / 50,000/ 30,000/ 5,000
Now I want to have 3 multiple choice questions:
Policing necessary, with drop down of yes/no (the cost of policing being 5000 standard for day game and 8000 for night game for 80K stadium. Obviously the price is different depending on the size of stadium - so although that won't be in the drop down, the price will need to dynamically change.)
Programmes for sell, with drop down of yes/no - with the cost of printing (50p per programme)
Selling beers?, with drop down of "individual/pack of 6" (with cost of buying individual being £1 and pack of 6 being £5)
Once you select your 3 drop down option, the grid between the X and Y axis completes with different costs.
Anyone know how to do this in excel?
Using multiple drop down menus to change calculation
-
- Dumbledore
- Posts: 5457
- Joined: 01 Sep 2010, 12:25
- Location: Amsterdam
- FPL:
- jpk
- Dumbledore
- Posts: 5216
- Joined: 13 Oct 2005, 18:42
- Contact:
Re: Using multiple drop down menus to change calculation
sounds like you need to use a formula
if I understand correctly your 3 drowdowns will be cells with data validation applied to populate the list of possible values
your result will be a formula that uses those 3 cells with a combination of IF() statements
if I understand correctly your 3 drowdowns will be cells with data validation applied to populate the list of possible values
your result will be a formula that uses those 3 cells with a combination of IF() statements
-
- Dumbledore
- Posts: 5457
- Joined: 01 Sep 2010, 12:25
- Location: Amsterdam
- FPL:
Re: Using multiple drop down menus to change calculation
That sounds about correct - but my knowledge of excel is shocking, so I need someone to explain to a layman like mejpk wrote:sounds like you need to use a formula
if I understand correctly your 3 drowdowns will be cells with data validation applied to populate the list of possible values
your result will be a formula that uses those 3 cells with a combination of IF() statements
Thanks
- jpk
- Dumbledore
- Posts: 5216
- Joined: 13 Oct 2005, 18:42
- Contact:
Re: Using multiple drop down menus to change calculation
ah well it's not going to be easy to teach the basics of Excel from a series of posts on here.
Maybe you could search for a few youtube vids on Excel formulas and data validation and see how that goes. I could set up an example sheet and send it to you but that seems to be a bit like doing your homework for you and I reckon you'll get a lot more understanding out of learning it for yourself
Maybe you could search for a few youtube vids on Excel formulas and data validation and see how that goes. I could set up an example sheet and send it to you but that seems to be a bit like doing your homework for you and I reckon you'll get a lot more understanding out of learning it for yourself
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Using multiple drop down menus to change calculation
You won't data validation but a drop down box for the 3 multi choice questions as it's only yes or no. Then as jpk says IF statements in the grid to calculate costs which will reference to the yes/no answer.
edit: actually data validation will work fine
edit: actually data validation will work fine
View Latest: 1 Day View Your posts