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!