Referencing Sheet Based on Cell Value

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
280
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon,

I have a drop down list that changes and the short names in the drop down list match the names of the sheets with the data on it.

I want to select something from the drop down and then it automatically pull all the data from that same named sheet into a cell below the drop down.

How would I go about something like this?

Thanks,
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
=INDIRECT("'" & C3 & "'!A3")

Where C3 is the cell with drop down list and A1 is a cell of the sheet where you want the data.
 
Upvote 0
How would I pull everything in that sheet though?

For example, I have a drop down that says SADNJ and a Sheet named SADNJ and the SADNJ sheet has data in cells B2:AC45 -- how would I pull all of that data in and put it right underneath the drop down on another sheet?

Would I need to change the A3 in your forumula to A4, A5, A6 etc... for each cell?

I should mention the data I want to pull in is formatted too and has merged cells. How do I keep all of that as well?
 
Last edited:
Upvote 0
You can do all the formulas or execute this macro in your sheet with drop down list

Note: In cell A1 drop down list

Code:
Sub Referencing_Sheet()
    Sheets(Range("A1").Value).Range("B2:AC45").Copy Range("B2")
End Sub
 
Upvote 0
This worked 1 time but when I change the drop down to another selection it doesn't update below, it just still shows the previous data it pulled from the other sheet.
 
Upvote 0
If you want it in automatic, then put the following code in the events of your sheet

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If targer.Value = "" Then Exit Sub
        Sheets(Range("A1").Value).Range("B2:AC45").Copy Range("B2")
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
 
Upvote 0
Your last bit of coding -- is that to replace everything before or is that On top of everything else?

I tried that code in the sheet and just get a run time 424 error.
 
Upvote 0
Only this code.
The sheet selected in A1 must exist in the book
 
Upvote 0
I discovered the reason it wouldn't work was due to a spelling error in your code - Targer instead of Target.

Got that fixed -- thanks!

How do I copy all the formatting and everything as well? It currently isn't doing that.

I have merged cells and text it isn't bringing over with it.
 
Last edited:
Upvote 0
You're right, my mistake:oops:

I discovered the reason it wouldn't work was due to a spelling error in your code - Targer instead of Target.

Got that fixed -- thanks!

How do I copy all the formatting and everything as well? It currently isn't doing that.

I have merged cells and text it isn't bringing over with it.

Fixed code, it must copy all the format and combined cells of the other sheet to the sheet with the macro

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        Sheets(Range("A1").Value).Range("B2:AC45").Copy Range("B2")
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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