Dependent drop down list from Index Match

Hey All,

I have made an index match formula that pulls a name from a series of IDs. The name that is pulled in the cell with the index match formula is also a named range that I want to make into a dependent drop down list. For some reason, when I make a drop down list using the indirect formula that references the cell with the index match formula in it (the value in that sell is the same as the named range), it doesn't work.

Does anyone know why this might be happening? As a test, I used the indirect formula referencing the cell with the index match formula in it, and it also gives me a #ref error.

Cheers,
B

This will work:

your second drop-down

=INDIRECT(A2)

where A2 is a "Name" AND the named range is "Name"

e,g A2="Bill"

there is a named range "Bill"

Thanks John, I don't think I explained myself very well though, so I have attached an example of what I'm talking about.

In the attached, I am looking to have the red cell (J6) become a drop down list, depending on the selection made in the yellow cell (J3). The extra step is that the named range cannot be the same name as what is in J3, so I used a naming table (columns B and C) and an index / match formula to transform the name (seen in J4).

The Offset formula used to name the ranges is definitely working, because I have tested that in cell J5, but for some reason the indirect reference to J3 does not work when I try and make the dropdown for J6.