Drop down look up [Solved]

nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 16, 2017 at 12:06 PM - Latest reply: vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen
- Nov 21, 2017 at 10:21 PM
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!
See more 

14 replies

Reply to this topic
vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Nov 16, 2017 at 06:29 PM
0
Helpful
1
Hello NMinnis,

Are the worksheets(2 - 40) already hidden?

Cheerio,
vcoolio.
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 16, 2017 at 06:54 PM
No they are still in view. For example to help understand: I have 30 restaurants and I have all of their information in their individual sheet. I would like to have one sheet where I use a drop down and all of that information on that specific store appears. This way I don't have to scroll through 50 tabs. Thanks for the quick response!
Respond to vcoolio
vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Nov 16, 2017 at 07:56 PM
0
Helpful
2
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 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 17, 2017 at 10:26 AM
Will you please work now?

'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!
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 17, 2017 at 02:35 PM
FYI...when I said "will you please work now" it wasn't letting me load any of my messages and then of course it did there. That had nothing in reference to vcoolio or anyone else...very grateful for the help!
Respond to vcoolio
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 17, 2017 at 10:22 AM
0
Helpful
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!
Respond to nminnis
vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Nov 18, 2017 at 07:03 AM
0
Helpful
2
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.
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 18, 2017 at 08:10 AM
Thank you again! A few questions:

1. Can I do it without hitting a transfer button and just by clicking on the drop down?
2. How do I not "grey" it in?
3. For my base rent, I have '=HLOOKUP(E1,'Base Rent'!A1:E311,2,FALSE) but how do I get it to match with the date that is inserted in B1? Which I can make a drop down menu, but they will all be first of the month?

Thanks again!
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 18, 2017 at 08:18 AM
Nevermind on #3, I got that one.

'=HLOOKUP(E1,'Base Rent'!A1:E311,MATCH(B1, 'Base Rent'!A1:A517,0),FALSE)
Respond to vcoolio
vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Nov 18, 2017 at 08:51 AM
0
Helpful
4
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.
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 18, 2017 at 09:02 AM
It is very true that not all heroes where capes! You are my savior! However, with this one change, it now makes one more necessary change and then I'm done!!!

I need to keep B6 and B15 all keep the existing formula. B6 has the Hlookup, B15 is going to be a SUM equation for the rows above.

Thanks!
vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen > nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 18, 2017 at 09:30 AM
A slight amendment to the code in the standard module 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
       
Sheet1.[B6].Formula = "=IF(B1="""","""",HLOOKUP(E1,'Base Rent'!A1:E311,MATCH(B1, 'Base Rent'!A1:A517,0),FALSE))"

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet1.[A1].Select

End Sub


should do it.

You'll notice that I've made a slight amendment to your formula so that you don't receive a #N/A error in B6 (when a date isn't present in B1).

The SUM formula for B15 will be transferred across from each source sheet when a source sheet is selected.

I hope that this helps.

Cheerio,
vcoolio.
nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 21, 2017 at 06:18 PM
Thank you so so much!
vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen > nminnis 9 Posts Thursday November 16, 2017Registration date November 21, 2017 Last seen - Nov 21, 2017 at 10:21 PM
You're welcome Nminnis. I'm glad that I was able to help.

Cheerio,
vcoolio.
Respond to vcoolio