Drop down selection?

H

Hsouiri

New Member
Joined
Mar 30, 2018
Messages
5
I have been looking around for an answer for a few days now and I can't seem to find anything close to what I am looking for. I am hoping you guys can either tell me if it is doable or not! What I want to achieve is select a name from a drop down list and show me all the items associated with that on the table below it.

Essentially if I select T&M, all of the fields below it will populate, same with basic and standard. This would be in one cell eliminating the Basic and Standard cells to reduce space. (these calculations are done through a formula so I would like to keep their integrity as another table has changeable inputs. Any help in this would be greatly appreciated!

[TABLE="width: 356"]
<tbody>[TR]
[TD]Items[/TD]
[TD]T&M[/TD]
[TD]Basic[/TD]
[TD]Standard[/TD]
[/TR]
[TR]
[TD]Sec/Ops[/TD]
[TD="align: right"]$4,770.40[/TD]
[TD="align: right"]$4,502.40[/TD]
[TD="align: right"]$3,966.40[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD="align: right"]$90.00[/TD]
[TD="align: right"]$84.00[/TD]
[TD="align: right"]$77.50[/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD="align: right"]$4,860.40[/TD]
[TD="align: right"]$4,586.40[/TD]
[TD="align: right"]$4,043.90[/TD]
[/TR]
[TR]
[TD]ODC[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]Cleaning Fee[/TD]
[TD="align: right"]$300.00[/TD]
[TD="align: right"]$300.00[/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Conference Fee[/TD]
[TD="align: right"]$1,500.00[/TD]
[TD="align: right"]$1,500.00[/TD]
[TD="align: right"]$1,500.00[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]$6,660.40[/TD]
[TD="align: right"]$6,386.40[/TD]
[TD="align: right"]$5,843.90[/TD]
[/TR]
[TR]
[TD]Labor Analysis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Add'l cost for meeting[/TD]
[TD="align: right"]$4,860.40[/TD]
[TD="align: right"]$3,870.40[/TD]
[TD="align: right"]$2,096.40[/TD]
[/TR]
[TR]
[TD]Annual Fee[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3,200.00[/TD]
[TD="align: right"]$8,100.00[/TD]
[/TR]
[TR]
[TD]Cost of 4 meetings per year[/TD]
[TD="align: right"]$19,441.60[/TD]
[TD="align: right"]$18,681.60[/TD]
[TD="align: right"]$16,485.60[/TD]
[/TR]
[TR]
[TD]Cost of 3 meetings per year[/TD]
[TD="align: right"]$14,581.20[/TD]
[TD="align: right"]$14,811.20[/TD]
[TD="align: right"]$14,389.20[/TD]
[/TR]
[TR]
[TD]Cost of 2 meetings per year[/TD]
[TD="align: right"]$9,720.80[/TD]
[TD="align: right"]$10,940.80[/TD]
[TD="align: right"]$12,292.80[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
"This would be in one cell eliminating the Basic and Standard cells to reduce space. "

What I meant to say is column and not cell. Thanks!
 
Upvote 0
Hsouiri said:
I have been looking around for an answer for a few days now and I can't seem to find anything close to what I am looking for. I am hoping you guys can either tell me if it is doable or not! What I want to achieve is select a name from a drop down list and show me all the items associated with that on the table below it.

Essentially if I select T&M, all of the fields below it will populate, same with basic and standard. This would be in one cell eliminating the Basic and Standard cells to reduce space. (these calculations are done through a formula so I would like to keep their integrity as another table has changeable inputs. Any help in this would be greatly appreciated!
Click to expand...

Hi!

If I understand correctly what you want, maybe the formula below can helps.

In G2 and copy down:

=INDEX($B$2:$D$14,ROWS($G$2:$G2),MATCH(G$1,$B$1:$D$1,0))


[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Items[/TD]
[TD]T&M[/TD]
[TD]Basic[/TD]
[TD]Standard[/TD]
[TD][/TD]
[TD]Items[/TD]
[TD]T&M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sec/Ops[/TD]
[TD]$4,770.40[/TD]
[TD]$4,502.40[/TD]
[TD]$3,966.40[/TD]
[TD][/TD]
[TD]Sec/Ops[/TD]
[TD]$4,770.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]IT[/TD]
[TD]$90.00[/TD]
[TD]$84.00[/TD]
[TD]$77.50[/TD]
[TD][/TD]
[TD]IT[/TD]
[TD]$90.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Labor[/TD]
[TD]$4,860.40[/TD]
[TD]$4,586.40[/TD]
[TD]$4,043.90[/TD]
[TD][/TD]
[TD]Labor[/TD]
[TD]$4,860.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ODC[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD][/TD]
[TD]ODC[/TD]
[TD]$ -[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cleaning Fee[/TD]
[TD]$300.00[/TD]
[TD]$300.00[/TD]
[TD]$300.00[/TD]
[TD][/TD]
[TD]Cleaning Fee[/TD]
[TD]$300.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Conference Fee[/TD]
[TD]$1,500.00[/TD]
[TD]$1,500.00[/TD]
[TD]$1,500.00[/TD]
[TD][/TD]
[TD]Conference Fee[/TD]
[TD]$1,500.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Grand Total[/TD]
[TD]$6,660.40[/TD]
[TD]$6,386.40[/TD]
[TD]$5,843.90[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD]$6,660.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Labor Analysis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Labor Analysis[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Add'l cost for meeting[/TD]
[TD]$4,860.40[/TD]
[TD]$3,870.40[/TD]
[TD]$2,096.40[/TD]
[TD][/TD]
[TD]Add'l cost for meeting[/TD]
[TD]$4,860.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Annual Fee[/TD]
[TD]$0.00[/TD]
[TD]$3,200.00[/TD]
[TD]$8,100.00[/TD]
[TD][/TD]
[TD]Annual Fee[/TD]
[TD]$0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Cost of 4 meetings per year[/TD]
[TD]$19,441.60[/TD]
[TD]$18,681.60[/TD]
[TD]$16,485.60[/TD]
[TD][/TD]
[TD]Cost of 4 meetings per year[/TD]
[TD]$19,441.60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Cost of 3 meetings per year[/TD]
[TD]$14,581.20[/TD]
[TD]$14,811.20[/TD]
[TD]$14,389.20[/TD]
[TD][/TD]
[TD]Cost of 3 meetings per year[/TD]
[TD]$14,581.20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Cost of 2 meetings per year[/TD]
[TD]$9,720.80[/TD]
[TD]$10,940.80[/TD]
[TD]$12,292.80[/TD]
[TD][/TD]
[TD]Cost of 2 meetings per year[/TD]
[TD]$9,720.80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*************************[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[TD]*************************[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Last edited:
Upvote 0
Sir, you are a true gentleman, this is exactly what I was looking for. Thank you SO MUCH! now I just have to make it look pretty lol
 
Upvote 0
markmzz said:
Hi!


In G2 and copy down:

=INDEX($B$2:$D$14,ROWS($G$2:$G2),MATCH(G$1,$B$1:$D$1,0))


Markmzz
Click to expand...


Okay sorry I'm stumped again, It works beautifully however I am not trying to do this across multiple spreadsheets (the index being in another spreadsheet) and although I am pretty sure I'm typing it properly it is not linking to the desired sheet.
 
Upvote 0
Hsouiri said:
Okay sorry I'm stumped again, It works beautifully however I am not trying to do this across multiple spreadsheets (the index being in another spreadsheet) and although I am pretty sure I'm typing it properly it is not linking to the desired sheet.
Click to expand...

Try this in B2 of the Sheet 2:

=INDEX('Sheet 1'!$B$2:$D$14,ROWS($B$2:$B2),MATCH(B$1,'Sheet 1'!$B$1:$D$1,0))


[TABLE="class: grid, width: 375"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Items[/TD]
[TD]T&M[/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sec/Ops[/TD]
[TD]$4,770.40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]IT[/TD]
[TD]$90.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Labor[/TD]
[TD]$4,860.40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ODC[/TD]
[TD]$ -[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cleaning Fee[/TD]
[TD]$300.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Conference Fee[/TD]
[TD]$1,500.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Grand Total[/TD]
[TD]$6,660.40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Labor Analysis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Add'l cost for meeting[/TD]
[TD]$4,860.40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Annual Fee[/TD]
[TD]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Cost of 4 meetings per year[/TD]
[TD]$19,441.60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Cost of 3 meetings per year[/TD]
[TD]$14,581.20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Cost of 2 meetings per year[/TD]
[TD]$9,720.80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*************************[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[TD]********[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Last edited:
Upvote 0
markmzz said:
Try this in B2 of the Sheet 2:

=INDEX('Sheet 1'!$B$2:$D$14,ROWS($B$2:$B2),MATCH(B$1,'Sheet 1'!$B$1:$D$1,0))

Markmzz
Click to expand...

Awesome that worked thank you. My issue was that I placed the sheet name in the wrong place.

Thanks again for all of your help!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

Share this page

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