Multiple inputs from drop down lists to give single output from data on separate sheet

Sports123

New Member
Joined
Apr 12, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I am trying to have my spreadsheet show costs for running a stadium based on two inputs - Crowd Size and Zones Active.

On Sheet One I have two drop down lists - Crowd Size and Zones Active. I also have a Total Cost cell where I require the output.

On Sheet Two I have my data set - rows detail crowd size, columns detail zones active - giving a grid of costs based on the two variables,

When I select the appropriate drop down variables on Sheet One I am trying to give the correct output from the grid on sheet two, but can not work a formula for more than one input.

Any help would be much appreciated!

Thank you
 

Attachments

  • Sheet 1.JPG
    Sheet 1.JPG
    80.5 KB · Views: 70
  • Sheet 2.JPG
    Sheet 2.JPG
    88.2 KB · Views: 69

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You'll probably need an INDEX(MATCH function, but can you post the dat so that we can work with it please.....pictures won't cut it I'm afraid.
 
Upvote 0
Two Inputs to One Output.xlsx
ABC
1
2Crowd Size0-5,000
3Zones Active4 Zones
4
5Total Cost
6
Sheet1
Cells with Data Validation
CellAllowCriteria
B2List=Sheet2!$A$2:$A$8
B3List=Zones


Two Inputs to One Output.xlsx
ABCDE
1Total Charge (Inc GST)4 Zones5 Zones6 Zones
20-5,000100020003000
35,001-10,000200030004000
410,001,15,000300040005000
515,001-20,00050006000
620,001-25,00060007000
725,001-30,0008000
830,001 +9000
9
Sheet2
 
Upvote 0
Try using
Excel Formula:
=SUMPRODUCT((B2=Sheet2!A2:A8)*(B3=Sheet2!B1:D1)*Sheet2!B2:D8)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top