12

I have a named range (NamedRange1) and I need to reference the first row and first column of that range within a formula. I came up with the following:

  • First row:

    INDIRECT("R" & ROW(UnpivotSource) & "C" & COLUMN(UnpivotSource) & ":R"& ROWS(UnpivotSource) +ROW(UnpivotSource) -1 & "C" & COLUMN(UnpivotSource),0)

  • First column:

    INDIRECT("R" & ROW(UnpivotSource) & "C" & COLUMN(UnpivotSource) & ":R"& ROW(UnpivotSource) & "C" & COLUMNS(UnpivotSource) + COLUMN(UnpivotSource) -1,0)

This seems cumbersome, especially since the Excel object model exposes Rows and Columns in VBA. Is there a more concise way of expressing this?

Lance Roberts
  • 8,837
technomalogical
  • 733

1 Answers1

18

Row:

INDEX(UnpivotSource,1,0)

Column:

INDEX(UnpivotSource,0,1)

Note that this will only work for contiguous named-ranges, there's an index function for areas of a range, but it could get tricky if you wanted something other than the first row or column.

Lance Roberts
  • 8,837