Indirect formula issue

[StuartWhi]

Hi Guru’s,

Need to your help in making an address reference within an “indirect” formula to be referencing a cell not text (I guess).
I have the following,
A list selector in B4 which is pointing to Name references “Flist” and “Slist” (H2:H3)
A list option which uses an Indirect formula to references the list selector in B4 so the user can select from the Name References i.e. 1 to 5 or a to e
This works without any issues as it’s a cell reference B4.

The problem is I need the Indirect to be a formula not a directly inputted cell reference and I’m using,
=Indirect(Address(4,2,4)) ‘*Simplified original formula can be seen in this post*'
When above is placed in a cell or in a Data Validation it returns what is written in Cell B4 “Flist” not the option of selecting from the Name References list.

See below for more details.
Excel 2010

	A	B	C	D	E	F	G	H
1
2								Flist
3								Slist
4	List Select	Flist	=INDIRECT(H2:H3) 'in Data Validation***'
5	List Option	1	=INDIRECT(B4) 'in Data Validation***'					Flist
6								1
7								2
8	Indirect	3	=INDIRECT(B4)					3
9	Address	B4	=ADDRESS(4,2,4)					4
10	Indirect + Address	Flist	=INDIRECT(ADDRESS(4,2,4))					5
11	Data Validation	Flist	=INDIRECT(ADDRESS(4,2,4)) 'in Data Validation***'
12
13								Slist
14								a
15								b
16								c
17								d
18								e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Thanks for your assistance in advance,

Stuart.

 

[pgc01]

Hi Stuart

If I understand correctly, you want 2 levels of indirection

=INDIRECT(ADDRESS(4,2,4))

Gives you what is in cell B4: the string "FList"

=INDIRECT(INDIRECT(ADDRESS(4,2,4)))

will in a second step point to the named range with name "Flist".

Please try.

 

[StuartWhi]

Thanks PGC,

So simple that I should have seen it, I’m sure you saved me several more days of wasted time.

Stuart.

 

[pgc01]

You're welcome. Thanks for the feedback.