• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula query

B

brianaclark1967

Member
Morning guys,

I've attached a sheet for reference. The query is for cell J32

I want a formula that will copy the contents of cell A32, BUT if cell D32 has the words "Standard Handle/s" in it; I want cell J32 to be zero

Many thanks for reading this guys.

Regards

Brian
 

Attachments

In cell J32 you can write the formula as given below :

Code: 
=IF(ISERROR(SEARCH("Standard Handle/s",$D$32)),0,$A$32)
 
brianaclark1967 said:
Hi Jay,

Many thanks for your help. Awesome solution.

I have another one on the attached sheet that's got me stumped.

I have a formula that's working fine, but I wanted to expand on the formula if possible.

Many thanks

Brian
Click to expand...
they brain do u want to replace the existing formula in the cellR9 then??
 
jayalaxmi said:
hey brian check this out??
Click to expand...

Hi Jay,

I was hoping to add the IF(R9="Y",Z9,IF(R10="Y",Z10,"")) formula to this one =IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s"))

I'm just not sure how to join the two IF formulas

Many thanks

Brian
 
Ramesh said:
PFA, the attached Updated file,, where I have written the formula.
Click to expand...

Hi Ramesh,

I was hoping to add the IF(R9="Y",Z9,IF(R10="Y",Z10,"")) formula to this one =IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s"))

I'm just not sure how to join the two IF formulas

Many thanks

Brian
 
sorry for d late reply brian, really it took a much time for me to solve this...

here it is

=IF(AND(R9="Y",R10="Y"),Z9,IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s")))
 
brianaclark1967 said:
Hi Ramesh,

I was hoping to add the IF(R9="Y",Z9,IF(R10="Y",Z10,"")) formula to this one =IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s"))

I'm just not sure how to join the two IF formulas

Many thanks

Brian
Click to expand...
Dear Briana,
Please be clear what you want to have answer in specific column.



Thanks & Regards,
CMA Vishal Srivastava
 
jayalaxmi said:
sorry for d late reply brian, really it took a much time for me to solve this...

here it is

=IF(AND(R9="Y",R10="Y"),Z9,IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s")))
Click to expand...

Hi Jay,

Thank you for your reply.

It is working when R9 has a Y, it pulls Z9 through, but when R10 has a Y it still pulls through Z9 instead of Z10

Regards

Brian
 
Guess this is what you are looking for.
Code: 
=IF($R$9="Y",$Z$9,IF($R$10="Y",$Z$10,IF(ISNUMBER(MATCH($C$9,$AB$68:$AB$71,0)),$AC$68,IF(ISNUMBER(MATCH($C$9,$AB$72:$AB$77,0)),$AC$72,"Standard Handle/s"))))
Thanks
 
Hi Briana,

Try this in cell "O10":

=IFNA(LOOKUP(2,1/SEARCH("Y",R9:R10),Z9:Z10),IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s")))

Hope your problem have solved now.

Thanks & Regards,
CMA Vishal Srivastava
 
brianaclark1967 said:
Hi Jay,

Thank you for your reply.

It is working when R9 has a Y, it pulls Z9 through, but when R10 has a Y it still pulls through Z9 instead of Z10

Regards

Brian
Click to expand...
=IF(R9="Y",Z9,IF(R10="Y",Z10,IF(ISNUMBER(MATCH(C9,AB68:AB71,0)),AC68,IF(ISNUMBER(MATCH(C9,AB72:AB77,0)),AC72,"Standard Handle/s"))))
 
You must log in or register to reply here.
Back
Top