hello all,

I have a workbook which I use to keep track of our sales for the month
/ year. I have tried to automate various pieces on each worksheet
(Jan - Dec) to eliminate manual data entry. I wrote the following to
display which territoy (North or South) the sale was made in based on
the salesman's initials:

----- TAKEN FROM CELL 10
=IF(OR(C10="AM",C10="BP",C10="JS",C10="SW",C10="WB "),"No.",
IF(OR(C10="JD",C10="KO",C10="PD",C10="SP"),"So."," "))
-----

however, I need to manually update this on each worksheet everytime a
salesman is hired, leaves, and/or moves to a new territory. I want to
develope a formula or macro that will do the following:

-----
If ('Corprate Sales Dec 2004'!C10) = Salesman's Initals ('Settings
Worksheet'!C3:C12) and their territory ('Settings Worksheet'!D3:D12) =
"NORTH" display "No." in cell B10. Below is my failed attempt:

=IF(AND(C10='Settings Worksheet'!C3:C12,'Settings
Worksheet'!D3:D12="NORTH"),"No."," ")

but all this does is give me an "#VALUE" error. I also tried

=IF(OR(C3='Settings Worksheet'!C4,AND('Settings
Worksheet'!D3="NORTH")),"No."," ")

but when added all the comparison just for the "NORTH Territory"

=IF(OR(C3='Settings Worksheet'!C3,'Settings Worksheet'!C4,'Settings
Worksheet'!C5,'Settings Worksheet'!C6,'Settings
Worksheet'!C7,'Settings Worksheet'!C8,'Settings
Worksheet'!C9,'Settings Worksheet'!C10),AND('Settings
Worksheet'!D3="NORTH",'Settings Worksheet'!D4="NORTH",'Settings
Worksheet'!D5="NORTH",'Settings Worksheet'!D6="NORTH",'Settings
Worksheet'!D7="NORTH",'Settings Worksheet'!D8="NORTH",'Settings
Worksheet'!D9="NORTH",'Settings Worksheet'!D10="NORTH"),"No."," ")
-----

it does not work and I cannot figure out how or why. Any / all
assistance will be greatly appreciated.

Thank you in advance.

You refer to cells but to what properties to you want to compare the value
of?

try this: If 'Settings Worksheet'!D4.value="NORTH"
or
If ('Settings Worksheet'!D4.value="NORTH") = True AND ('Settings
Worksheet'!D6.value ="NORTH") = True then ...
' If 1 AND 1 Then True
' else 1 AND 0, 0 AND 1, 0 AND 0, all these = 0 = False

--

Hope that helps,

Phil

------------------------------------------

IT Analyst
SE1, London, UK,

http://uk.geocities.com/philippeoget
philippeoget at yahoo dot com

Programming Excel: <a
href="http://uk.geocities.com/philippeoget/xl/InternetLinkOrganiser.zip"
target="_blank"The Excel A2Z Project: </a
http://uk.geocities.com/philippeoget/a2z/

"WebWizard97" wrote in message
...
hello all,

I have a workbook which I use to keep track of our sales for the month
/ year. I have tried to automate various pieces on each worksheet
(Jan - Dec) to eliminate manual data entry. I wrote the following to
display which territoy (North or South) the sale was made in based on
the salesman's initials:

----- TAKEN FROM CELL 10
=IF(OR(C10="AM",C10="BP",C10="JS",C10="SW",C10="WB "),"No.",
IF(OR(C10="JD",C10="KO",C10="PD",C10="SP"),"So."," "))
-----

however, I need to manually update this on each worksheet everytime a
salesman is hired, leaves, and/or moves to a new territory. I want to
develope a formula or macro that will do the following:

-----
If ('Corprate Sales Dec 2004'!C10) = Salesman's Initals ('Settings
Worksheet'!C3:C12) and their territory ('Settings Worksheet'!D3:D12) =
"NORTH" display "No." in cell B10. Below is my failed attempt:

=IF(AND(C10='Settings Worksheet'!C3:C12,'Settings
Worksheet'!D3:D12="NORTH"),"No."," ")

but all this does is give me an "#VALUE" error. I also tried

=IF(OR(C3='Settings Worksheet'!C4,AND('Settings
Worksheet'!D3="NORTH")),"No."," ")

but when added all the comparison just for the "NORTH Territory"

=IF(OR(C3='Settings Worksheet'!C3,'Settings Worksheet'!C4,'Settings
Worksheet'!C5,'Settings Worksheet'!C6,'Settings
Worksheet'!C7,'Settings Worksheet'!C8,'Settings
Worksheet'!C9,'Settings Worksheet'!C10),AND('Settings
Worksheet'!D3="NORTH",'Settings Worksheet'!D4="NORTH",'Settings
Worksheet'!D5="NORTH",'Settings Worksheet'!D6="NORTH",'Settings
Worksheet'!D7="NORTH",'Settings Worksheet'!D8="NORTH",'Settings
Worksheet'!D9="NORTH",'Settings Worksheet'!D10="NORTH"),"No."," ")
-----

it does not work and I cannot figure out how or why. Any / all
assistance will be greatly appreciated.

Thank you in advance.