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

 

[Joe4]

If your named range is the value in a cell, then you need to use the INDIRECT function to tell Excel that the value is a range reference, i.e.

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

You can Google the INDIRECT function for more details, explanations, and examples.

 

[Fluff]

As you have 365, you can also do it like

+Fluff 1.xlsm
	A												M	N	O	P	Q	R	S	T	U
1	Named_Range
2	Name1												Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8
3	Name2
4	Name3
5	Name4
6	Name5
7	Name6
8	Name7
9	Name8
10	Name9
Main

Cell Formulas
Range	Formula
M2:T2	M2	=TRANSPOSE(SWITCH(A1,"Named_Range",Named_Range))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Named_Range	=Main!$A$2:$A$9	M2

 

[jcooooper]

If your named range is the value in a cell, then you need to use the INDIRECT function to tell Excel that the value is a range reference, i.e.

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

You can Google the INDIRECT function for more details, explanations, and examples.

Thanks Joe, I tried this but seem to be getting the #REF.

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

A1 = named_range

when I go into evaluate formula, it seems to bug out at the first mention of A1. it's returning the named_range as "named_range" rather than named_range. I tried both TRUE and FALSE within DIRECT.

Any other ideas?

 

[jcooooper]

As you have 365, you can also do it like

+Fluff 1.xlsm
	A												M	N	O	P	Q	R	S	T	U
1	Named_Range
2	Name1												Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8
3	Name2
4	Name3
5	Name4
6	Name5
7	Name6
8	Name7
9	Name8
10	Name9
Main

Cell Formulas
Range	Formula
M2:T2	M2	=TRANSPOSE(SWITCH(A1,"Named_Range",Named_Range))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Named_Range	=Main!$A$2:$A$9	M2

Cheers fluff.

No luck with this one either. Would that formula work given the " "? The formula will always be returning a dynamic set of values (numbers or text), based on what the named_range is referencing

 

[Fluff]

Is the name of the named range in A1 & is it spelt exactly the same as the name of the range?

 

[jcooooper]

Is the name of the named range in A1 & is it spelt exactly the same as the name of the range?

Thanks fluff.

How would this work if the value in cell A1 changes? As that will be a dropdown list of named ranges. is " " hard coded?

I'm also trying to only return/transpose the first column of named_range. But using the above returns the whole array. Sorry should have specified that.

 

[jcooooper]

(deleting double post)

 

[Fluff]

How many names do you have?

 

[jcooooper]

Named ranges? About 50.