Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. | | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. | | |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. | | | | | | | |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. | | | | | | | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Circular (?) | Excel Worksheet Functions | |||
Circular Ref. | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Worksheet Functions | |||
Circular:G4? | Excel Worksheet Functions | |||
Circular references | Excel Worksheet Functions |