Advanced IF statement / LOOKUP
 

Hello,
I hope someone will be able to help me,

How do I create a formula that will check a certain cell for text
=IF(B25="SAT"

and then, If that is True search another worksheet for any values less than 1
=IF(B25="SAT",IF('Sheet2'!F5:F300<1,"NAME",""))

Then, depening on whether it finds anything less than one I REALLY want it
to look at a different column in Sheet2 and produce the NAME for any that are
less than 1 in the previous result.

If anyone could at least tell me if it is possible to get excel to display
the text from a different column that would be great help!

Thanks
Jane

Advanced IF statement / LOOKUP
 

=IF((Sheet1!B25="SAT")*(COUNTIF(Sheet2!F5:F300,"<1 ")0),"name","other")

ctrl+shift+enter (not just enter)


"Jane" wrote:

Hello,
I hope someone will be able to help me,

How do I create a formula that will check a certain cell for text
=IF(B25="SAT"

and then, If that is True search another worksheet for any values less than 1
=IF(B25="SAT",IF('Sheet2'!F5:F300<1,"NAME",""))

Then, depening on whether it finds anything less than one I REALLY want it
to look at a different column in Sheet2 and produce the NAME for any that are
less than 1 in the previous result.

If anyone could at least tell me if it is possible to get excel to display
the text from a different column that would be great help!

Thanks
Jane

Advanced IF statement / LOOKUP
 

Less than 1 is zero, so how can it tell the name of anything that doesn't
exist?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Jane" wrote in message
...
Hello,
I hope someone will be able to help me,

How do I create a formula that will check a certain cell for text
=IF(B25="SAT"

and then, If that is True search another worksheet for any values less
than 1
=IF(B25="SAT",IF('Sheet2'!F5:F300<1,"NAME",""))

Then, depening on whether it finds anything less than one I REALLY want it
to look at a different column in Sheet2 and produce the NAME for any that
are
less than 1 in the previous result.

If anyone could at least tell me if it is possible to get excel to display
the text from a different column that would be great help!

Thanks
Jane

Advanced IF statement / LOOKUP
 

That's brilliant, except is there a way for it to take the name from another
cell?

I would like ALL the names of anything with less than 1 to automatically
display when I select SAT from the list?



"Teethless mama" wrote:

=IF((Sheet1!B25="SAT")*(COUNTIF(Sheet2!F5:F300,"<1 ")0),"name","other")

ctrl+shift+enter (not just enter)


"Jane" wrote:

Hello,
I hope someone will be able to help me,

How do I create a formula that will check a certain cell for text
=IF(B25="SAT"

and then, If that is True search another worksheet for any values less than 1
=IF(B25="SAT",IF('Sheet2'!F5:F300<1,"NAME",""))

Then, depening on whether it finds anything less than one I REALLY want it
to look at a different column in Sheet2 and produce the NAME for any that are
less than 1 in the previous result.

If anyone could at least tell me if it is possible to get excel to display
the text from a different column that would be great help!

Thanks
Jane

Advanced IF statement / LOOKUP
 

Hi Bob,
Thanks for your reply.
There are some rows with "0".
E.g

Column A may have the name FRED and column G (entitled SAT) may have a 0 for
Fred. If this is the case I would like to be able to sleect SAT from my list
on another sheet and the name Fred appear so that I know that Fred has less
than one?



"Bob Phillips" wrote:

Less than 1 is zero, so how can it tell the name of anything that doesn't
exist?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Jane" wrote in message
...
Hello,
I hope someone will be able to help me,

How do I create a formula that will check a certain cell for text
=IF(B25="SAT"

and then, If that is True search another worksheet for any values less
than 1
=IF(B25="SAT",IF('Sheet2'!F5:F300<1,"NAME",""))

Then, depening on whether it finds anything less than one I REALLY want it
to look at a different column in Sheet2 and produce the NAME for any that
are
less than 1 in the previous result.

If anyone could at least tell me if it is possible to get excel to display
the text from a different column that would be great help!

Thanks
Jane