auto change cell formula on condition of worksheet change in other cells

**futurejock** · 05-31-2009, 11:30 AM

Hi Everyone,

I would like a cell to use two formulas (one or other (not simultaneous)),
one primary, and one secondary (if/only when conditions apply).
Can it be done?

ASSUME:
in Worksheet one: C1=A1+B1

in Worksheet two:
A1='Worksheet one'!A1
B1='Worksheet one'!B1
C1='Worksheet one'!C1

----------------------
PROBLEM:
in worksheet two,
ONLY if I change a value for A1 or B1 (in worksheet two),
I want C1 formula to be =A1+B1 (in worksheet two)
otherwise I want it to be ='Worksheet one'!C1

Can I have the formulas tiered?
Can the formula for C1 (worksheet two) change from:
='Worksheet one'!C1
to:
=A1+B1
when and only if A1 or B1 (in worksheet two) change?

**stanleydgromjr** · 05-31-2009, 02:01 PM

futurejock,

Welcome to the Excel Forum.

PROBLEM:
in worksheet two,
ONLY if I change a value for A1 or B1 (in worksheet two),
I want C1 formula to be =A1+B1 (in worksheet two)
otherwise I want it to be ='Worksheet one'!C1

Are you MANUALLY changing worksheet two, cell A1 and/or cell B1?

**stanleydgromjr** · 05-31-2009, 02:37 PM

futurejock,

If you are MANUALLY changing "Worksheet two" cell A1, or cell B1, Then see the attached workbook "Worksheet_Change Event - auto change cell formula on condition of worksheet change in other cel.xlsm" with the "Worksheet_Change" Event in "Worksheet two".

In "Worksheet two", manually change either A1 or B1 and see the chnage to C1.

If you delete the entries in either A1 or B1, then all the original formulae are returned.

**futurejock** · 05-31-2009, 02:50 PM

ThankS!!!

Yes I am manually changing workbook two cells.

Will try!

Dan

**futurejock** · 05-31-2009, 05:36 PM

Stanley's workbook did not work for me.

Still interested in ideas/solutions...

Thanks,

Dan

**JBeaucaire** · 05-31-2009, 06:25 PM

Hmm, maybe in Sheet2!C1:

=IF(AND(A1=Sheet1!A1,B1=Sheet1!B1),Sheet1!C1,A1+B1)

Although, unless you can demonstrate differently, I think just putting =A1+B1 into that cell will result in always having the answer you want.

==========
EDIT: As per forum rules, always include a link in this forum to other forums where you have posted the same question:
http://www.mrexcel.com/forum/showthread.php?t=393480

**stanleydgromjr** · 05-31-2009, 09:00 PM

futurejock,

Please post your workbook - scroll down and see "Manage Attachments".

Or, per our "Private Messages", send you workbook to my e-mail address.

**futurejock** · 05-31-2009, 10:55 PM

Hi All,

Don't know if this has worked but the demonstration of solution is in the workbook I hope is attached to this message -- furnished by jbeaucaire.

Thanks,

Dan

**JBeaucaire** · 06-01-2009, 12:11 AM

Looks like it works to me. This is a non-macro solution, so you don't need the .xlsm file format.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)