Automated entry in one field to change values in another instead of manual entry

Wow...sorry for that cryptic title but I wasn't sure how to explain it any shorter (not that that explains it very well!)

Anyway...I'm going to explain this as simply as possible:

I need to make cell A1 = cell D1. Cell A1 is calculated by entering a number in cell E1. Due to the various formulas used, when cell E1 goes up, the value in cell A1 goes down (and vice-versa - When E1 goes down, value in A1 goes up). Cell D1 is calculated using formulas UNASSOCIATED with A1 or E1.

I can't enter a formula for cell E1 to do the calculating due to the circular reference created.

I need some type of code that will automatically figure out what number needs to be in cell E1 to make cell A1 equal Cell D1 without creating the circular reference.

Thanks!

Originally Posted by Josiah

Wow...sorry for that cryptic title but I wasn't sure how to explain it any shorter (not that that explains it very well!)

Anyway...I'm going to explain this as simply as possible:

I need to make cell A1 = cell D1. Cell A1 is calculated by entering a number in cell E1. Due to the various formulas used, when cell E1 goes up, the value in cell A1 goes down (and vice-versa - When E1 goes down, value in A1 goes up). Cell D1 is calculated using formulas UNASSOCIATED with A1 or E1.

I can't enter a formula for cell E1 to do the calculating due to the circular reference created.

I need some type of code that will automatically figure out what number needs to be in cell E1 to make cell A1 equal Cell D1 without creating the circular reference.

Thanks!

Hi,

Can you zip and add the workbook here. If you're getting a circular reference with the cells you mention, then clearly D1 IS associated in some way, directly or more likely indirectly with A1 & E1.

Have you tried the Solver tool?

Rgds

See if this helps. (FYI The cell examples I provided in my original post were not the actual cells involved in the sheet)

??????????????????

Can anyone help????

Thanks







...
..

Here's a bit more of an explanation.

I11 is calculated using various cells including cell C11 and it's formula can never be changed. The only cell that can be changed is C11. Here's a Scenario (Based on the attached ZIP file above):

E2 = 3508.80
If the value of C11 = 0
Then I11 = 11921.33

I need a number that will cause I11 to equal E2. C11 is the number I'm looking for.


When a value is entered into C11, the numbers change as shown (E2 = 3508.80 and always remains the same in this example):

If the value of C11 is changed to 500.00
Then I11 changes to 6296.33 (looking for 3508.80)

If the value of C11 is changed to 600.00
Then I11 changes to 5171.33 (looking for 3508.80)

If the value of C11 is changed to 700.00
Then I11 changes to 4046.33 (looking for 3508.80)

If the value of C11 is changed to 800.00
Then I11 changes to 2921.33 (looking for 3508.80)

If the value of C11 is changed to 750.00
Then I11 changes to 3483.83 (looking for 3508.80)

If the value of C11 is changed to 745.00
Then I11 changes to 3540.08 (looking for 3508.80)

If the value of C11 is changed to 748.00
Then I11 changes to 3506.33 (looking for 3508.80)

If the value of C11 is changed to 748.75
Then I11 changes to 3497.90 (looking for 3508.80)

If the value of C11 is changed to 748.78
Then I11 changes to 3508.81 (looking for 3508.80)

If the value of C11 is changed to 748.781
Then I11 changes to 3508.80 (Got it!)



So you see I'm searching (up to make I11 lower and down to make I11 higher) trying to find the number that will cause I11 (which cannot ever be manipulated) to be equal to E2 (which also can never be manipulated). I11 has to rely on the current formulas involved to get to the number I'm seeking (which is the number in Cell E2 and in this case, it's $747.781).

Does that make any better sense? Someone please help!

.....sigh.....