Create Drop-down List from Multiple Columns

thotto

New Member
Joined
Dec 15, 2019
Messages
2
Office Version
2019
Platform
MacOS
Screen Shot 2019-12-15 at 17.28.35.png
I have different accounts listed horizontally as in the picture. I'd like to create a data validation in a new cell that containing all the accounts I have i.e. "My credit card" and "Paypal". I've been working at this problem for hours. INDEX and MATCH don't work in this case. Why real-world data can't be as tame as what I was taught in Excel courses 😩? Can anyone offer any help?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
796
Are you asking how to create a list to place in the data validation list?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,112
Office Version
2019
Platform
Windows
Assuming that your account names span the range A3:Z3, enter this formula into AA2, then fill down to create a list of account names in consecutive cells.

=IFERROR(INDEX($A$3:$Z$3,AGGREGATE(15,6,COLUMN($A$3:$Z$3)/ISTEXT($A$3:$Z$3),ROWS($AA$2:$AA2))),"")

Then for your validation list, use

=OFFSET($AA$2,0,0,COUNTIF($AA:$AA,"<>"),1)

The list doesn't need to start in AA2, just somewhere convenient. It could be in a different sheet if needed.
 

thotto

New Member
Joined
Dec 15, 2019
Messages
2
Office Version
2019
Platform
MacOS
Assuming that your account names span the range A3:Z3, enter this formula into AA2, then fill down to create a list of account names in consecutive cells.

=IFERROR(INDEX($A$3:$Z$3,AGGREGATE(15,6,COLUMN($A$3:$Z$3)/ISTEXT($A$3:$Z$3),ROWS($AA$2:$AA2))),"")

Then for your validation list, use

=OFFSET($AA$2,0,0,COUNTIF($AA:$AA,"<>"),1)

The list doesn't need to start in AA2, just somewhere convenient. It could be in a different sheet if needed.
Thank you for taking the time to help me. I've learned several formulas that I'm not aware of, but I guess I'd need to reorganize the data.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,112
Office Version
2019
Platform
Windows
Does the method suggested not work for you?

While well organised data is always advisable, re-organising if you don't need to could solve one problem and create another.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,647
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top