My intention was to use a formula in a named range, not a named range
in a formula. I think my problem is too complex for Excel formulas,
so I'm working on a VBA solution which is much easier.

By defining a range of non-contiguous data, I want to loop through the
cells in each area, capture (both column and row) offsets which are
then used elsewhere. I have this working, but in a different way to
originally intended.

I'm analysing the values columns, a column offset of Column A, using

Set rngValues = rngCols.SpecialCells(xlCellTypeConstants, 1)

This captures various Areas in the desired columns and, because of the
nature of the data, any values I require are part of single-cell
Areas. I loop through each Area, if the Area.Cells.Count=1, then I
capture offsets of that Area for other values I'm looking for.

Thanks for your responses

Paul