Create Drop-down List from Multiple Columns

[thotto]

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?

 

[davesexcel]

Are you asking how to create a list to place in the data validation list?

 

[jasonb75]

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]

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]

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.