Reference individual elements from a Non-Contiguous Range Name

[RelearningExcel]

I have several tables on one sheet, each of which has a Grand Total value at the bottom right of the summed data range

I have manually selected (using ctrl + left mouse click) each Grand Total cell and defined all these cells as a Named Range called Grand_Totals

I thought it would be possible to reference each of the cells in the Grand_Totals range individually.

The =Index function works fine if the data is contiguous but I can't figure out how to get anything other than the first element of the Grand_Totals range

So for example

Lets say the cells of the Grand_Totals non-contiguous named range are F10,N10,F30,N30,F50,N50

How would I reference the third element/cell (F30) in this range.
I should point out that I actually need to reference all the cells in the Grand_Totals range and the range has a lot more than 6 cells

Thanks in advance

Bill

 

[Rick Rothstein]

Each contiguous range within a non-contiguous range can be referenced through the non-contiguous range's Areas property.

Dim Ar As Range
.....
.....
For Each Ar In Range("Grand_Totals").Areas
  '
  '  If you had more than one cell in an area, you would need another loop to iterate each Ar
  '  In your particular case, however, Ar is a reference to an individul "Grand Total" cell.
  '
Next

 

[Mohammad Basem]

Use this syntax,

INDEX(reference, row_num, [column_num], [area_num])

So,
=INDEX(Grand_Totals,1,1,1) refers to F10
=INDEX(Grand_Totals,1,1,3) refers to F30

Try to examine which area refers to which cell.

 

[Rick Rothstein]

Each contiguous range within a non-contiguous range can be referenced through the non-contiguous range's Areas property.

Dim Ar As Range
.....
.....
For Each Ar In Range("Grand_Totals").Areas
  '
  '  If you had more than one cell in an area, you would need another loop to iterate each Ar
  '  In your particular case, however, Ar is a reference to an individul "Grand Total" cell.
  '
Next

Sorry, I responded to the above question based on my assumption that you were doing this in VB (not sure why I came to that conclusion, but I did). When I saw Mohammad's response, I went back and read your message again.... and I saw the =INDEX reference. So, the answer is still "areas", but as Mohammad has shown you, in a formula you would address them via the INDEX function's third (optional) argument.

 

[RelearningExcel]

Thanks guys.

Can't believe I missed the [area_num] argument !!!

But thanks, both solutions work fine

 

[Mohammad Basem]

Most welcome and thank you for the feedback!