Biff,

I just saw your post on this entry now. Thanks for taking the time to help
me.

The data comes from a system generated report that lists all of the sales
for each rep. I downloaded the sales data into excel and now I want to sum up
a reps sales of a particular product in all of his/her states. The problem is
that there are also some entries for certain reps in states that don't belong
to them (which is an indication that the entry is faulty). So, I want to sum
up a reps sales only if those sales were in a state that belonged to them. If
I can create a column that puts a marker in every row that has a state that
belongs to the rep then I could do a sumif function in another column to get
a total of only the sales that are not faulty. The part that I'm having a lot
of trouble with is how to make a marker that identifies whether the state
belongs to the rep or not.

Again, thanks so much for helping me.

Michael

"T. Valko" wrote:

Is this data manually entered?

If so, you can still use the suggestion I made at your other post *except*
don't use the in-cell drop down option in data validation.

If someone tries to enter NV for RepNum 20 it'll be rejected.

P.S.

This post contains more info than your other post so I'm not going to follow
the other post anymore.

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

Could someone please give me some suggestions for how to do the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple times)
on
each row of the first column and then lists all of the States in the
second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
...

Any ideas for how to use excel to do what I am trying would be hugely
appreciated.

Thanks,
Michael