Perform formula if value is found on another sheet

Joseph Adams

Hello Smartsheet users,

I am attempting to create a sheet that will provide me with Weekly Usage totals of an inventory item so I can use the data to create better forecasts and projections. There might be a better way of doing this but as of right now my plan is as follows:

1. Active Sheet contains all of the current totals of inventory item in question. This is updated live by the team that uses it so the totals decrease over the course of a week.
2. Automation on Active Sheet will make a copy of the inventory items at the end of every week (Saturday) to a new sheet called EOW.
3. Automation on Active sheet will make a copy of the inventory items at the beginning of every week (Sunday) to a new sheet called BOW
4. On the BOW Sheet, create a column that will find the matching Lot ID and Week # from the EOW sheet and subtract the active inventory total in its row with that found on the EOW total. This value should be how much of the specific lot from a given week was used.

The problem I am running into is how to create the formula to subtract out the correct value. This is the current formula I have on the BOW sheet.

=IF(AND(HAS({Polish Lot - EOW}, [Polish Lot ID]@row), HAS({Week # - EOW}, [Week #]@row)), {Active Amount - EOW} - [Active Amount (nmol)]@row, "false")

Any help would be greatly appreciated!

Genevieve P.

Hi @Joseph Adams

Your process sounds like a good idea! That's exactly what I would suggest.

In regards to the formula, you'll want to use an INDEX(COLLECT function to do the matching across the sheets then bring back the number.

An INDEX(COLLECT works like this:

=INDEX(COLLECT({Column to Return}, {Criteria Column 1}, [Criteria 1]@row, {Criteria Column 2}, [Criteria 2]@row), 1)

So in your case, try:

=IFERROR(INDEX(COLLECT({Active Amount - EOW}, {Polish Lot - EOW}, [Polish Lot ID]@row, {Week # - EOW}, [Week #]@row), 1) - [Active Amount (nmol)]@row), "")

Let me know if this works for you and makes sense!

Cheers,

Genevieve

Joseph Adams

Thank you Genevieve for the help though this formula returns #Unrparsable.

There is another issue however that I noticed in the set up of my sheets. I was hoping to use the "Created (Date)" to identify which week I am adding items to the BOW and EOW sheets from the Active sheet. However, the active already has a created date column and so when rows are copied to the BOW and EOW sheets, it displays the original created date taken from the Active sheet, not when the data got copied over to the BOW and EOW sheets which is what I want and how I planned on tracking the current weeks usage. There are automations on the Active sheet that need the created date column so i cant simply remove that one.

Also I noticed in your formula, that if it were to work, I would get a negative number.

INDEX(COLLECT({Active Amount - EOW}, {Polish Lot - EOW}, [Polish Lot ID]@row, {Week # - EOW}, [Week #]@row), 1) This would get me the active amount of the item taken from the end of the week which should be lower than at the beginning of the week. The  [Active Amount (nmol)]@row would then be subtracted out which is the BOW amount. I can just make it positive via absolute but I like being able to display a negative number because it is a flag that something is wrong.

Joseph Adams

I was able to get the formula to return the expected value:

=[Active Amount (nmol)]@row - IFERROR(INDEX(COLLECT({Active Amount - EOW}, {Polish Lot - EOW}, [Polish Lot ID]@row, {Week # - EOW}, [Week #]@row), 1), "")

Was a simple reversal of the formula you suggested. However this is only working for my test rows from today (i.e week 45) since the created date of the test rows is this week as I manually added them to each sheet and have 11/08/2021 as the created date. The data from last week still has the original created date from the active sheet and not the date the data was populated on the EOW and BOW sheets. Not sure how to get around this part.

Genevieve P.

Hi @Joseph Adams

I'm glad you were able to get the formula to work by reversing it!

In regards to the "Created" date, I would suggest having a new column on the sheets you're copying rows to. Set this up as a Date Column, then create a Record a Date workflow for when Rows are Added to the sheet. This will date-stamp when the row was placed in your sheet and you can use this as the column to reference in your formula.

See: Set the Current Date with Record a Date Action

Let me know if you'd like me to clarify further.

Cheers,

Genevieve