CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
mp3por
mp3por

Reputation: 1852

Dynamically set range based on column number

I have a table with headings ID, PROFIT, COST. Those headings can be in any order. I have to define a generic approach to get the:

My problem is that in my current formulas I have hardcoded the columns which is a problem since they can come in any order.

I have devised a solution on how to get the correct column number. I have created a settings sheet with 1 column - all the possible column names. I then look up the correct column number by using the following formula: 

=MATCH(Settings!B2,A1:1,False)

I don't know how to transfer this COLUMN NUMBER to the range of form: (columnNumber2:columnNumber). The data is always 1 row of headers and then the data.

Please help

Upvotes: 1

Views: 730

Answers (2)

player0
player0

Reputation: 1

=COUNTA(INDIRECT(ADDRESS(1, MATCH("ID", A1:1, 0), 4)&":"&
      SUBSTITUTE(ADDRESS(1, MATCH("ID", A1:1, 0), 4), 1, )))

=SUM(INDIRECT(ADDRESS(1, MATCH("PROFIT", A1:1, 0), 4)&":"&
   SUBSTITUTE(ADDRESS(1, MATCH("PROFIT", A1:1, 0), 4), 1, )))

=AVERAGE(INDIRECT(ADDRESS(1, MATCH("COST", A1:1, 0), 4)&":"&
       SUBSTITUTE(ADDRESS(1, MATCH("COST", A1:1, 0), 4), 1, )))

Upvotes: 0

player0
player0

Reputation: 1

=INDIRECT(  ADDRESS(1, MATCH(Settings!B2, A1:1, 0), 4)&":"&
 SUBSTITUTE(ADDRESS(1, MATCH(Settings!B2, A1:1, 0), 4), 1, ))

0

Upvotes: 1

Related Questions