Question

Locked

Excel 2003 moving data from a calculation to another cell

By Jeninems ·
I have a workbook that contains order sheetsfor each month and an inventory worksheet. Each time an order is placed, I want the number from the "quantity" field to be susubtracted from its corresponding entry on the inventory sheet. I've used the vlookup function successfully in the price column to pull prices down from the inventory but is it possible to have a formula in one cell and have it place the results in another? It seems fine pulling data from other cells into the active one but doesn't seem to go the other way. Help!

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Did you solve this one?

by LocoLobo In reply to Excel 2003 moving data fr ...

The simplest way I can figure this one without writing a macro would be to use the SUMIF function. My method would require using an initial Qty on the inventory page. The formula would look something like this:

=B2-SUMIF(Order!$A$5:$B$14,A2,Order!$B$5:$B$14)

Where B2 is the initial Qty
Order!A5:B14 is the range containing the product id and Qty ordered. Column A would have the product id and B the Qty.
A2 contains the product id you want inventoried.
Order!B5:B14 is the summary range. You can look that up in the help file for excel.

The formula above doesn't take into account supplies received. You have 2 ways to do that. First adjust the init each time you receive supplies. Second add a Supply Receiving page and change the formula to look something like:

=B2+SUMIF(SupplyRec!$A$5:$B$14,A2,SupplyRec!$B$5:$B$14)-SUMIF(Order!$A$5:$B$14,A2,Order!$B$5:$B$14)

Hope that helps.

edited to fix my booboo

Back to Software Forum
1 total post (Page 1 of 1)  

Related Forums