=index(indirect....)

[rob1987]

Hello,

Simple question I think...

I am using the following formula to produce a list of names in C based on either PO, PM or BA being entered in cell A1:

=INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))

However the names ranges PO,PM and BA are all different lengths, as I'm dragging the forumal down to cover enough cells for the longest range, BA, when I select the shortest, PO, I am getting 0 appear in the rest of the empty cells.

Is there anyway around this please?

Thanks for your help

 

[Dryver14]

can you give an example of some names

 

[rob1987]

Hi, they are just first names: John, James, Dave, etc...

There are 7 in the named range PO, 8 in the named range PM and 11 in the named range BA.

So if i select PO in A1 I am getting my list of 7 names but then th next 4 cells all contain 0

 

[Aladin Akyurek]

Where do you enter the formula?

 

[rob1987]

In C2:11

 

[Aladin Akyurek]

In C2:11

In C2 enter and copy down:

=INDEX(INDIRECT($A$1),ROWS($C$2:C2))

if the range behind the value in A1 has one dimension.

 

[tushiroda]

maybe the below would work. I think you need to handle empty cells in the named range and rolling past the length of the named range itself.

=IFERROR(IF(INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))="","",INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))),"")

or

=IF(ISERROR(INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))),"",IF(INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))="","",INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))))

 

[Dryver14]

if I am understanding you right you just dont want to see the zero's

try

=If(INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1))=0,"",INDEX(INDIRECT($A$1),ROW(A1),COLUMN(A1)))

 

[scottylad2]

Unashamedly stealing Barry Houdini and rorys example as a means of understanding it

Excel Workbook
	B	C	D
27	PO	BA	PM
28	john	steph	polly
29	allan	sally	pauline
30	dave	simone	christine
31	sally	shirley
32	simone	willie
33		colin
34		tom
35		tony
36
37			BA
38			polly
39			pauline
40			christine
41
42
43
44
Sheet1

Excel Workbook
	B	C	D
27	PO	BA	PM
28	john	steph	polly
29	allan	sally	pauline
30	dave	simone	christine
31	sally	shirley
32	simone	willie
33		colin
34		tom
35		tony
36
37			PM
38			steph
39			sally
40			simone
41			shirley
42			willie
43			colin
44			tom
Sheet1

 

[rob1987]

Tried Dryver14's formula, worked a treat, thank you.

Thank you all for your help!