Excel VLookup Question 1

[ccepaulb]

I hope I can explain myself OK here.

I want to create a vlookup function that will return to me all outlets that fall into a particular chain and then have the corresponding volume and sales data in the columns to the right.
For example, let's say I'm working with 3 retailers (Retailer A,B and C). Retailer A has 40 outlets (or individual stores) B has 300 and c has 100.
I have set-up a list box with the retailers listed, this is linked to give me either a 1,2 or 3 in a particular cell. If they pick retailer A, then a 1 would appear, if it is B a 2 would appear and so on.
The vlookup is linked to the linked list box cell.
Now where I'm stuck.
I'm trying to get it to list all the individual outlets based on the retailer selected. In column (A) I have a 1 for all 40 Retailer A outlets a 2 for all 300 retailer B outlets and a 3 for all 100 retailer C outlets.
How can I get the individual outlets to list based upon the retailer they select??

Thanks, Paul

 

[ccepaulb]

Could I maybe use the MS Query function and set the criteria for Retailer to link to an Excel Cell?
I tried to set the criteria to a cell or control (like I would in Access) but I can't get it to work.
Not sure which route to take?

Thanks, Paul

 

[cheerio]

Take a look ar Data, Filter, Advanced Filter

You can use this to filter on just one company

 

[PaulRD]

Have you checked out Pivot Tables? While a bit complex at first, well worth the investment to learn. They can be a very powerful way to view data in tables or charts.

Paul D
pimpin' aint easy

 

[Gavona]

I agree with Paul. Pivot tables will probably do this task most easily. But selecting to display (say) 3 of 50 Retailers to display is a bit complex. (Getting a worksheet generated for each retailer in the database though is work of a matter of seconds, though you may well spend a while formatting them to look pretty. - sometimes a macro is worthwhile for this.)

Filters will do it too if your database has only one line per outlet but pivots will summarise for each outlet if you have multiple lines per outlet, or if you want a summary for each retailer.

I have in the past used advanced filters with events so that the user just entered a partial string in one or two fields and the advanced filter auto adjusted the criteria range and applied the filter automatically...
You could equally use Datavalidation to specify a drop down list to populate the advanced filter criteria range....



Thanks,

Gavin

 

[ccepaulb]

I can try pivot tables, but in the past I struggled with them because of how it shows data. Let's say all I want to do is show a value from my data, PTables limit the way I can see them to Sum..count...avg...ect. Is there an option that will just pull in that particular value without summing, averaging..etc?

Thanks, Paul

 

[xlbo]

Pivottable definitely the way to go

Set Retailer as PAGE field - this can be used as a filter

Outlets go in as ROW fields - they will display vertically

All numbers go in the VALUE field - use SUM in general - if a SUM is used but there is only 1 record then the sum IS the record...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Paul,
I agree, you can hit limitations with pivot tables but they are a powerful tool and well worth gettingto grips with. There seem to be limits (depending on your memory and the size and complexity of your database) as to how many row and column fields you can have...and when you try to work around these you can get a 'too many unique values'error.

To show "just a particular value" you need to either:
(i)include it as a row field or
(ii) you need to include something in the row field that makes sure that a row is produced for each record so there is no summing ar averaging. This could be a record number for example.

Assuming that (ii) is not practical or you want to show text data for each record then you have to go with option (i). One way I have used in the past to reduce the number of row fields is to concatenate fields in the database. So I might in the database add a new calculated column with a formula =left(lastname+", "+firstname+" ",30)&" "&Text(salary,"$0.00"). Shown with a non-proportional font such as courier the salary figures all appear below one another, irrespective of the length of the name.



Thanks,

Gavin

 

[ccepaulb]

Thanks guys I appreciate your help, probably a good way to go but I was hoping to find a way to do this without using Pivots. I know there has got to be a way to list out the relevant rows based on a selection form a drop down control without using filters or Pivot tables

Thank you, Paul

 

