Home

How do I use Excel to show a list from a sheet in a second column, dependant on the first column?

%3CLINGO-SUB%20id%3D%22lingo-sub-623277%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20Excel%20to%20show%20a%20list%20from%20a%20sheet%20in%20a%20second%20column%2C%20dependant%20on%20the%20first%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623277%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file%20with%20sample%20data%20and%20sample%20results.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-623189%22%20slang%3D%22en-US%22%3EHow%20do%20I%20use%20Excel%20to%20show%20a%20list%20from%20a%20sheet%20in%20a%20second%20column%2C%20dependant%20on%20the%20first%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623189%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20have%20a%20drop%20down%20list%20in%20column%20A%20-%20lets%20say%201%2C%202%2C%203%2C%204.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20list%20relates%20to%20one%20of%204%20sheets%20of%20data%2C%20say%20sheet%201%2C%20sheet%202%2C%20etc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20select%201%2C%20I%20want%20it%20to%20only%20show%20a%20list%20of%20data%20from%20sheet%201%20in%20Column%20B%2C%20likewise%20select%20if%20I%202%2C%20then%20only%20show%20a%20list%20of%20data%20from%20sheet%202%20in%20Column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20know%20how%20I%20do%20this%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-623189%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-623314%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20Excel%20to%20show%20a%20list%20from%20a%20sheet%20in%20a%20second%20column%2C%20dependant%20on%20the%20first%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bhere%20you%20go%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624207%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20Excel%20to%20show%20a%20list%20from%20a%20sheet%20in%20a%20second%20column%2C%20dependant%20on%20the%20first%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159971%22%20target%3D%22_blank%22%3E%40Moyna%20Good%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EIf%20without%20cosmetic%20when%3C%2FP%3E%0A%3CPRE%3E%3DCHOOSE(%24C%242%2C'Sheet%201'!%24A%241%3A%24A%244%2C'Sheet%202'!%24A%241%3A%24A%244%2C'Sheet%203'!%24A%241%3A%24A%244%2C'Sheet%204'!%24A%241%3A%24A%244)%3C%2FPRE%3E%0A%3CP%3Ein%20data%20validation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632295%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20Excel%20to%20show%20a%20list%20from%20a%20sheet%20in%20a%20second%20column%2C%20dependant%20on%20the%20first%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159971%22%20target%3D%22_blank%22%3E%40Moyna%20Good%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20these%20are%20the%203%20choices%20of%20formulas%20for%20the%20drop-down%20list%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%26nbsp%3BStatic%20categories%20and%20items%20using%20non-volatile%20CHOOSE%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCHOOSE(A2%2C%3C%2FP%3E%3CP%3E'Sheet%201'!%24A%241%3A%24A%244%2C%3C%2FP%3E%3CP%3E'Sheet%202'!%24A%241%3A%24A%244%2C%3C%2FP%3E%3CP%3E'Sheet%203'!%24A%241%3A%24A%244%2C%3C%2FP%3E%3CP%3E'Sheet%204'!%24A%241%3A%24A%244)%3C%2FP%3E%3CP%3E2.%26nbsp%3BDynamic%20categories%20and%20static%20items%20using%20volatile%20INDIRECT%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDIRECT(%22'Sheet%20%22%26amp%3BA3%26amp%3B%22'!%24A%241%3A%24A%244%22)%3C%2FP%3E%3CP%3E3.%26nbsp%3BDynamic%20categories%20and%20items%20using%20non-volatile%20INDEX%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCountedItems%3C%2FP%3E%3CP%3EMy%20choice%20is%20the%20last%20one.%20What%20about%20you%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Moyna Good
New Contributor

Hi - I have a drop down list in column A - lets say 1, 2, 3, 4. 

 

That list relates to one of 4 sheets of data, say sheet 1, sheet 2, etc

 

If I select 1, I want it to only show a list of data from sheet 1 in Column B, likewise select if I 2, then only show a list of data from sheet 2 in Column B.

 

Anyone know how I do this please?

 

 

4 Replies
Please attach your sample file with sample data and sample results.

@Moyna Good ,

If without cosmetic when

=CHOOSE($C$2,'Sheet 1'!$A$1:$A$4,'Sheet 2'!$A$1:$A$4,'Sheet 3'!$A$1:$A$4,'Sheet 4'!$A$1:$A$4)

in data validation

 

@Moyna Good 

In the attached file, these are the 3 choices of formulas for the drop-down list: 

1. Static categories and items using non-volatile CHOOSE: 

=CHOOSE(A2,

'Sheet 1'!$A$1:$A$4,

'Sheet 2'!$A$1:$A$4,

'Sheet 3'!$A$1:$A$4,

'Sheet 4'!$A$1:$A$4)

2. Dynamic categories and static items using volatile INDIRECT: 

=INDIRECT("'Sheet "&A3&"'!$A$1:$A$4")

3. Dynamic categories and items using non-volatile INDEX: 

=CountedItems

My choice is the last one. What about you?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies