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: 5
  • Sheet 2.JPG
    Sheet 2.JPG
    88.2 KB · Views: 4

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,150
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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.
 

Sports123

New Member
Joined
Apr 12, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,150
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try using
Excel Formula:
=SUMPRODUCT((B2=Sheet2!A2:A8)*(B3=Sheet2!B1:D1)*Sheet2!B2:D8)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,150
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad it worked... (y) (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

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
Top