Retrieve a list from another sheet

Bnygren

Board Regular
Joined
Feb 11, 2004
Messages
94
I have a list of 800 stores broken down by area, region, and division.

Here's what I'd like to do:

Select the Region (from a drop down box - using Data Validation(I've got that part))
When the region is selected, the cells below will populate with the areas that "belong" to that region (this is where I am struggling)

I'd like to do the same with areas and restaurants.

I know I can use data validation to get another drop down box with the areas listed, but I need them to populate the cells as a list.

Does that make sense??

Thanks,

Bob
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I just had a similar issue. Using the "record" function, I fleshed out the following macro. 2 hints. 1) you need to make sure that the items in your validationlist match your range names exactly. 2)You will need to add the ".Value" argument to the "Application.Goto" lines.


I hope it helps

Joe

Sub Stage()
'
' Stage Macro
' Macro recorded 2/9/2005 by Joe Berry
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Input").Select
Range("C1").Select
Application.Goto Reference:=Range("c1").Value
Selection.Copy
Sheets("Staging").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("Input").Select
Range("C2").Select
Application.Goto Reference:=Range("C2").Value
Application.CutCopyMode = False
Selection.Copy
Sheets("Staging").Select
Range("E1").Select
ActiveSheet.Paste
Sheets("Input").Select
Range("C1").Select
Application.CutCopyMode = False
End Sub
 
Upvote 0
I just had a similar issue. Using the "record" function, I fleshed out the following macro. 2 hints. 1) you need to make sure that the items in your validationlist match your range names exactly. 2)You will need to add the ".Value" argument to the "Application.Goto" lines.


I hope it helps

Joe

Sub Stage()
'
' Stage Macro
' Macro recorded 2/9/2005 by Joe Berry
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Input").Select
Range("C1").Select
Application.Goto Reference:=Range("c1").Value
Selection.Copy
Sheets("Staging").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("Input").Select
Range("C2").Select
Application.Goto Reference:=Range("C2").Value
Application.CutCopyMode = False
Selection.Copy
Sheets("Staging").Select
Range("E1").Select
ActiveSheet.Paste
Sheets("Input").Select
Range("C1").Select
Application.CutCopyMode = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,203,605
Messages
6,056,269
Members
444,853
Latest member
sam69

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
Back
Top