Circular
 

Problem:

Cell A1 is data entry =quantity on hand
Cell B1 is data entry = quantity sold
Cell C1 is data entry = quantity received
Cell D1 is formula = new quantity on hand (A1-B1+C1)

What I want to do is have only one 'quantity on hand' column.

A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
formula I use, IF, AND, etc., it continues to be circular as long as the
value in A1 is part of the formula.

No doubt the answer is perfectly simple, and I'm just not asking Help the
right question. After all, every inventory program must need that basic
function.

Circular
 

Hi Margo,

First advice: Don't do this! Any accidental recalculation will change your quantity, possibly without you even noticing!
Second advice: Look he

http://www.mcgimpsey.com/excel/accumulator.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Margo" wrote in message ...
| Problem:
|
| Cell A1 is data entry =quantity on hand
| Cell B1 is data entry = quantity sold
| Cell C1 is data entry = quantity received
| Cell D1 is formula = new quantity on hand (A1-B1+C1)
|
| What I want to do is have only one 'quantity on hand' column.
|
| A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
| formula I use, IF, AND, etc., it continues to be circular as long as the
| value in A1 is part of the formula.
|
| No doubt the answer is perfectly simple, and I'm just not asking Help the
| right question. After all, every inventory program must need that basic
| function.
|
|

Circular
 

Thank you very much for the response and the link. It's all beyond my
understanding though at this time ... guess I'm going to go take a Visual
Basic course.

"Niek Otten" wrote:

Hi Margo,

First advice: Don't do this! Any accidental recalculation will change your quantity, possibly without you even noticing!
Second advice: Look he

http://www.mcgimpsey.com/excel/accumulator.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Margo" wrote in message ...
| Problem:
|
| Cell A1 is data entry =quantity on hand
| Cell B1 is data entry = quantity sold
| Cell C1 is data entry = quantity received
| Cell D1 is formula = new quantity on hand (A1-B1+C1)
|
| What I want to do is have only one 'quantity on hand' column.
|
| A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
| formula I use, IF, AND, etc., it continues to be circular as long as the
| value in A1 is part of the formula.
|
| No doubt the answer is perfectly simple, and I'm just not asking Help the
| right question. After all, every inventory program must need that basic
| function.
|
|

Circular
 

<guess I'm going to go take a Visual Basic course

You could, of course.

But what remains is that circular references and similar concepts should not be used, unless it is the very nature of the problem
description itself, like for some recursive calculation algorithms.
For record-keeping structures, like the one you describe, I strongly advise NOT to use circular references.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Margo" wrote in message ...
| Thank you very much for the response and the link. It's all beyond my
| understanding though at this time ... guess I'm going to go take a Visual
| Basic course.
|
| "Niek Otten" wrote:
|
| Hi Margo,
|
| First advice: Don't do this! Any accidental recalculation will change your quantity, possibly without you even noticing!
| Second advice: Look he
|
| http://www.mcgimpsey.com/excel/accumulator.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Margo" wrote in message ...
| | Problem:
| |
| | Cell A1 is data entry =quantity on hand
| | Cell B1 is data entry = quantity sold
| | Cell C1 is data entry = quantity received
| | Cell D1 is formula = new quantity on hand (A1-B1+C1)
| |
| | What I want to do is have only one 'quantity on hand' column.
| |
| | A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
| | formula I use, IF, AND, etc., it continues to be circular as long as the
| | value in A1 is part of the formula.
| |
| | No doubt the answer is perfectly simple, and I'm just not asking Help the
| | right question. After all, every inventory program must need that basic
| | function.
| |
| |
|
|
|

Circular
 

To add to Niek's "strong advice".

Doing it as you describe will leave no "paper trail" of inputs so very difficult
to troubleshoot any errors in data entry.

Excel has lots of cells, use them to keep track of your inputs.


Gord Dibben MS Excel MVP

