Trying to return a value once, but not when cell value changes

KC Jones

Sorry I don't really know how to phrase this. Basically in the projects we have, we get a quote from a vendor, and they automatically enter it into our project sheets.

Because this can change to a lower price or cancelled altogether we set up automation so our price column changes to the lower cost, or to zero if cancelled. However, I have noticed that means we don't get the full picture of the quoted cost. On our dashboard and reports we have the total (after the cost value changed) cost and expended.

I'm trying to set it up so we see all 3; the quoted cost, the final price, and the expended costs. I've tried using SUMIF and various other formulas. I don't know if there is a way to automate it either. I created a quoted cost column and a helper column I intend to hide because I want the final price to still display in the visible column. I think changing that column to the quoted cost would mess with the vendor's automations (they use a different program to auto-enter into our SS). It would also make things confusing to anyone else who looks at the sheet but doesn't work with it like I do.

After trying various formulas I got nothing but errors until I did this:

=IF([Total Price]@row > 150, (SUM(0, [Total Price]@row)))

That formula does return the price in the Total Price value, but it still changes when the Total Price column changes. So I really need a way for it to only record the first value entered. I have made sure my columns are all set as currency and I have used the formula examples sheet and several questions asked here by others with no luck. Is there a way to do this in the project sheet or even in a metrics sheet?

Andrée Starå

Hi @KC Jones

I hope you're well and safe!

This might help!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KC Jones

Here's another one I have tried. Since I'm trying to only collect the information before the changes, and these columns are the reason it changes (tied to the automation) I thought I could set it up to only return the number by these.

If I do this formula it works but only for one answer:

=IF([Site Decision]@row = "Yes", (SUM(0, [Total Price]@row)))

but I think I either I need another function or my syntax is wrong when I try to add the second.

I have also tried this with no success:

=IF(OR(COLLECT([Site Decision]@row, "Yes", ([Site Decision]@row, "Yes, Pending Waiver" = [Total Price]@row)

Andrée Starå

Hi @KC Jones

I hope you're well and safe!

This might help!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KC Jones

@Andrée Starå

I don't know if VLOOKUP would work but I think INDEX/MATCH would. I just don't know how I would write the formula. Each row has a unique number for billing so I should be able to do that. Ex:

BU column is 800100 (fake number for confidentiality), Vendor enters $1000.00 Total Price after an initial check. BU goes onto a PO and Vendor is given NTP. Then maybe the customer cancels the job, so I change the status to cancelled and automation changes Total price to $0.00. Or Vendor goes to location to complete job, but can't due to a variety of factors. Since they have sent someone they have to pay, we then pay a lower amount to cover that cost. So the vendor or I change status and it automatically changes total price to the $150.00

I did already set up the automation to copy the rows to another sheet but I had to manually copy every existing row. How do I set it up from here?

KC Jones

@Andrée Starå

I was able to find an example online and I finally got it working! Thank you so much!

For anyone who stumbles on this in the future, this was my final formula. I used the copy rows automation and created a helper sheet. I then indexed just the data I needed, and matched it by a code that is a unique ID for each row:

=INDEX({Quoted Cost Helper Metrics Range 1}, MATCH([Job #]@row, {Quoted Cost Helper Metrics Range 3}, 0))

*Range one is the cost, Range three is the Job # to match to the project sheet.

I did get the No Match error for the rows that did not meet the criteria to pull into my helper sheet. However I plan to hide the column since I'm using it for reporting only, as I could not sum the column because of the errors. So I created a helper column in my project sheet, and used this formula to convert the errors to a numeric value:

=IFERROR(VALUE([Quoted Cost]@row), 0)

Then I was able to sum the quoted cost by using the sum formula on the helper column.