CODEWITHSUNDEEP
google-sheets
David
David

Reputation: 197

google sheet formula - get column letter by variable?

Given a formula, eg:

= FILTER(matrix!B3:B99, NOT(matrix!C3:C99 = "") )    )

Is it possible to pass a variable in place of column B reference? Eg:

= FILTER(matrix!$requiredCol3:$requiredCol99, NOT(matrix!C3:C99 = "") )    )

where $requiredCol is whatever value is always in A1 for example?

eg, A1=X formula is now equivilent to:

= FILTER(matrix!$X3:$X99, NOT(matrix!C3:C99 = "") )    )

Thanks

Upvotes: 1

Views: 2166

Answers (1)

JPV
JPV

Reputation: 27302

You should be able to use INDIRECT(), which takes a cell reference as string as (first) parameter. See if this works:

= FILTER(INDIRECT("matrix!"&A1&"3:"&A1&"99"), NOT(matrix!C3:C99 = "") )

EDIT: if you have the ranges in cells A1 and A2 try

=FILTER(INDIRECT(A1), INDIRECT(A2) <>"")

Upvotes: 1

Related Questions