Dynamic cells

[asmth]

Hi

I am trying to dynamically link three cells (A1, A2, A3), where the third cell (A3)is the sum of cells(A1, A2) - totalling 100%. I need a formula which will automatically adjust A1 if A2 and vice versa and keep A3 fixed to 100%.

I am new to writing formulas in Excel and any help with this will be greatly appreciated. I've been going round and round this with no progress.

[ChemistB]

You will need to use VBA to achieve this if what you are asking is....
When you enter a value in A1, A2 adjusts so that A3 (the sum of A1 and A2) remains the same AND
when you enter a value in A2, A1 adjusts so that A3 remains the same.
Formula's reside in cells so when you manually enter a value, you automatically remove the formula.

[asmth]

Thanks ChemistB, that's what I mean. There is no way to do this without VBA? I don't know where to begin with that.

[vandan_tanna]

You can either have user entered values or formulas in a cell but not both (unless you use VBA)

To use equations:

You could have user entries in A1 and A2 and calculations in B1 and B2

B1 = if(trim(len(A1)) = 0, 1- A2, "")
B2 = if(trim(len(A2)) = 0, 1- A1, "")

Edit: Too slow in typing...ChemistB is faster than this Chemical Engineer

[vandan_tanna]

with VBA:

Use worksheet_change event.

Press Alt + F11 to go to VBA editor, select your worksheet in the project browser on the left and paste following code

Please Login or Register  to view this content.

[asmth]

Thanks Vandan. I have tried your equation but it doesn't work for me.

I have just read about the Trim () and it says it's used for removing extra spaces? How does it work in this example please?

[ChemistB]

To use VBA
Right click on the worksheet tab and "View Code"
Copy and paste this code into the now open VBA editor

Please Login or Register  to view this content.

Close the VBA editor (no need to save). Code is set for A1, A2 and A3 as per your example

[vandan_tanna]

Book1.xls

sorry I had trim and len swapped...that is why it didn't work


see attached with VBA and non-vba solution

[asmth]

Thank you both! They all work brilliantly!