Sticky: Display Named Range contents based on text in cell

[ClimoC]

Hey Excellers...

I have some dynamic named ranges in sheet1, and in sheet2 I have data validation dropdown list which has the names of all the DNR's in it.

What I want it to do (in sheet3, no less) is for the user to be able to pick a named range from the dropdown list, and have a particular column in sheet3 then display that entire named range.

I have made a 'data' worksheet which is the source for the data validation in sheet2. Each item in the list identically matches the name of each of the named ranges. I was hoping to be able to use some form of =INDIRECT but alas, no such luck.

So it's almost like a copy and paste function I'm after, where:

If you pick "Schedule_From" out of the DD-list, then DNR 'Schedule_From' is what is pasted in Column B in Sheet3.

Is this possible? It's rattling my brain something fierce...

Oh - and I definately DON'T want to use a PivotTable.

 

[MickG]

Hi, This code will copy Data from Named Range "Sheet Tools "G5" to sheet3, "B3".

[COLOR="Navy"]Sub[/COLOR] MG24Aug55
ActiveWorkbook.Names(Sheets("Tools").Range("G5").Value).RefersToRange.Copy _
 Sheets("sheet3").Range("B3")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[wigi]

FYI, except for a typo (Target should be Target.Address in the 2nd line), my suggestion in post # 6 also worked.