Running inventory count - even when clearing linked worksheet

Question

This may be impossible. My son uses a spreadsheet with inventory counts. The inventory counts are linked to another worksheet for a particular customer project. He wants to be able to clear the customer project worksheet data after it has been completed and still have the inventory count keep a running tally on the master worksheet. Then he would use the customer project worksheet again and have the inventory subtract from the master worksheet. He wants it to keep subtracting the inventory from the master worksheet even when he clears the data in the project worksheet. Is this possible and how?

Answer 1

Hi, This should be possible by using a formula that calculates the sum of the inventory counts from the beginning to the current row. Here are the steps to do this:

1. On the master worksheet, enter the initial inventory counts in column C, starting from row 2.
2. On the customer project worksheet, enter the quantity of items used for each project in column C, starting from row 2. Make sure that the items are in the same order as on the master worksheet.
3. On the master worksheet, in cell D2, enter this formula: =C2-SUM(INDIRECT("'Customer Project'!C2:C"&ROW()))
4. This formula subtracts the sum of the quantities used for each project from the initial inventory count, using the INDIRECT function to refer to the customer project worksheet dynamically. The ROW function returns the current row number, so that the formula adjusts as you copy it down.
5. Copy the formula in cell D2 and paste it down to the rest of the cells in column D. This will create a running tally of inventory counts that updates automatically when you change or clear the data on the customer project worksheet.

I hope this helps, but if you have any further questions or issues, please let me know. Thanks!

I have tried this and it isn't working for me. My original formula to pull from 2 cells on 2 worksheets was:

=[@[Quantity in Stock]]-'Asphalt (2)'!E9+Asphalt!E9

I tried modifying your formula several ways - even for just one worksheet to test and I am messing up. It keeps trying to open another Excel file. On the main worksheet my column is G with inventory starting on line 4. I have tried over 50 formulas but cannot make this work.

=G4-SUM(INDIRECT(“Asphalt (2)'!E9:E”&ROW()))

=G4-SUM(INDIRECT(“Asphalt!E10:E”&ROW()))

=G4-SUM(INDIRECT(“Asphalt’!E10:E”&ROW()))

Answer 2

Sorry, right! It looks like we were on the right track with using the INDIRECT and SUM functions. However, it seems that the issue might be with the use of quotation marks in your formula. In Excel formulas, you need to use straight quotation marks (") instead of curly quotation marks (“ ”). Try replacing the curly quotation marks with straight ones in your formulas and see if that helps.

For example, try using this formula: =G4-SUM(INDIRECT("Asphalt (2)'!E9:E"&ROW()))

Apologies for the runaround. Thanks!

Answer 3

The issue isn't the 3D summing but the fact that I will be clearing out the Asphalt worksheets and want my inventory count to hold. So my inventory is 200 and the asphalt sheets each deduct 25. I clear the asphalt sheets and then want the total to hold at 150.

Answer 4

Darn, sorry about that. Could you try to use a subtraction formula with 3-D references. (A 3-D reference is a reference that refers to the same cell or range on multiple sheets) For example, if you want to subtract the values in cell E9 on both Asphalt and Asphalt (2) sheets from the value in cell G4 on the main worksheet, you can use this formula:

=G4-SUM(Asphalt:Asphalt (2)!E9)

This formula will sum up the values in cell E9 on both sheets and subtract it from the value in cell G4. The colon (:) between the sheet names indicates that you want to include all the sheets between them in the reference. If you have more sheets that you want to include, you can simply add them to the reference. For example, if you have another sheet named Asphalt (3), you can use this formula:

=G4-SUM(Asphalt:Asphalt (3)!E9)

You can also use VLOOKUP function with 3-D references if you want to look up a value based on another value on different sheets. For example, if you want to look up the quantity of a product based on its name on both Asphalt and Asphalt (2) sheets, and subtract it from the quantity in stock on the main worksheet, you can use this formula:

=G4-VLOOKUP(A4,Asphalt:Asphalt (2)!A:E,5,FALSE)

This formula will look up the value in cell A4 (the product name) on both sheets and returns the value in column E (the quantity) of the matching row. Then it will subtract it from the value in cell G4. The FALSE argument indicates that you want an exact match for the lookup value.

I hope this helps you solves it, but please let me know if not. Thanks!

Answer 5

Hi, This should be possible by using a formula that calculates the sum of the inventory counts from the beginning to the current row. Here are the steps to do this:

1. On the master worksheet, enter the initial inventory counts in column C, starting from row 2.
2. On the customer project worksheet, enter the quantity of items used for each project in column C, starting from row 2. Make sure that the items are in the same order as on the master worksheet.
3. On the master worksheet, in cell D2, enter this formula: =C2-SUM(INDIRECT("'Customer Project'!C2:C"&ROW()))
4. This formula subtracts the sum of the quantities used for each project from the initial inventory count, using the INDIRECT function to refer to the customer project worksheet dynamically. The ROW function returns the current row number, so that the formula adjusts as you copy it down.
5. Copy the formula in cell D2 and paste it down to the rest of the cells in column D. This will create a running tally of inventory counts that updates automatically when you change or clear the data on the customer project worksheet.

I hope this helps, but if you have any further questions or issues, please let me know. Thanks!