Circular reference! ugh... how to fix???

[wildpetals]

Hello! Thank you for reading this. I'm really stumped at how to achieve what I call an "update in place" with excel 2003

I have a worksheet that I print out that is used in the shop. They keep track of the amount of product used and turn this sheet in for update.

The sheet looks like this (simplified)

<table x:str="" style="border-collapse: collapse; width: 342pt;" border="0" cellpadding="0" cellspacing="0" width="457"><col style="width: 35pt;" width="47"> <col style="width: 48pt;" width="64"> <col style="width: 80pt;" width="107"> <col style="width: 95pt;" width="127"> <col style="width: 84pt;" width="112"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 35pt;" height="17" width="47">Row#</td> <td style="width: 48pt;" width="64">A</td> <td class="xl66" style="width: 80pt;" width="107">B</td> <td class="xl67" style="border-left: medium none; width: 95pt;" width="127">C</td> <td class="xl65" style="border-left: medium none; width: 84pt;" width="112">D</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="ROW1 " height="17">ROW1 </td> <td>Material</td> <td class="xl66" style="border-top: medium none;">Last Used Amt</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">balance on hand</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">total used</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ROW2</td> <td>M1441</td> <td class="xl70" style="border-top: medium none;" x:num="">25.00</td> <td class="xl71" style="border-top: medium none; border-left: medium none;" x:num="">50.00</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" x:num="">75.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ROW2 Expected amount
</td> <td>
</td> <td class="xl66" style="border-top: medium none;">25.00</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=D3-C3">25</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=E3+C3">100.00</td> </tr> </tbody></table>

If I put the calculations in another row, they work, as shown above. Basically the formula in C2 (C2-B2) and the formula in D2 is (D2+B2)

If I try to put that cell reference in row 2, it gives me a circular reference.

It is a seemingly simple task.. but yet, I'm stumped.

Is there any way to achive this so I can print the form out again with the current values for C & D?

Thank you very much for your assistance in this matter!!

smiles to all.

 

[snerz]

I assume that your formulas is in cell C3 and D3 and not in C2 and D2 as you have stated. Having them in row 2 will give circular ference as your formula refers to itself (which it can't do).
Snerz

 

[Nalani]

Seems to me this is something like a Check Book. I would think you need a starting balance as what is in row 2 below.
Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Material</TD><TD>Used</TD><TD>Balance</TD><TD>Total Used</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="COLOR: #ff0000">Starting Balance</TD><TD style="COLOR: #ff0000"></TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">50</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">75</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>M1441</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">100</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C3</TD><TD>=C2-B3</TD></TR><TR><TD>D3</TD><TD>=D2+C3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

It would really help if you can show your layout. Click on the link below my post and install. You can then show your sheet as above.

I'm also assuming that you have more then one "Material" number. Which may require a different type of formula(s). So a screen shot would really help. Just a few rows will do.