Drop down look up Solved/Closed

Question

Hello all,

I have a 40 sheet workbook. I want to hide 39 of the tabs and use one sheet as the "master" to pull all of the data. I have the tabs in a drop-down and would like it to populate the information from that specific sheet.  I can't figure out Vlookup and Hlookup isn't working for me.  Any help is much appreciated!

vcoolio · Answer

Hello NMinnis,

Are the worksheets(2 - 40) already hidden?

Cheerio,
vcoolio.

vcoolio · Answer

Hello NMinnis,

Following is the link to a little sample that I prepared for you. It only has three sheets (1 - 3) but will give you an idea of how it could work. Sheet1 is the assumed Master sheet.

http://ge.tt/2z8o6Mn2

In the sample, select a sheet from the yellow drop down in cell G1 and then click on "GO". All the data from the selected sheet will be transferred to Sheet1.

Following is the code assigned to the "GO" button. It will first hide all sheets except Sheet1:-


Sub TransferData()

Dim ws As Worksheet
        Dim sh As Worksheet
        Dim Srch As String
        
Application.ScreenUpdating = False

For Each ws In Worksheets
       If ws.Name <> "Sheet1" Then
       ws.Visible = False
       End If
Next ws

Srch = Sheet1.[G1].Value
       Set sh = Worksheets(Srch)
       Sheet1.UsedRange.Offset(1).ClearContents
       sh.UsedRange.Offset(1).Copy
       Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheet1.[G1].ClearContents

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

If you feel that you don't need to hide any sheets, then remove lines 3 and 9 - 13 from the code above. 

Test the code in a copy of your workbook first. You may also need to change the drop down cell reference.

If you have any problems with getting this to work then upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box. We will then have something to work with and test. Please use dummy data in the sample and just supply a condensed version of your workbook with the Master sheet and two or three source sheets (with just a few rows of data in each).

I hope that this helps.

Cheerio,
vcoolio.

nminnis · Answer

I responded last night but it didn't register apparently. Glad I looked! WHY IS NOT UPLOADING?

http://ge.tt/2fDAeMn2

1. Attached is the "dummy" format that I'm using.  I need to have the drop down list report back to the master with the city that I choose.

2. I think I'll be able to use the same code, but I need the base rent to match the drop down and then the date provided within that cell.

Thanks for all of the time and attention!

vcoolio · Answer

Hello NMinnis,

I noticed that you have been having some problems with posting. I believe it has something to do with the fact that our tech team is working on a brand new look for the CCM site so at certain times of any day, as they work on the site, it could become a little frustrating to post but please just persevere.

Following is the link to your file with the code implemented:-

http://ge.tt/9wDtINn2

You'll notice that your file is a little different as I've made some alterations to make the whole process as seamless as possible. In the Master sheet, I have removed all formatting and just left what you see in row1 (including a button to execute the code). All merged cells in all sheets have been unmerged as merged cells create havoc with VBA coding. You can easily re-format the worksheets without merged cells.

I have also amended the code, which is assigned to the blue button in the Master sheet, as follows:-

Sub TransferData()

Dim ws As Worksheet
        Dim sh As Worksheet
        Dim Srch As String
        
Application.ScreenUpdating = False

Srch = Sheet1.[E1].Value
If Srch = vbNullString Then Exit Sub

Set sh = Worksheets(Srch)
       Sheet1.[A1].CurrentRegion.Offset(1).ClearContents
       sh.UsedRange.Copy
       Sheet1.[A3].PasteSpecial xlPasteAll

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Select a city from the drop down in cell E1 then click on the blue button to see details of your property in that city. When you select a new city from the drop down, the Master sheet will be refreshed with only the details of the new selection.

Create a copy of your actual workbook to test the code but remember to unmerge all merged cells in each sheet and remove all formatting from the Master sheet so that it looks something like the one in my sample.

I hope that this is a good start for you.

Cheerio,
vcoolio.

vcoolio · Answer

Hello again,

How do I not "grey" it in? 

In the code above (post #9), add the following line:-

Sheet1.[A1].Select

just above End Sub.

Can I do it without hitting a transfer button and just by clicking on the drop down?

In the Master sheet, remove the blue button.

Leave the code in post#9(with the newly added line) in its standard module. In the Master sheet module, paste the following code:-


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

TransferData

End Sub

To implement this code:-

- Right click on the Master sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the new code.

Now, each time that you select a new city from the drop down, the relevant data will be transferred to the Master sheet without having to click on a button.

Nevermind on #3, I got that one.'=HLOOKUP(E1,'Base Rent'!A1:E311,MATCH(B1, 'Base Rent'!A1:A517,0),FALSE)
Excellent! Well done!

Here's the link to the updated sample file:-

http://ge.tt/2OaZNNn2

I hope that this helps.

Cheerio,
vcoolio.

Drop down look up

5 responses

Similar discussions

Subscribe To Our Newsletter!