software

I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

As a life long salesman (an excel playboy), I am curious as to how you
define quot;weaselsquot;

--
Don Guillett
SalesAid Software

quot;Omakbobquot; gt; wrote in message
...
gt;I have a very large spreadsheet with multiple sales weasels on it that
gt; contains their gross commissions for the month. The columns a Client;
gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
gt; for
gt; each month that commissions are received for that client.
gt;
gt; On another spreadsheet within the same workbook I want to just pull those
gt; commissions received from specific clients, so that:
gt;
gt; Bob's Auto Garage is in column A, the vlookup will also give me the amount
gt; from column F.
gt;
gt; The formula that I have that isn't working is:
gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt;
gt; The result is a #VALUE! error.
gt;
gt; Would someone please tell me where I'm going wrong?
gt;
gt; Thanks
Try it this way:

=IF(ISNA(VLOOKUP(quot;Bob's Auto Garagequot;,'COM 06'!$A:$F,6,FALSE)),quot;Not
Foundquot;,VLOOKUP(quot;Bob's Auto Garagequot;,'COM 06'!$A:$F,6,FALSE))

The first parameter is what you're looking for, Bob's Auto Garage

The second parameter is where to look for it ... an array of data. VLOOKUP
always looks down the first column, in this case, column A on sheet COM 06.

The third parameter is the column to go to for the return value, relative to
the first column; in this case, column 6 = column F ... that's if it finds
anything.

The fourth and last parameter is set to FALSE so that VLOOKUP will return an
exact match or nothing. If Bob's Auto Garage is not found, you'll get back
an #N/A! error.

Lastly, wrapping the VLOOKUP in the IF(ISNA(...) construct allows you to
control what you get back if it doesn't find what you're looking for.

You wouldn't normally use a fixed Lookup value so you might expect the
formula to look something like:

=IF(ISNA(VLOOKUP(A2,'COM 06'!$A:$F,6,FALSE)), quot;Not Foundquot;, VLOOKUP(A2,'COM
06'!$A:$F,6,FALSE))

Where A2 contains Bob's Auto Garage

Regards

Trevorquot;Omakbobquot; gt; wrote in message
...
gt;I have a very large spreadsheet with multiple sales weasels on it that
gt; contains their gross commissions for the month. The columns a Client;
gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
gt; for
gt; each month that commissions are received for that client.
gt;
gt; On another spreadsheet within the same workbook I want to just pull those
gt; commissions received from specific clients, so that:
gt;
gt; Bob's Auto Garage is in column A, the vlookup will also give me the amount
gt; from column F.
gt;
gt; The formula that I have that isn't working is:
gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt;
gt; The result is a #VALUE! error.
gt;
gt; Would someone please tell me where I'm going wrong?
gt;
gt; Thanks
D'oh! busted
My definition of a 'sales weasel' is a salesperson who promises more than
the product is/was meant to deliver in order to make the sale. (Be gentle,
I'm an accounting troll)

quot;Don Guillettquot; wrote:

gt; As a life long salesman (an excel playboy), I am curious as to how you
gt; define quot;weaselsquot;
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Omakbobquot; gt; wrote in message
gt; ...
gt; gt;I have a very large spreadsheet with multiple sales weasels on it that
gt; gt; contains their gross commissions for the month. The columns a Client;
gt; gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
gt; gt; for
gt; gt; each month that commissions are received for that client.
gt; gt;
gt; gt; On another spreadsheet within the same workbook I want to just pull those
gt; gt; commissions received from specific clients, so that:
gt; gt;
gt; gt; Bob's Auto Garage is in column A, the vlookup will also give me the amount
gt; gt; from column F.
gt; gt;
gt; gt; The formula that I have that isn't working is:
gt; gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt; gt;
gt; gt; The result is a #VALUE! error.
gt; gt;
gt; gt; Would someone please tell me where I'm going wrong?
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

Oh, a pointy headed pencil pusher, huh.

--
Don Guillett
SalesAid Software

quot;Omakbobquot; gt; wrote in message
...
gt; D'oh! busted
gt; My definition of a 'sales weasel' is a salesperson who promises more than
gt; the product is/was meant to deliver in order to make the sale. (Be
gt; gentle,
gt; I'm an accounting troll)
gt;
gt; quot;Don Guillettquot; wrote:
gt;
gt;gt; As a life long salesman (an excel playboy), I am curious as to how you
gt;gt; define quot;weaselsquot;
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Omakbobquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a very large spreadsheet with multiple sales weasels on it that
gt;gt; gt; contains their gross commissions for the month. The columns a
gt;gt; gt; Client;
gt;gt; gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
gt;gt; gt; for
gt;gt; gt; each month that commissions are received for that client.
gt;gt; gt;
gt;gt; gt; On another spreadsheet within the same workbook I want to just pull
gt;gt; gt; those
gt;gt; gt; commissions received from specific clients, so that:
gt;gt; gt;
gt;gt; gt; Bob's Auto Garage is in column A, the vlookup will also give me the
gt;gt; gt; amount
gt;gt; gt; from column F.
gt;gt; gt;
gt;gt; gt; The formula that I have that isn't working is:
gt;gt; gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt;gt; gt;
gt;gt; gt; The result is a #VALUE! error.
gt;gt; gt;
gt;gt; gt; Would someone please tell me where I'm going wrong?
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt;
gt;gt;
gt;gt;
Thank you, Thank you, Thank you!

quot;Trevor Shuttleworthquot; wrote:

gt; Try it this way:
gt;
gt; =IF(ISNA(VLOOKUP(quot;Bob's Auto Garagequot;,'COM 06'!$A:$F,6,FALSE)),quot;Not
gt; Foundquot;,VLOOKUP(quot;Bob's Auto Garagequot;,'COM 06'!$A:$F,6,FALSE))
gt;
gt; The first parameter is what you're looking for, Bob's Auto Garage
gt;
gt; The second parameter is where to look for it ... an array of data. VLOOKUP
gt; always looks down the first column, in this case, column A on sheet COM 06.
gt;
gt; The third parameter is the column to go to for the return value, relative to
gt; the first column; in this case, column 6 = column F ... that's if it finds
gt; anything.
gt;
gt; The fourth and last parameter is set to FALSE so that VLOOKUP will return an
gt; exact match or nothing. If Bob's Auto Garage is not found, you'll get back
gt; an #N/A! error.
gt;
gt; Lastly, wrapping the VLOOKUP in the IF(ISNA(...) construct allows you to
gt; control what you get back if it doesn't find what you're looking for.
gt;
gt; You wouldn't normally use a fixed Lookup value so you might expect the
gt; formula to look something like:
gt;
gt; =IF(ISNA(VLOOKUP(A2,'COM 06'!$A:$F,6,FALSE)), quot;Not Foundquot;, VLOOKUP(A2,'COM
gt; 06'!$A:$F,6,FALSE))
gt;
gt; Where A2 contains Bob's Auto Garage
gt;
gt; Regards
gt;
gt; Trevor
gt;
gt;
gt; quot;Omakbobquot; gt; wrote in message
gt; ...
gt; gt;I have a very large spreadsheet with multiple sales weasels on it that
gt; gt; contains their gross commissions for the month. The columns a Client;
gt; gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
gt; gt; for
gt; gt; each month that commissions are received for that client.
gt; gt;
gt; gt; On another spreadsheet within the same workbook I want to just pull those
gt; gt; commissions received from specific clients, so that:
gt; gt;
gt; gt; Bob's Auto Garage is in column A, the vlookup will also give me the amount
gt; gt; from column F.
gt; gt;
gt; gt; The formula that I have that isn't working is:
gt; gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt; gt;
gt; gt; The result is a #VALUE! error.
gt; gt;
gt; gt; Would someone please tell me where I'm going wrong?
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

I think you misspelled bean counter. lt;vbggt;

Don Guillett wrote:
gt;
gt; Oh, a pointy headed pencil pusher, huh.
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Omakbobquot; gt; wrote in message
gt; ...
gt; gt; D'oh! busted
gt; gt; My definition of a 'sales weasel' is a salesperson who promises more than
gt; gt; the product is/was meant to deliver in order to make the sale. (Be
gt; gt; gentle,
gt; gt; I'm an accounting troll)
gt; gt;
gt; gt; quot;Don Guillettquot; wrote:
gt; gt;
gt; gt;gt; As a life long salesman (an excel playboy), I am curious as to how you
gt; gt;gt; define quot;weaselsquot;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Don Guillett
gt; gt;gt; SalesAid Software
gt; gt;gt;
gt; gt;gt; quot;Omakbobquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have a very large spreadsheet with multiple sales weasels on it that
gt; gt;gt; gt; contains their gross commissions for the month. The columns a
gt; gt;gt; gt; Client;
gt; gt;gt; gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
gt; gt;gt; gt; for
gt; gt;gt; gt; each month that commissions are received for that client.
gt; gt;gt; gt;
gt; gt;gt; gt; On another spreadsheet within the same workbook I want to just pull
gt; gt;gt; gt; those
gt; gt;gt; gt; commissions received from specific clients, so that:
gt; gt;gt; gt;
gt; gt;gt; gt; Bob's Auto Garage is in column A, the vlookup will also give me the
gt; gt;gt; gt; amount
gt; gt;gt; gt; from column F.
gt; gt;gt; gt;
gt; gt;gt; gt; The formula that I have that isn't working is:
gt; gt;gt; gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt; gt;gt; gt;
gt; gt;gt; gt; The result is a #VALUE! error.
gt; gt;gt; gt;
gt; gt;gt; gt; Would someone please tell me where I'm going wrong?
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;

--

Dave Peterson

So solly.

Actually, I would like to help but until he realizes that, without the
weasel he wouldn't have a bean to count. Maybe the thought was that there
are only accounting types here.

In my old insurance/stock brokerage firm we used to call the home office
management organization the quot;homosquot; lt;Ggt;

Heck, we gotta have some fun sometimes like Fridays on the L amp; G groups.

--
Don Guillett
SalesAid Software

quot;Dave Petersonquot; gt; wrote in message
...
gt;I think you misspelled bean counter. lt;vbggt;
gt;
gt; Don Guillett wrote:
gt;gt;
gt;gt; Oh, a pointy headed pencil pusher, huh.
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Omakbobquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; D'oh! busted
gt;gt; gt; My definition of a 'sales weasel' is a salesperson who promises more
gt;gt; gt; than
gt;gt; gt; the product is/was meant to deliver in order to make the sale. (Be
gt;gt; gt; gentle,
gt;gt; gt; I'm an accounting troll)
gt;gt; gt;
gt;gt; gt; quot;Don Guillettquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; As a life long salesman (an excel playboy), I am curious as to how you
gt;gt; gt;gt; define quot;weaselsquot;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Don Guillett
gt;gt; gt;gt; SalesAid Software
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Omakbobquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I have a very large spreadsheet with multiple sales weasels on it
gt;gt; gt;gt; gt;that
gt;gt; gt;gt; gt; contains their gross commissions for the month. The columns a
gt;gt; gt;gt; gt; Client;
gt;gt; gt;gt; gt; Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March
gt;gt; gt;gt; gt; (etc
gt;gt; gt;gt; gt; for
gt;gt; gt;gt; gt; each month that commissions are received for that client.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; On another spreadsheet within the same workbook I want to just pull
gt;gt; gt;gt; gt; those
gt;gt; gt;gt; gt; commissions received from specific clients, so that:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Bob's Auto Garage is in column A, the vlookup will also give me the
gt;gt; gt;gt; gt; amount
gt;gt; gt;gt; gt; from column F.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; The formula that I have that isn't working is:
gt;gt; gt;gt; gt; =VLOOKUP('Com 06'!A:A=quot;Bob's Auto Garagequot;,'Com 06'!F:F)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; The result is a #VALUE! error.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Would someone please tell me where I'm going wrong?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thanks
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;
gt; --
gt;
gt; Dave Peterson