Formula Query


Posted by Sam on January 19, 2002 1:45 AM

Just a query about adding a formula to a cell that changes the contents of another cell. For example:

If(A1="YES",[if true make B1 state Yes],[otherwise make B2 state No].
(with no formulas in B1 or B2)

I hope this is a clear question. I will try and rephrase just in case it doesnt make sense.

If I was using a standard 'If' statement but placing the result into 'another' cell without a reference in the 'other' cell.

Thansk in anticipation of your help.

Posted by Aladin Akyurek on January 19, 2002 1:52 AM

Formulas with...

built-in functions can't do that. Stated otherwise, it's not the way spreadsheets operate.

Aladin

========

Posted by Sam on January 19, 2002 2:10 AM

Re: Formulas with...

Thanks for your help mate.

There is another way I can get around it. I will try and describe the situation with a little graphic.


A.....B.....C
......50
......60
Y.....70
......80
............#

(Ignore the full stops, I wasnt sure if the graphic would appear correctly, so I added the full stops to help the appearance.)

I want c5 to equal b3 whenever a3="Y". If the Y was in a2 then c5 would equal 60. etc. C5 should also only be triggered by the first Y in column A. So a Y in a4 would still produce a 70 in c5. But c5 needs to remain in c5 and all cells above it must remain free of formulas.

Posted by Sam on January 19, 2002 2:19 AM

Re: Formulas with...

Sorry, and the area I want to use it in extends beyond the 5 rows, so using a nested If statement will not work due to its limitations.

Posted by Aladin Akyurek on January 19, 2002 2:29 AM

Re: Formulas with...

Sam --

It makes now more sense (to me).

If we have in A1:B4

{"",50;
"",60;
"Y",70;
"",80}

where "" stands for empty cells,

enter just in C5: =INDEX(B1:B4,MATCH("Y",A1:A4,0))

This will give you the value that corresponds to "Y" in B. If there is no "Y" in A, you'll get #N/A.

Is this what you're looking for?

Aladin

==============



Posted by Sam on January 19, 2002 3:19 AM

Re: Formulas with...

Mate you are a legend!

Thanks a lot, that worked a treat. I am very appreciative.