Dynamic Dropdown list

[RPM7]

I'm trying to create a dependent data validation list but have encountered some issues.

I have 36 ranges and thought I would be able to use index & match to find the name of the active range based on the reference cell values.

If my input value was 80, the dependent drop down list should return range R_80,
Similarly if the reference value was 1050, the dropdown list should return the range R_1050.

Currently the "lookup_table" contains the Range names in row1, and the reference value in row2.
i.e Row 1 would be "=R_1050", & Row2 would be "1050".

When I put the formula in the data validation "Source", I thought once the range name was returned, it would display that range in the dropdown list.

=INDEX(Lookup_Table,1,MATCH($A20,Ref_Name,0))

I even changed the cells in row1 to include the =, but it just shows my dropdown list as "=R_1050"

Has anyone encountered this before or know a solution to it?

Here's a link to a copy of my worksheet.

https://drive.google.com/open?id=0B71xEOOjBHW2UVJwanNLZVRJQUE

Thanks.

 

[tygrrboi]

Edit.....


Just do this

=INDIRECT("R_" & $A20)

 

[RPM7]

Thanks tygrrboi, that worked perfectly.

I clearly over complicated it.

 

[tygrrboi]

Its easy to do. Before I edited the post it used to say


=INDIRECT("R_" & INDEX(Ref_Name,1,MATCH($A20,Ref_Name,0))

but then I realised it was saying. Look in A20, find whatever it says and search for that in the table. Then wherever it is in the table, go there and give me the value... But thats like saying. "just get the value from A20...