On Sat, 3 Nov 2007 21:42:28 +0100, "Niek Otten" wrote:

<guess I'm going to go take a Visual Basic course

You could, of course.

But what remains is that circular references and similar concepts should not be used, unless it is the very nature of the problem
description itself, like for some recursive calculation algorithms.
For record-keeping structures, like the one you describe, I strongly advise NOT to use circular references.

Circular
 

And I won't use either ... I did try it, and oh my goodness, did the columns
ever go wild.

"Niek Otten" wrote:

<guess I'm going to go take a Visual Basic course

You could, of course.

But what remains is that circular references and similar concepts should not be used, unless it is the very nature of the problem
description itself, like for some recursive calculation algorithms.
For record-keeping structures, like the one you describe, I strongly advise NOT to use circular references.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Margo" wrote in message ...
| Thank you very much for the response and the link. It's all beyond my
| understanding though at this time ... guess I'm going to go take a Visual
| Basic course.
|
| "Niek Otten" wrote:
|
| Hi Margo,
|
| First advice: Don't do this! Any accidental recalculation will change your quantity, possibly without you even noticing!
| Second advice: Look he
|
| http://www.mcgimpsey.com/excel/accumulator.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Margo" wrote in message ...
| | Problem:
| |
| | Cell A1 is data entry =quantity on hand
| | Cell B1 is data entry = quantity sold
| | Cell C1 is data entry = quantity received
| | Cell D1 is formula = new quantity on hand (A1-B1+C1)
| |
| | What I want to do is have only one 'quantity on hand' column.
| |
| | A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
| | formula I use, IF, AND, etc., it continues to be circular as long as the
| | value in A1 is part of the formula.
| |
| | No doubt the answer is perfectly simple, and I'm just not asking Help the
| | right question. After all, every inventory program must need that basic
| | function.
| |
| |
|
|
|

Circular
 

Thank you. And after trying it anyway, I am heeding the warning.

Since I obviously have a lot to learn yet about formulas/functions I've
created a solution by 'Hide' the column with the original values after they
have been entered, my formulas then work well, and the column with the
correct and updated value is the only one showing. And 'Unhide' allows input
checking if necessary.

"Gord Dibben" wrote:

To add to Niek's "strong advice".

Doing it as you describe will leave no "paper trail" of inputs so very difficult
to troubleshoot any errors in data entry.

Excel has lots of cells, use them to keep track of your inputs.


Gord Dibben MS Excel MVP

On Sat, 3 Nov 2007 21:42:28 +0100, "Niek Otten" wrote:

<guess I'm going to go take a Visual Basic course

You could, of course.

But what remains is that circular references and similar concepts should not be used, unless it is the very nature of the problem
description itself, like for some recursive calculation algorithms.
For record-keeping structures, like the one you describe, I strongly advise NOT to use circular references.

Circular
 

Good solution.

Thanks for the feedback.


Gord

On Sat, 3 Nov 2007 15:47:01 -0700, Margo
wrote:

Thank you. And after trying it anyway, I am heeding the warning.

Since I obviously have a lot to learn yet about formulas/functions I've
created a solution by 'Hide' the column with the original values after they
have been entered, my formulas then work well, and the column with the
correct and updated value is the only one showing. And 'Unhide' allows input
checking if necessary.

"Gord Dibben" wrote:

To add to Niek's "strong advice".

Doing it as you describe will leave no "paper trail" of inputs so very difficult
to troubleshoot any errors in data entry.

Excel has lots of cells, use them to keep track of your inputs.


Gord Dibben MS Excel MVP

On Sat, 3 Nov 2007 21:42:28 +0100, "Niek Otten" wrote:

<guess I'm going to go take a Visual Basic course

You could, of course.

But what remains is that circular references and similar concepts should not be used, unless it is the very nature of the problem
description itself, like for some recursive calculation algorithms.
For record-keeping structures, like the one you describe, I strongly advise NOT to use circular references.