New User - Help With Formula!

Mborges

I need help, I need a formula that brings me a value if it changed in the month before my update. If the value has not changed bring the same value.

So, I need the formula in column "N" that bring the month before value of estimated final cost if changed, if not changed bring the same value. The problem is the column "M" is reference the month the report, and the value in column "N" need to refer the month before always. Have solution for this?

kirstie858

I think you'll need to provide a little more info in order to get the formula you're looking for. Where do you keep your historical data? If I'm understanding correctly, I have a similar use case in which I have a workflow that copies my main sheet (A) to another sheet (B) on a monthly interval. On Sheet A I have a Report Date [=TODAY()], so when the workflow writes this data to Sheet B, that column indicates the timeframe. Sheet B is my historical record.

In order to lookup the value from the previous month, I would use an INDEX(MATCH()) formula from Sheet A to Sheet B to bring in the value for that item the prior month. What it's matching on is a little tricky. You'll need to create a composite primary key by concatenating your primary key (Order Number? Opportunity ID? Whatever this is) along with the report date (9/28/2022), subtract one month (8/28/2022) and convert to just the year and month (2022.08). Let's say you're trying to get last month's value for Object ABC, your composite primary key would be something like ObjectABC_2022.08. You then INDEX(MATCH()) this value to the Composite Primary Key you've created on Sheet B.

Mborges

Thanks for your answer.

I have the historical data in many others Smartsheet linked with this one. So I will try let you know more.

My Smartsheet A/B/C are my background data information. But I don't have a Report Date, How can I inserted a "smart date" in my Smartsheet? How can I do that : " when the workflow writes this data to Sheet B, that column indicates the timeframe".

So this photo shows my final report, that is automatically populate by "CBS" column that linked with my others Smartsheet. When I do any change, anytime in the other Smartsheet is automatic update this final report. The problem, I need the information from the Prev. Report (column painted). How can I copy once a month all the information in the last report automatically?

If you need more information let me know.

Thanks.