Locking in results from a formula

MarkCep

I have a sheet that each ROW is a monthly store visit.

I have a column that counts the number of visits each store has and add that number to a unique store visit ID…. example Store 123 visits= 3 then the store visit ID would be: store123-3

Every thing has been working fine, it is great because you can always tell how many store visits there were by the store visit ID.

Problem…… there are times I need to delete a store visit from an earlier date and there are store visits after the row i am deleting - once I delete the store visit is changes all the store visits ID below.

Not a big issue if I was only working in a sheet, but this is the intake sheet for Control Center, and control center uses the Store visit ID to spin the project up.

I want to be able to delete a store visit but maintain the Store visit ID count indicator on store visits after the deleted store Visit. Is there a way to lock in the store visit ID once it is created?

Thanks, hope this makes sense.

Paul Newcome

I would have one sheet (the current sheet) that generates everything and then a second sheet (what will be the intake / working sheet) that is the recipient of a copy row automation being sent from the first.

This way the first has everything and can calculate the IDs correctly. It then uses a copy row automation to send the row over to the second sheet. This locks in the results as static data and is what you will use as your "intake" sheet or your working sheet where you can delete and whatnot.

MarkCep

How coincidental, I was just looking at what possibilities using a second sheet via auto copy new rows. Thanks for the advice, I need to consider how this will impact what I am doing in Control center which uses the current intake sheet to spin up visits.

Paul Newcome

Mapping a new intake sheet in Control Center is pretty straightforward assuming you have all of the same column names for the important columns. Otherwise, you can leave the current sheet mapped as the intake sheet in Control Center, make a copy of it, remove all formulas from the original (make sure you grab the data that is in them first so you can paste it back as static), then set up your copy row from the new copy of the sheet that still contains the formulas.

MarkCep

Thanks so much, I think I am going to take the path of having the second sheet / copy rows added to intake - with a new column that counts the store visit dates and then link that back to the intake sheet to use in creating the unique ID.

Thanks for the help and inspiration