Hi folks... I hope this will be simple and thank you in advance for your help.

Each month I get a 1400 row Excel sheet with each of our retail store
locations on it (1 row per store). Its got a lot of columns in it, but the
one Im concerned with is the number of sales calls received that month.

Each store belongs to a chain, and each chain has a budget for sales calls.
What we have to do is pick out is locations that received less than their
budgeted sales calls. So, for example, a store that should have gotten 6
calls and only got 4. Usually this is done by hand and with 1400 lines, you
can imagine, is very time consuming even with filters.

I want all under-budget stores to automatically get plopped onto their own
sheet, section, or tab, preferably by chain. My trouble is that each of the
chains has a different budget.

So, in a nutshell€¦

Account Gs budget is 8 calls. For all Account Gs locations, if calls < 8,
then have that locations row show up over here (or otherwise get separated
out). Repeat for the other four accounts with their budgets.

A scaled down version of the sheet:

Chain StoreNumber SalesCalls
Petstore A 1352 6
Petstore A 1022 9
Petstore B 152 5
Petstore C 2077 10

A's budget is 8 calls, B's budget is 7, C's budget is 6. I'd want something
that would pull out the first and third store in this mini-example.

Hopefully that's clear. Thanks again for your help!

Do you have the budgeted sales calls in a column?

Here's an imperfect way to do it with an IF statement:

Try =IF(C2D2,"OVER BUDGET",IF(C2=D2,"ON BUDGET","UNDER BUDGET")) in cell E2
and fill down.

expected results

column A: Column B: Column C: Column D:
Column E:
Chain StoreNumber SalesCalls BudgetedCalls
Budget?
Petstore A 1352 6 8
UNDER
Petstore A 1022 9 8
OVER
Petstore B 152 5 7
UNDER
Petstore C 2077 10 6
OVER

You could then just filter for UNDER BUDGET.

I think the easiest way to get the cells on another sheet would be to record
a macro of copying and pasting the filtered cells.

I hope this is the kind of thing you were after.


"BeckyRode" wrote:

Hi folks... I hope this will be simple and thank you in advance for your help.

Each month I get a 1400 row Excel sheet with each of our retail store
locations on it (1 row per store). Its got a lot of columns in it, but the
one Im concerned with is the number of sales calls received that month.

Each store belongs to a chain, and each chain has a budget for sales calls.
What we have to do is pick out is locations that received less than their
budgeted sales calls. So, for example, a store that should have gotten 6
calls and only got 4. Usually this is done by hand and with 1400 lines, you
can imagine, is very time consuming even with filters.

I want all under-budget stores to automatically get plopped onto their own
sheet, section, or tab, preferably by chain. My trouble is that each of the
chains has a different budget.

So, in a nutshell€¦

Account Gs budget is 8 calls. For all Account Gs locations, if calls < 8,
then have that locations row show up over here (or otherwise get separated
out). Repeat for the other four accounts with their budgets.

A scaled down version of the sheet:

Chain StoreNumber SalesCalls
Petstore A 1352 6
Petstore A 1022 9
Petstore B 152 5
Petstore C 2077 10

A's budget is 8 calls, B's budget is 7, C's budget is 6. I'd want something
that would pull out the first and third store in this mini-example.

Hopefully that's clear. Thanks again for your help!

Unfortunately the budgeted sales calls aren't in a column on this sheet. Is
there a way to plug in something to your suggestion to make it work?

Perhaps something like "if Chain=PetstoreA and SalesCalls< 8, "Under budget"
for each chain, with the appropriate salescalls number?

Thanks for the quick response the first time, and thank you for your help!

"Josh Craig" wrote:

Do you have the budgeted sales calls in a column?

Here's an imperfect way to do it with an IF statement:

Try =IF(C2D2,"OVER BUDGET",IF(C2=D2,"ON BUDGET","UNDER BUDGET")) in cell E2
and fill down.

expected results

column A: Column B: Column C: Column D:
Column E:
Chain StoreNumber SalesCalls BudgetedCalls
Budget?
Petstore A 1352 6 8
UNDER
Petstore A 1022 9 8
OVER
Petstore B 152 5 7
UNDER
Petstore C 2077 10 6
OVER

