Sticky: Display Named Range contents based on text in cell

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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. :)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Aug 21, 2009
Messages
584
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Either a PT, either VBA code... the choice is yours.
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584

ADVERTISEMENT

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

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Then use something like this:

Code:
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

Code:
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

Well-known Member
Joined
Aug 21, 2009
Messages
584

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
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
 
Last edited:

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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

Well-known Member
Joined
Aug 21, 2009
Messages
584
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
 

Forum statistics

Threads
1,136,775
Messages
5,677,661
Members
419,710
Latest member
Kvt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top