Chance a Fomula... 2

[jatolo]

=IF($BL$64="","",IF(BL3=$BL$64,1,0))
I NEED TO CHANCE THE ABOVE FORMULA TO:
IF $BL$64="A" THEN BL3=1,0
IF $BL$64="B",THEN BL3=1,0
IF $BL$64="C",THEN BL3="C1",0
ONCE AGAIN JUST WHEN IT SHOWS "C" THE RESULT SHOULD BE C1 or 0
THANKS FOR YOUR HELP!!!

Thanks....
Jatolo

 

[Scott24x7]

I think this will do what you need:

=IF(OR($BL$64="A",$BL$64="B",$BL$64="C"),1,0)

Or you can tweak it a bit if you have some other value.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Ooops... missed one of your criteria. This will do the trick:

=IF($BL$64="","",IF(OR($BL$64="A",$BL$64="B",$BL$64="C"),1,0))

Cheers.

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Ha! Didn't notice that last one with the C1... sorry for jumping the gun, but THIS one really works:

=IF($BL$64="","",IF($BL$64="C","C1",IF(OR($BL$64="A",$BL$64="B"),1,0)))

Cheers

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[jatolo]

SORRY SORRY SORRY!!!
i MADE A BIG MISTAKE...

=IF($BL$64="","",IF(BL3=$BL$64,1,0))
I NEED TO CHANGE THE ABOVE FORMULA TO:
IF $BL$64="A" AND BL3="A",1,0
IF $BL$64="B",AND BL3="B",1,0
IF $BL$64="C",and BL3="C","C1",0
ONCE AGAIN JUST WHEN IT SHOWS "C" THE RESULT SHOULD BE C1 or 0
THANKS FOR YOUR HELP!!!

Thanks....
Jatolo

 

[Scott24x7]

Just for clarity then, in the first part of your statement, does it really matter the A and B then if you're testing for BL3 = BL64? Is it correct that if BL3 = BL64, it should be 1, and if not it should be 0, unless it is matching C in both locations, and then it should be C1? Is that the criteria?

Is there any chance a value other than A, B, C, or "" will be in your target cell? I can simplify that if it the above is the case.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[jatolo]

You are right.... "Is it correct that if BL3 = BL64, it should be 1, and if not it should be 0, unless it is matching C in both locations, and then it should be C1? Is that the criteria?"
..and the vaslues are...A, B, C.... NOT ""..
Thanks again

Thanks....
Jatolo

 

[Scott24x7]

Jatolo,
Use this:

=IF(AND($BL$64="C",BL3="C"),"C1",IF(BL3=$BL$64,1,0))

It will solve all your criteria then.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[jatolo]

Works ... but WHEN $BL$64="C"and BL3="C" it keeps showing 1 instead "C"...
thanks...

Thanks....
Jatolo

 

[Scott24x7]

Have you entered the formula correctly?
I just checked this twice by pasting back into Excel file, and it worked under these conditions:

BL3 = C BL64 = C Target Cell = C1
BL3 = A BL64 = A Target Cell = 1
BL3 = A BL64 = C Target Cell = 0
BL3 = R BL64 = B Target Cell = 0

Can you check your locations and values?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[jatolo]

Thanks themaniac,, the formula WORKS great!!!
Just one more question....
If I want to copy this formula to BL4, BL5, BL6 and so on...
what should I do.... I tried but it keeps BL3 all the time.....
Thanks..

Thanks....
Jatolo

 

[Scott24x7]

Jatolo,
Instead of coping the formula from here and pasting into your sheet, select the Cell BL3. You will notice a small square on the bottom right of the cell. Click that square, hold the left mouse button down, and just drag it down the the last row you want (Maybe BL200 for instance). It will populate, and since it doesn't have the $BL$# in front of it, it will increment automatically.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[jatolo]

TheManiac
Thank you for your help....AGAIN YOU GOT IT RIGHT!!!

Thanks....
Jatolo