You could then just filter for UNDER BUDGET.

I think the easiest way to get the cells on another sheet would be to record
a macro of copying and pasting the filtered cells.

I hope this is the kind of thing you were after.


"BeckyRode" wrote:

Hi folks... I hope this will be simple and thank you in advance for your help.

Each month I get a 1400 row Excel sheet with each of our retail store
locations on it (1 row per store). Its got a lot of columns in it, but the
one Im concerned with is the number of sales calls received that month.

Each store belongs to a chain, and each chain has a budget for sales calls.
What we have to do is pick out is locations that received less than their
budgeted sales calls. So, for example, a store that should have gotten 6
calls and only got 4. Usually this is done by hand and with 1400 lines, you
can imagine, is very time consuming even with filters.

I want all under-budget stores to automatically get plopped onto their own
sheet, section, or tab, preferably by chain. My trouble is that each of the
chains has a different budget.

So, in a nutshell€¦

Account Gs budget is 8 calls. For all Account Gs locations, if calls < 8,
then have that locations row show up over here (or otherwise get separated
out). Repeat for the other four accounts with their budgets.

A scaled down version of the sheet:

Chain StoreNumber SalesCalls
Petstore A 1352 6
Petstore A 1022 9
Petstore B 152 5
Petstore C 2077 10

A's budget is 8 calls, B's budget is 7, C's budget is 6. I'd want something
that would pull out the first and third store in this mini-example.

Hopefully that's clear. Thanks again for your help!

If there's only three chains i.e. petstore a, b and c then you could use this
formula in column D:

=IF(A2="PetstoreA",IF(C2<8,"UNDER BUDGET","ON OR OVER
BUDGET"),IF(A2="Petstoreb",IF(C2<7,"UNDER BUDGET","ON OR OVER BUDGET"),
IF(C2<6, "UNDER BUDGET","ON OR OVER BUDGET")))

If there's more this won't work because excel won't let you use any more IF
statements in the one function.

You'll have to either create a column and enter in the budgeted calls or
(probably better) create a table with chain name and budgeted calls and then
use vlookup.

This isn't a very complicated thing to do but let me know if the first
formula is sufficient before I launch into an explanation of VLOOKUP (or
maybe you can work it out yourself).

There are five chains to track, so it looks as if the VLOOKUP is the way to
go. If it's not too much trouble, any help with creating a table and using
the function to find the under-budget stores would be very, very much
appreciated.

Thanks again!!!

"Josh Craig" wrote:

If there's only three chains i.e. petstore a, b and c then you could use this
formula in column D:

=IF(A2="PetstoreA",IF(C2<8,"UNDER BUDGET","ON OR OVER
BUDGET"),IF(A2="Petstoreb",IF(C2<7,"UNDER BUDGET","ON OR OVER BUDGET"),
IF(C2<6, "UNDER BUDGET","ON OR OVER BUDGET")))

If there's more this won't work because excel won't let you use any more IF
statements in the one function.

You'll have to either create a column and enter in the budgeted calls or
(probably better) create a table with chain name and budgeted calls and then
use vlookup.

This isn't a very complicated thing to do but let me know if the first
formula is sufficient before I launch into an explanation of VLOOKUP (or
maybe you can work it out yourself).

Okay, first on a new sheet (Sheet2 in my example) create a table with the
chain names and budgets:

column a: column b:
petstorea 8
petstoreb 7
petstorec 6
petstored 5
petstoree 4

then use this formula in a column on Sheet1:
=IF(C2<VLOOKUP(A2,Sheet2!A$1:B$5,2), "UNDER BUDGET","OVER OR ON BUDGET")

Then fill down.

The VLOOKUP command tells excel to find the value from A2 in the table on
Sheet2 and return the value from column 2 on Sheet2. Then it's just a matter
of comparing this value to actual phone calls (C2 in my example)..

Hope that all works. Please click 'yes' below if I've answered your question.

oops! one modification- the formula should be:

=IF(C2<VLOOKUP(A2,Sheet2!A$1:B$5,2,FALSE), "UNDER BUDGET","OVER OR ON BUDGET")