adding and subtracting from inventory sheet based on orders

BryanOramas

Hello,

what im trying to accomplice is when selecting a item in the drop down menu "equipment shipped" for example "logitech". i would like it to subtract a quantity of 1. example, we have 200 and in our inventory sheet. i would like it to look at our inventory sheet and subtract 1. i would also like to do this with if we enter a serial number on the shipping tracker we want it to remove or delete the serial number from the inventory as well. first image below is the shipping tracker. where we would enter the item and/or serial number that is going out.

and this sheet is where we want to subtract items and remove serial numbers

jmyzk_cloudsmart_jp

Hi @BryanOramas

Thank you for sharing an interesting question.

Here is a solution.
In the Inventory Tracker sheet, I put each piece of equipment's base inventory level or number in the Sheet Summary fields.

Then, I retrieved the number of pieces of equipment shipped using the COUNTIF cross-shee formula. (The Formulas are shown below.) Substituting the count from the base inventory will give the current inventory number.

As for the Serial Number, I put the Shipped checkbox with the following formula to check if the Serial Number exists in the order sheet.

=IF(CONTAINS(AIO@row, {Order Sheet : Serial Asset}), 1)

Then, using the workflow automation, I cleared the AID's cell value whose checkbox is checked.

Cell formulas

[AIO03]=COUNTIF({Order Sheet : Equipment Shipped}, AIO4)
[Logitech HS]3=COUNTIF({Order Sheet : Equipment Shipped}, [Logitech HS]4)
[Power Cable]4=COUNTIF({Order Sheet : Equipment Shipped}, [Power Cable]4)

Workflow Automation

https://app.smartsheet.com/b/publish?EQBCT=73afdbcb9e51469b8c312935d4ac0c21

BryanOramas

Thank you for your Response, i was hoping there was a way of doing this without changing my sheet much. what im trying to accomplice is when when i add a serial number to the shipping tracker i would like it to look for that same serial number in our inventory sheet. i think i may still be confused with your response and im not really understanding the sheet summary and how that would come into play.

trying

jmyzk_cloudsmart_jp

@BryanOramas

You do not have to use the Sheet Summary fields if you do not want.

I directly put the Base Inventory numbers in the second row on the sheet below.

https://app.smartsheet.com/b/publish?EQBCT=cbc66b640b6748248b1ed6b85f10c5a2

I changed the Shipped column to show the Equipment name whose serial numbers are on the order sheet. That way, you do not have to create multiple shipped help columns for each piece of equipment.

=IF(HAS({Order Sheet : Serial Asset}, AIO@row), "AIO") + CHAR(10) + IF(HAS({Order Sheet : Serial Asset}, [Laptop I7 32 GB]@row), "Laptop I7 32 GB")

Since I set the Shipped column as a multiple-select dropdown list, I concatenated the IF statement with CHAR(10) to show multiple values, such as AIO and Laptop I7 32 GB, in the same cell.

Then, the conditional formatting strikes out the serial number if the corresponding equipment name is listed in the Shipped column.

Unfortunately, the updated workflow automation does not work as it violates this rule.

To prevent infinite loops, Smartsheet doesn’t trigger actions that change the sheet automatically by inbound cell-links or cross-sheet formulas. This includes formulas that refer to another cell with an inbound cell link or cross sheet formula.

https://help.smartsheet.com/articles/2479236-trigger-blocks-when-your-workflow-is-executed

So, I changed the workflow trigger to one based on time.

The workflow worked as I tested by the "run now" on rows 5 and 6.