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?
