IF Function (Newbie needing help)

[Chris Olson]

Hello Group!

I've been puzzling over something I want my spreadsheets to do for a long
time now, and I thought perhaps I might find help here.

Here is what I am doing and trying to do.

I have a spreadsheet tracking Commissions for paid orders and another
tracking Shipping charges for shipped orders.

On each entry, on each spreadsheet there is a unique Order Number
identifying that credit or charge.

What I want to do is to have a function on the Commissions sheet that would
tell if the order that has had the commission paid, and that is listed on
that sheet, has been listed as Shipped on any of my multiple shipping
sheets.

I figure that it could go something like this, but I'm not sure how to
implement: there would be a Field with a header of "Shipped?" and underneath
it would be "Yes" or "No" on each entry. Each individual "Yes" or "No" would
be determined by checking a particular range of cells on all the shipping
sheets _looking for the unique order number_ which corresponds with the
commissions credit.

For example: order number 1337 has been listed as paid on the commissions
sheet. Within the same row of this entry would be a cell that says "Yes" or
"No", whose column header is "Shipped?". The cell which says "yes" or "no"
would be determined by checking a particular column on each of the Shipping
sheets to see IF the number 1337 is present (which it would be if the order
had been shipped).

Any ideas, or am I hard to follow?

Thanks for any help, I really appreciate it.

 

[Don Guillett]

Have a look in HELP index for lookup. Probably, a vlookup of
=vlookup(partnumber,othersheet!range,2,false) would do it

 

[Melissa]

If I understand correctly, you're checking for the 1337
and if it's present you want "yes", if not, then "no"?

=IF(b2=1337,"Yes","No")

 

[Chris Olson]

Yes, that is what I'm looking for: the 1337.
However, I am not looking for it's presence in a specific cell, but rather
in a range of cells. If it is found within that range of cells anywhere,
then I would want it to return the "yes".

Maybe something like:

=IF(b1:b45[has 1337?],"yes,"no")

I just don't know how to instruct it to check for a specific number within a
range.

 

[alcorjr]

The match function should help you.
Let's say that b1 holds the value you want to look for, and c1:c500 i
the range you want to analyze. In the cell where wou want to write th
result of the search write:
=IF(ISERROR(MATCH(b1,C1:C500,0)),"NO","YES")

Hope that helps:

 

[Dave R.]

Quick tip, your original message was long to read, you might just ask the
simpler version you just asked --

use countif
IF(COUNTIF(B$1:B$45,A1)=1,"Yes","No")

countif will check the range b1:b45 to see if the value of A1 (1337 for
example) is contained within that range. If it is, it says "Yes".. if it's
not, it says "No". Notice that the range b1:b45 uses $ to keep them from
sliding down as you copy the formula down.

Yes, that is what I'm looking for: the 1337.
However, I am not looking for it's presence in a specific cell, but rather
in a range of cells. If it is found within that range of cells anywhere,
then I would want it to return the "yes".

Maybe something like:

=IF(b1:b45[has 1337?],"yes,"no")

I just don't know how to instruct it to check for a specific number within a
range.

If I understand correctly, you're checking for the 1337
and if it's present you want "yes", if not, then "no"?

=IF(b2=1337,"Yes","No")

 

[Chris Olson]

Thank you VERY much Dave, that looks like it will make sense!

Sorry for the long post at the beginning - I am just learning this sort of
thing, but I'm beginning to understand how to compose my questions better
now.

Quick tip, your original message was long to read, you might just ask the
simpler version you just asked --

use countif
IF(COUNTIF(B$1:B$45,A1)=1,"Yes","No")

countif will check the range b1:b45 to see if the value of A1 (1337 for
example) is contained within that range. If it is, it says "Yes".. if it's
not, it says "No". Notice that the range b1:b45 uses $ to keep them from
sliding down as you copy the formula down.

Yes, that is what I'm looking for: the 1337.
However, I am not looking for it's presence in a specific cell, but rather
in a range of cells. If it is found within that range of cells anywhere,
then I would want it to return the "yes".

Maybe something like:

=IF(b1:b45[has 1337?],"yes,"no")

I just don't know how to instruct it to check for a specific number

within

a
range.