Count rows referencing named range in cell

[jcooooper]

Hi all,

I am trying to return the text strings in a named range.

When I use the named_range directly the formula, it works fine:

=TRANSPOSE(OFFSET(named_range,,,ROWS(named_range),1))

When I use a cell reference, it doesn't seem to work.

A1 = named_range

=TRANSPOSE(OFFSET(A1,,,ROWS(A1),1))

Any ideas?

Many thanks

 

[Fluff]

In that case you are better of going with Joe's idea of using Indirect.

 

[jcooooper]

I tried however it looks like INDIRECT doesn't work when a named range is dynamic (which all 50 are).

Thanks anyway for the suggestion, much appreciated!

 

[Joe4]

I tried however it looks like INDIRECT doesn't work when a named range is dynamic (which all 50 are).

Please explain what you mean by this.
Do you mean that the value in cell A1 is changing, and the formula does not update automatically?
If so, that is because INDIRECT is not a volatile function.

If that is the case, how is the value in cell A1 (named range) being changed?

 

[Fluff]

it looks like INDIRECT doesn't work when a named range is dynamic

Your quite right it doesn't. The only other way I know (short of using VBA) is the switch function, but 50 named ranges in that would be very long.