[Help] Look up Data, Variable sheet names

[Glasgowsmile]

I have a drop down list A-Z, and I have Sheets A-Z, each sheet has data specific to that letter.

I want to be able to select a letter in the drop down and then below the drop down it pulls all the data from that specific sheet name into the current working sheet. How would I go about doing this? I've looked at vlookup and indirect but can't seem to get it to work correctly.

 

[mumps]

What is the name of the sheet with the drop down list and in which cell is the list? As you choose a letter from the list, do you want the data from each sheet to be placed underneath each other or do you want to clear the old data and replace it with the most recently selected sheet?

 

[Glasgowsmile]

Good questions.

The sheets are named exactly the same as the drop down list. So if I pick D in the drop down list, D is the name of the sheet as well.

I would just want the from D to be placed under the drop list A1:F30, It would be ideal to clear the area and then put the newly selected sheet.

 

[jim may]

Here's ONE WAY....
Below Sheet "Master" Shows the Contents of Sheet "C" AFTER Selecting C from dropdown in Cell C2 AND THEN Running the Macro Foo...

Sub Foo()
Dim Ws As Worksheet
Dim LR As Long, HeaderRow As Integer
HeaderRow = 5
LR = Range("A" & Rows.Count).End(xlUp).Row
If LR < HeaderRow Then LR = 5
Range("$A$5:$A$" & LR).EntireRow.Delete
Select Case Range("$C$2")
    Case "A"
        With Worksheets("A")
            SLR = .Cells(Rows.Count, 1).End(xlUp).Row
            SLC = .Cells(5, .Columns.Count).End(xlToLeft).Column
            .Range(.Cells(5, 1), .Cells(SLR, SLC)).Copy _
            Destination:=Worksheets("Master").Range("A5")
        End With
    Case "B"
        With Worksheets("B")
            SLR = .Cells(Rows.Count, 1).End(xlUp).Row
            SLC = .Cells(5, .Columns.Count).End(xlToLeft).Column
            .Range(.Cells(5, 1), .Cells(SLR, SLC)).Copy _
            Destination:=Worksheets("Master").Range("A5")
        End With
     Case "C"
        With Worksheets("C")
            SLR = .Cells(Rows.Count, 1).End(xlUp).Row
            SLC = .Cells(5, .Columns.Count).End(xlToLeft).Column
            .Range(.Cells(5, 1), .Cells(SLR, SLC)).Copy _
            Destination:=Worksheets("Master").Range("A5")
        End With
End Select
End Sub

Excel 2010

	A	B	C
Select SheetName of
Data Desired >>>>
XXX	YYY	ZZZ

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]153[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]446[/TD]
[TD="align: right"]369[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]377[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]219[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]269[/TD]
[TD="align: right"]339[/TD]
[TD="align: right"]309[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]208[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]282[/TD]

</tbody>

Master​

 

[mumps]

I understand that the source sheets are named exactly the same as the drop down list. I assume, however, that you have another sheet other than those named A to Z where you want the data to be placed and that the drop down list would be on that sheet. If this is correct, what is the name of the destination sheet and in which cell is the drop down list on that sheet?

 

[Toadstool]

You can do this with CELL and INDIRECT.

So let's put the dropdown selection into cell K2.
Now put out of the way somewhere (maybe N1) enter =CELL("address",A1) and copy that across 5 columns then those cells copy down 29 rows. It will look something like this:

$A$2	$B$2	$C$2	$D$2	$E$2	$F$2
$A$3	$B$3	$C$3	$D$3	$E$3	$F$3
$A$4	$B$4	$C$4	$D$4	$E$4	$F$4
$A$5	$B$5	$C$5	$D$5	$E$5	$F$5

<colgroup><col style="width:48pt" width="64" span="6"> </colgroup><tbody>
[TD="width: 64"]$A$1[/TD]
[TD="width: 64"]$B$1[/TD]
[TD="width: 64"]$C$1[/TD]
[TD="width: 64"]$D$1[/TD]
[TD="width: 64"]$E$1[/TD]
[TD="width: 64"]$F$1[/TD]

</tbody>

Wherever you want to pull in the first cell enter =INDIRECT($K$2&"!"&N1) so this takes the sheet id from the dropdown at k2, puts an ! to indicate it's a sheet name and the N1 is the first value from the table we created, $A$1.

Now copy that =INDIRECT($K$2&"!"&N1) across 5 columns then copy those 6 cells down 29 rows. e.g. the third row down, 4th column across will have =INDIRECT($K$2&"!"&R3)

Whatever sheet you select in the dropdown it will now go and retrieve the 6 columns and 30 rows from tat sheet and replace the formulae you entered.