CODEWITHSUNDEEP
excel-formula
user1
user1

Reputation: 444

Store column name in an extra cell

By now, I use something like =function(tablename[[Columnname]:[Columnname]]). Is there an (easy) way to store Columnname in cell X1 and use something like =function(tablename[[X1]:[X1]])

My table named tablename:

ColumnA ColumnB
   1       a
   2       b
   3       c
   4       d

Cell A1 = ColumnA <-the string

Cell B1 = =COUNTA(tablename[[ColumnA]:[ColumnA]])

Cell A2 = ColumnB <-the string

Cell B2 = =COUNTA(tablename[[ColumnB]:[ColumnB]])

My aim is that the formulas in B1 and B2 use the values in A1 and A2.

What I tried yet is something like: =COUNTA(tablename[[INDIRECT(A1)]:[INDIRECT(A1)]])

Upvotes: 1

Views: 63

Answers (1)

user4039065
user4039065

Reputation:

The INDIRECT function converts a text string to a usable cell reference or in your case a structured (list object) table name.

=VLOOKUP(K3, INDIRECT(J3), 3, FALSE)

enter image description here

In my sample worksheet illustrated above, J3 is a Data Validation List with a source of AA2:AA3. K3 is a typed value and L3 contains the formula. If J3 is switched to Table1 then L3 becomes 66.

Upvotes: 1

Related Questions