Clash Royale CLAN TAG#URR8PPP

selecting data from a dropdown list in excel

I am trying to create a callable column of data in excel, based on a dropdown list. The idea is to create a list of all column names in a header cell at the end of the columns in the excel file, using Data Validation from the Data Menu in Excel.

Then, when you select the column name from the list, the entire column is populated with the column values from the name that you have referenced. It should look like this...

This is easy enough to do with IF loops when there are only a handful of columns you might need to call. But when there are hundreds of columns you want to be able to reference, the IF loop method means you will have to keep writing IF loops hundreds of times to get it to work.

My Question: Is there a programmable way to do this in excel (VBA or otherwise) for many columns? It is not realistic to keep writing hundreds of IF loops to make this work.

2 Answers
2

I am not entirely sure what you want to do now, but this would be an Idea how to store your values or just copy them strait from another place in your worksheets

Sub Test()

Dim Column_1 As Variant

Column_1 = Range("B1:B500")
'You could use "Column_1" to get your values whereever you need
End Sub

Sub Test_2()
If Sheets("OneOfYourSheets").Range("B1") = Sheets("OneOfYourSheets").Range("xyz1") Then
Sheets("OneOfYourSheets").Range("B2:B501") = Sheets("OneOfYourSheets").Range("xyz2:xyz501")
End If
End Sub


This is a textbook case for INDEX/MATCH.

INDEX

MATCH

In F2, enter this formula: =INDEX(A:D,ROW(),MATCH(F$1,$A$1:$D$1,0)), where ROW() gives you the row number and MATCH gives you the column number.

F2

=INDEX(A:D,ROW(),MATCH(F$1,$A$1:$D$1,0))

ROW()

MATCH

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.