keep a copy of the first value written to a cell.

cbarth_wheelercat

Screenshot 2022-12-30 162959.jpg

I want to copy and record the first cell value even if the first cell value changes.

I am writing a program to generate PO#'s sequentially only on certain lines, so I can skip category headings. I have this part down well. See the formula here: =IF(Bucket@row <> "", "A****" + "00" + IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Bucket:Bucket, <>""), 0), ""), "")

So, if the cell is populated under "Bucket", it automatically generated a PO# A****001 in the first row, and counts sequentially for each row in which something is put under "Bucket".

The problem is that the value of the PO# is dynamic, so it can be changed after it is created, if a row were to be inserted and its associated "Bucket" cell contains a value, everything after that increases.

I'm hoping to copy the value of PO# into another cell called "Stagnant PO#" in the same sheet and make its value stagnant or locked so that if the source PO# changes, the copied cell does not.

Can someone help me with this?

Andrée Starå

Hi @cbarth_wheelercat

I hope you're well and safe!

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!

Have a fantastic weekend & Happy New Year!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

cbarth_wheelercat

This did not resolve my issue. I copied the row into another sheet and used both Vlookup and Index/Match methods to bring the PO# back, but it is still dynamic and changes in the original sheet, even though it is static in the copied sheet. I guess I need a solution to select and copy the contents of the cell in the copied sheet, maybe?

Here are the VLOOKUP and INDEX/MATCH formulas that I used and they work, but referencing the PO# in the original sheet to find the same in the copied sheet makes it dynamic... so if the PO# in the original sheet changes, so does the outcome.

=VLOOKUP([PO#]8, {Copy of New Project Range 4}, 1, false)

=(INDEX({Copy of New Project Range 4}, MATCH([PO#]7, {Copy of New Project Range 4}, 0), 0))

cbarth_wheelercat

I finally found the solution to what I was trying to accomplish, which was essentially to create PO#'s in a numerically sequential order and keep the PO# static, even when entering them in random order in a list of rows.

For the help of others, I am posting how I finally solved this, as it was not an obvious solution and the solution provided above did not work.

I created a dummy worksheet to copy information to and pull from and called it "Helper".

I wanted the PO# to generate when "Bucket" was populated with "Taxable" or "Non-Taxable" as a trigger.

So, I generated a workflow under Automation to trigger when "Taxable" or "Non-Taxable" was selected in the "Bucket" column. The action is to copy that row to the "Helper" sheet. When the row is coped to "Helper" it pastes the cell values, but not the formulas. That's how we capture the static PO# that doesn't change.

I then created a row called "Row ID" and used "# Auto-Number" to populate it numerically sequentially. I also created a column called "PO Suffix" which will count up each time I create a PO#, and that number will be used to create the next PO#. The "Row ID" cell uses the following formula to generate the next sequential number: =COUNT({Helper Range 1}). Note that the range includes the ROW ID column to PO Suffix columns.

The meat of the manipulation is done in the PO# cells, where the following formula resides:

=IF(Bucket@row <> "", "AC3077 " + RIGHT(10000 + INDEX({Helper Range 2}, MATCH([Row ID]@row, {Helper Range 1}, 0), 3), 4), "")

So, if the "Bucket" column is populated, it creates "AC3077 " (which is our project number) and the right four (4) characters of the number generated, which is 10000 plus the number is "PO Suffix" basically.

When "Bucket" is populated in a row, the "PO Suffix" which is counting and always one ahead of what is already created, will copy over to the "Helper" range and create the next numeric number and keep it static due to the Automation workflow created above. The formula in PO# will go find that number by using "INDEX" and "MATCH" and return the new "PO Suffix" number, which we combine with the "AC3077 " and the right 4 numbers of 10,000 + the new "PO Suffix" brought from "Helper" sheet.

In the example, AC3077 0002 was created last and AC3077 0003 will be created next no matter what row we are on and so forth.

I hope this helps someone else trying to do a similar function in SmartSheet.

Screenshot 2023-01-09 071838.jpg

Screenshot 2023-01-09 071745.jpg

Screenshot 2023-01-09 071514.jpg

Screenshot 2023-01-09 071444.jpg