Stock lookup

[vapohendo]

Hi all,

I have a vague idea, but wanted a second opinion to see if its possible.

Let's say I have a massive list of data, contains product types, locations, serial numbers and so on. What I want to do, is be able to say, on another sheet, show me all of this product type and then have a copy of all the rows appear below, or if I only want the stock from a particular location, it shows them only.

So, if I have vending machines across multiple states, but I only want to see the ones that are in state A, would a an index function be the best fit for that.

The first image, would be the look up setting, the second would be the data list. Any help would be wonderful

 

[Larry Haydn]

Have you tried using a pivot table?

 

[vcoolio]

Hello VapoHendo,

If your data set is large, then perhaps the Advanced Filter will do the task for you:-

Sub Test()

    If WorksheetFunction.CountA(Sheet2.Range("A2:H2")) = 0 Then Exit Sub

    Sheet2.Range("A3", Range("H" & Rows.Count).End(xlUp)).ClearContents
    Sheet1.Range("A1", Sheet1.Range("H" & Sheet1.Rows.Count).End(xlUp)).AdvancedFilter 2, Sheet2.[A1:H2], [A3]
    
End Sub

I've attached a mock-up sample file to show you how it could work Here.

Sheet1 has a data base.
In Sheet2, you'll notice drop down selections in cells A2, B2, C2 and D2. Select whichever criteria you may need (either a single criteria or multiple criteria) and then click on the "GO" button. The rows of your selected data options will then be shown.
Play with the sample and let us know what you think.

I hope that this helps.

Cheerio,
vcoolio.

 

[vapohendo]

Hello VapoHendo,

If your data set is large, then perhaps the Advanced Filter will do the task for you:-

Cheerio,
vcoolio.

Hey, this is like I was thinking, but much simpler, so I can work with it. Thanks for your help, really appreciate it

 

[vcoolio]

You're welcome Vapohendo.
I'm glad to have been able to assist.

Cheerio,
vcoolio.