Drop down selection?

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!

ItemsT&MBasicStandard
Sec/Ops$4,770.40$4,502.40$3,966.40
IT$90.00$84.00$77.50
Labor$4,860.40$4,586.40$4,043.90
ODC $ - $ - $ -
Cleaning Fee$300.00$300.00$300.00
Conference Fee$1,500.00$1,500.00$1,500.00
Grand Total$6,660.40$6,386.40$5,843.90
Labor Analysis
Add'l cost for meeting$4,860.40$3,870.40$2,096.40
Annual Fee$0.00$3,200.00$8,100.00
Cost of 4 meetings per year$19,441.60$18,681.60$16,485.60
Cost of 3 meetings per year$14,581.20$14,811.20$14,389.20
Cost of 2 meetings per year$9,720.80$10,940.80$12,292.80

<tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
"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
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!

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))


ABCDEFGH
1ItemsT&MBasicStandardItemsT&M
2Sec/Ops$4,770.40$4,502.40$3,966.40Sec/Ops$4,770.40
3IT$90.00$84.00$77.50IT$90.00
4Labor$4,860.40$4,586.40$4,043.90Labor$4,860.40
5ODC$ -$ -$ -ODC$ -
6Cleaning Fee$300.00$300.00$300.00Cleaning Fee$300.00
7Conference Fee$1,500.00$1,500.00$1,500.00Conference Fee$1,500.00
8Grand Total$6,660.40$6,386.40$5,843.90Grand Total$6,660.40
9Labor AnalysisLabor Analysis
10Add'l cost for meeting$4,860.40$3,870.40$2,096.40Add'l cost for meeting$4,860.40
11Annual Fee$0.00$3,200.00$8,100.00Annual Fee$0.00
12Cost of 4 meetings per year$19,441.60$18,681.60$16,485.60Cost of 4 meetings per year$19,441.60
13Cost of 3 meetings per year$14,581.20$14,811.20$14,389.20Cost of 3 meetings per year$14,581.20
14Cost of 2 meetings per year$9,720.80$10,940.80$12,292.80Cost of 2 meetings per year$9,720.80
15
*************************************************************************************************

<tbody>
</tbody>


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
Hi!


In G2 and copy down:

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


Markmzz


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
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.

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))


ABCD
1ItemsT&MSheet 2
2Sec/Ops$4,770.40
3IT$90.00
4Labor$4,860.40
5ODC$ -
6Cleaning Fee$300.00
7Conference Fee$1,500.00
8Grand Total$6,660.40
9Labor Analysis
10Add'l cost for meeting$4,860.40
11Annual Fee$0.00
12Cost of 4 meetings per year$19,441.60
13Cost of 3 meetings per year$14,581.20
14Cost of 2 meetings per year$9,720.80
15
************************************************

<tbody>
</tbody>


Markmzz
 
Last edited:
Upvote 0
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

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

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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