[Gavona]

So why not have the first column of your database being the retailer. Highlight the data and heading of that column only. Data, filter, autofilter.
You then have a drop down list of retailers. Choose the one you want and all the rows relating to it will be displayed.
Format the Title row as top aligned and make sure row height is generous. That way the pivot control doesn't obscure things...

Thanks,

Gavin

 

[ccepaulb]

Because I have a front end that I need to work with that is pretty limited in size and how much I can manipulate.
Also, the filter function is too raw for the application I am making, need something with a little more Wow! to it.

Thanks, Paul

 

[Gavona]

Use your drop down to populate an advanced filter criteria range.
Using named ranges the output range can be on a different sheet to the database. It can also include only selected columns.
Or if your data is external then use MS Query as fotr your other post.

Thanks,

Gavin

 

[ccepaulb]

That is how I will do it, thanks for the idea.
I would like to also play around with the MS Query idea, anyone know how to set an Excel cell as criteria?

Thanks, Paul

 

[ccepaulb]

OK it's me again. Sorry to be such a pain but I can't get the advanced filter option to work, Excel keeps blowing up on me?
I used Advanced Filter, set my data and then set my criteria based on the drop down selection, it appears to run right, but then shuts down???

Also, how would I (assuming I figure out my problem) automate the process, so that every time the drop down changed the advanced filter would re-run?

Thank you, Paul

 

[PaulRD]

Have you tried Data, List

Paul D
pimpin' aint easy

 

[ccepaulb]

Huh??

 

[xlbo]

ccepaulb - the easy answer is with code but you really are making a rod for your own back by refusing to utilise pivottables.

Personally, I would prefer to get something that works than anything with a "Wow" factor

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Blowing up? I suggest trying the technique on a clean workbook - without the complications of your real data.

This is the (generic) code I use to apply advanced filters. It uses named ranges "criteria", "output" and "alldata". So step 1 get this working on a sample workbook.

Sub ApplyFilter()
'
' This macro looks for the named range 'Criteria' _
    adjusts that name to the current region (bounded by blank cells) - _
    this is so that the criteria can be modified without needing to worry _
    about re-defining the range _
    Finally an advanced filter to a new location is run _
        using tthe named range "alldata" as the source _
        the named range criteria as the criteria _
        the named range output as the place to filter to _
            "output" should be the list of the column headings you want to extract
            
'Usually you will run this macro from the sheet containing the "output" and "criteria" ranges _
this avoids abiguity.  There should be only one range called Alldata in the workbook.
   
   Range("Criteria").CurrentRegion.Name = "Criteria"
    Range("alldata").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Range("Criteria"), _
        CopyToRange:=Range("output"), _
        Unique:=False

End Sub

If the code works in your clean test workbook then it should in your live workbook, so long as you adopt the same rangenames.

As I said in my first response you can automate this using events. Something like this but I am no expert and it doesn't seem to work at present (excel97) but I thought that this was the working version of the code I use at work (excel2000 or later).

Sub Worksheet_Change(ByVal Target As Range)
'Application.ScreenUpdating = False
ActiveSheet.Unprotect
Application.enableevents = False

'Redefine criteria named range
Range("Criteria").CurrentRegion.Name = "Criteria"

 Dim rTest As Range
  Set rTest = Application.Intersect(Target, Range("criteria"))
  If Not rTest Is Nothing Then                      ' criteria has changed
  Call ApplyFilter
  
End If
Application.enableevents = True
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub

Thanks,

Gavin

 

[PaulRD]

in excel 2003, if you select the data pull down menu, it has a fly-out option for list, then create list. this gives the ability to quickly filter a list based on a selected criteria

Paul D
pimpin' aint easy

 

[Gavona]

I look foward to exploring that when I get excel 2003 then....!

ccepaulb, in your application you would want to delete the
Range("Criteria").CurrentRegion.Name = "Criteria"
lines from the code I posted.


Thanks,

Gavin