displaying text based on "TRUE" result from data comparison
 

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.

displaying text based on "TRUE" result from data comparison
 

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.