Results 1 to 10 of 10

Thread: Referencing Sheet Based on Cell Value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2018
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Referencing Sheet Based on Cell Value

    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,

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,569
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    =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.
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Apr 2018
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    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 by Glasgowsmile; Apr 23rd, 2019 at 09:30 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,569
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    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
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    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.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,569
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    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.
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Apr 2018
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    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.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,569
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    Only this code.
    The sheet selected in A1 must exist in the book
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Apr 2018
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    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 by Glasgowsmile; May 9th, 2019 at 04:17 PM.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,569
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Referencing Sheet Based on Cell Value

    You're right, my mistake

    Quote Originally Posted by Glasgowsmile View Post
    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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •