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.

 

[wigi]

Hi

Supposing that you start your copy in row 1:

=IF(ROW()<=ROWS(DNR),INDEX(DNR,ROW());"")

DNR is the named range containing the other Dynamic named ranges.

The only drawback is that you have to copy this formula to enough cells to cover the longest DNR.

Wigi

 

[ClimoC]

Thanks, I think I know what you're getting at, but I'm trying to keep this as formula free as possible. Otherwise I'd just just do a dependant formula to return the values needed.

What I'm doing is making a 'make your own report' by where you get to pick from a selection of named_ranges, and it will paste them in a sheet.

So if there are DNRs "Dynamic1", "Dynamic2", "Dynamic3", "Dynamic4", and "Dynamic 5", you could fill 3 drop down lists with "Dynamic1" in the first, "Dynamic4" in the second, and "Dynamic5" in the third, and it would retrieve those three dynamic ranges and pop them in the worksheet.

I know that the more and more I say this the more and more a Pivot Table is exactly what I'm after - BUT I DON'T WANT ONE!

Whatever the long way around is for acheiving what a PivotTable does, that's what I want!!!!

Cheers in advance guys,
C

 

[wigi]

Either a PT, either VBA code... the choice is yours.

 

[ClimoC]

Yes Yes! VBA please!!!! Then I can assign a colourful button... Pretty...

 

[wigi]

Then use something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = "CellAddressWhereDNRisChosen" Then
    
        Sheets("NameOfTheSheetWithSourceData").Range(Target.Value).Copy Range("UpperLeftCellToPasteData")
    
    End If

End Sub

For instance

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = "$E$1" Then
    
        Sheets("Sheet1").Range(Target.Value).Copy Range("A1")
    
    End If

End Sub

Wigi

 

[ClimoC]

Lol - Cheers for all your help Wigi. I had given up and have already started doing exactly as you've shown above.

Since this is now the path I must take for the desired result - can anyone help me speed up this process a little?

Code as the moment is looking like this

Sub Buildyourown()
Application.ScreenUpdating = False
If Range("Tools!G5").Value = "Title" Then
Range("Title").Copy Worksheets("Sheet2").Range("B3")

ElseIf Range("Tools!G5").Value = "Series" Then
Range("Series").Copy Worksheets("Sheet2").Range("B3")
ElseIf Range("Tools!G5").Value = "Episode" Then
Range("Episode").Copy Worksheets("Sheet2").Range("B3")
ElseIf Range("Tools!G5").Value = "Destination" Then
Range("Destination_Boxes").Copy Worksheets("Sheet2").Range("B3")
ElseIf Range("Tools!G5").Value = "Reason For Push" Then
Range("Reason_For_Push").Copy Worksheets("Sheet2").Range("B3")
ElseIf Range("Tools!G5").Value = "Visible From" Then
Range("Visible_From").Copy Worksheets("Sheet2").Range("B3")


Now this does exactly what I want it to - except that it's going to take forever writing out all the individual if statements.

Since I have circa 40 DNRs, and I need to write the above code c.40x for each cell in 'Tools!' (looking like being up to 40 as well) is there something I can do to not have to write 1600 'ElseIf' statements?

Something like a "Find the value in 'Tools!G5', find the DNR of the exact same name as the value of this particular input cell and THEN do the ifstatement?

That way I only have to write 40 statements, then just copy and paste and change the input cell in each 'find' statement.

___________________________
"Whoever said Football is the most exhausting thing in the world obviously hasn't written a macro"

 

[MickG]

Hi, The code assumes you Validation list is in "sheet (2) cell D1"
When you select a "Named Range" from the Validation list, the code will copy this range From sheet (1) to sheet (3) column "A".
NB:- Change the Reference to Cell "D1" to you validation cell address.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]If[/COLOR] Target.Address(0, 0) <> "D1" [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
 ActiveWorkbook.Names(Sheets("Sheet2").Range("D1").Value).RefersToRange.Copy _
 Sheets("sheet3").Range("A1")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

You can obviously change "D1" to Target.value (Silly me)
Regards Mick

 

[ClimoC]

Thanks Mick - that looks like exactly what I want ("RefersToRange")

Umm... I'm a little bit thick though... where do I put this code?

I have copied and pasted the code, saved it, but can't find it as a macro to run. I tried changing it from 'Private Sub' to 'Sub' and saved, still no result.

Is it safe to change the "Private Sub Worksheet_Change(ByVal Target As Range)" bit or is that part of what it's doing?

Reason is I need to be able to assign this to a button...

Cheers mate
C

 

[ClimoC]

Also - let me offer some specifics (might help! lol)

• Input range is Sheet 'Tools!', input cell 'G5' (Data validation list)
• All the Dynamic Named Ranges are on a sheet called 'schedule'
• Output cell is 'Sheet3!' starting at "B3"

So Read the value of 'Tools!G5', referstorange in 'Schedule', output on 'sheet3!B3'

Now my code looks like this (thanks Mick) but still nothing happens, though I think it's getting the right idea.

The values in 'Tools!G5' exactly match the DNR's names.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "Tools!G5" Then Exit Sub
ActiveWorkbook.Names(Sheets("Tools").Range("G5").Value).RefersToRange.Copy _
Sheets("sheet3").Range("B3")
End If

End Sub




Also - can I change this to run as a macro - not when the cell is modified? I want to assign it (and several others) to a controlbutton.

Ta
C