lookup on a subtotal

[Christa]

On Sheet 1, I have a listing of stores. I have an auto filter on there so I
can filter by Account name and get back a listing of stores. I'm using the
the subtotal to get back a count of all the stores listed. On Sheet 2, I
have all the Account names and their total number of stores. I'd like to get
a % of stores retrieved using the subtotal. So if on Sheet 1, I filter on
ABC Stores and get back 6 and on Sheet 2 is shows that ABC Stores has 10
stores, how can I get the % (6/10 or 66%)? I'm assuming I can just do a
vlookup based upon the value of the Account name, but how do I tell the
formula to use the Account name that I filtered the table on?

 

[vezerid]

You are correct, VLOOKUP is what you need: in Sheet 1, say it is cell
F6 that shows the 6 stores and that A6 contains the code 'ABC Stores'.
Then the following will give you the percentage:

=F6/VLOOKUP(A6,'Sheet 2'!A:B,2,0)

Here I assume that acct names and total number of stores are in
columns A:B of 'Sheet 2'.

HTH
Kostis Vezerides

 

[Christa]

yeah - but I'm not always looking at A6. ABC Stores might be in A6, but if I
change my Auto Filter to pull XYZ Mart instead, that may be A55. How can I
always tell the formula to look at whatever the results are from my Auto
Filter?