Hello fellow Excel Users,

I am new to VBA in Excel and need some assistance.

Background: I have a workbook "Corporate Sales" which contains (13)
worksheets, one for each month of the year (Corprate Sales Jan 2004 -
Corprate Sales Dec 2004), and one worksheet that I want to use as a
settings worksheet (Settings Worksheet). I have tried to automate each
worksheet to populate the sales territory based on the salesman's
initials using the following formula:

-----------------------------
column "B" = Sales Territory (contains the formula)
column "C" = Salesman's Initial

=IF(OR(C7="AM",C7="BP",C7="DA",C7="JS",C7="SW",C7= "WB"),"No.",IF(OR(C7="JD",C7="KO",C7="PD",C7="SP") ,"So.","
"))
-----------------------------

unfortunately, if a salesman is added or deleted I need to manually
modify each of the (12) worksheets. I want to develope a VBA "IF"
statement which will do the following:

-----------------------------
Compare salesman's inital columns:

"Settings Worksheet - C (salesman's initials)"

to

"Corprate Sales Jan 2004 - C (salesman's initials)"

and if

"Settings Worksheet - D (sales territory)" = North or South

populate

"Corprate Sales Jan 2004 - D (sales territory)" with No. or So.
-----------------------------

I have tried the following, however, being new to VBA I could not get
anything to work:

-----------------------------
Function GetSalesTerritory(strRep As String, varVal As Variant) As
Variant

If strRep = Worksheet("Settings Worksheet").Cell("C2") And
Worksheet("Settings Worksheet").Cell("D2").Value = "North" Then
Worksheet("Corprate Sales Jan 2004").Cell("B2").Value = "No."
End If

End Function
-----------------------------

I put "=GetSalesTerritory()" in "Corprate Sales Jan 2004 - C (salesmans
initals)" to call the function, but nothing seems to work.

Any and all assiatance would be greatly appreciated.

Also if anyone knows of a good VBA book for Beginners / Intermediate
that I could use as a reference would be helpful.

Create a separate table of Salespeople/Regions and use VLookup. Then,
something like the following (assuming your table is in a Named Range called
SalespeopleRegions with Initials in column 1 and Region in column 2) would
return the proper region:

=VLookup(C7,SalespeopleRegions,2)

When salespeople are added, you only need to add them to the table, no need
to touch the formula.

--
George Nicholson

Remove 'Junk' from return address.


wrote in message
oups.com...
Hello fellow Excel Users,

I am new to VBA in Excel and need some assistance.

Background: I have a workbook "Corporate Sales" which contains (13)
worksheets, one for each month of the year (Corprate Sales Jan 2004 -
Corprate Sales Dec 2004), and one worksheet that I want to use as a
settings worksheet (Settings Worksheet). I have tried to automate each
worksheet to populate the sales territory based on the salesman's
initials using the following formula:

-----------------------------
column "B" = Sales Territory (contains the formula)
column "C" = Salesman's Initial

=IF(OR(C7="AM",C7="BP",C7="DA",C7="JS",C7="SW",C7= "WB"),"No.",IF(OR(C7="JD",C7="KO",C7="PD",C7="SP") ,"So.","
"))
-----------------------------

unfortunately, if a salesman is added or deleted I need to manually
modify each of the (12) worksheets. I want to develope a VBA "IF"
statement which will do the following:

-----------------------------
Compare salesman's inital columns:

"Settings Worksheet - C (salesman's initials)"

to

"Corprate Sales Jan 2004 - C (salesman's initials)"

and if

"Settings Worksheet - D (sales territory)" = North or South

populate

"Corprate Sales Jan 2004 - D (sales territory)" with No. or So.
-----------------------------

I have tried the following, however, being new to VBA I could not get
anything to work:

-----------------------------
Function GetSalesTerritory(strRep As String, varVal As Variant) As
Variant

If strRep = Worksheet("Settings Worksheet").Cell("C2") And
Worksheet("Settings Worksheet").Cell("D2").Value = "North" Then
Worksheet("Corprate Sales Jan 2004").Cell("B2").Value = "No."
End If

End Function
-----------------------------

I put "=GetSalesTerritory()" in "Corprate Sales Jan 2004 - C (salesmans
initals)" to call the function, but nothing seems to work.

Any and all assiatance would be greatly appreciated.

Also if anyone knows of a good VBA book for Beginners / Intermediate
that I could use as a reference would be helpful.