Incrementing cell values by a formula and not a system column

Rob Pritchard

I thought this would be relatively straightforward. I am looking for a formula that will increase the previous cell value by one when the cell contains a letter followed by the numbers i.e P1159 next row P1160 next row P1161 etc. Every time we create a new row the column formula will increase the previous row by 1.

I cant use a system column because i move the row to another sheet with a different autonumber sequence it still keeps its number from its original sheet and we need to number the same as the rows in this new sheet ?

Hope that make sense ?

Paul Newcome

@Rob Pritchard Did you try Genevieve's suggestion?

edapel

Could you use the # Auto Number column type? I use it for my RAID logs and other sheets where I have to have specific ID's. You can set the prefix as P then start your numbers where you wish. Each new row as it is added should automatically update in your sequence.

Rob Pritchard

Thanks but sorry Autonumber although good and works. When we move the row over to it's live sheet it keeps the Autonumber given to it. In its new sheet it needs to pick up the new number from the auto numbering scheme in that sheet.

Rob Pritchard

Still struggling with this formula? Can it be done without using the Autonumber system column?

Genevieve P.

Hey @Rob Pritchard

If you just need to list the row number with a P in front, you can use the MATCH function to return the row number. Match against something unique, such as that Autonumber column.

="P" + MATCH(Autonumber@row, Autonumber:Autonumber, 0)

Then if you rearrange rows it will re-number them with the current row number + P.

Cheers,

Genevieve

Rob Pritchard

Hi Thanks for the update. I probably haven't explained clearly what it is I am trying to achieve.

We have two sheets one called "pipeline" (source) and the other "projects" (destination). We want to move rows between the two sheets and allocate a new reference number in the destination sheet.

When we create a new pipeline project we allocate a unique reference number beginning with P, by using the system column autonumbering. In the project sheet we allocate a new project number from the autonumbering system column in that sheet.

We are now looking to use an automation to move the row across to the project sheet and when it lands in the project sheet it will be given a new project reference using the projects autonumbering scheme. The move across though doesn't allow the row to be renumbered and it keeps the number allocated from the pipeline auto numbering ?

So i am looking to use a non system generated numbering scheme which counts up from P1111 to P1112 to P1113 etc ? Using a formula within the cell ? The autonumbering is not to be used in the source sheet so that when the row moves over it picks up the autonumber scheme for that new sheet ?

Hope that makes sense ?

Or any other thoughts as to way ?

Paul Newcome

@Rob Pritchard Did you try Genevieve's suggestion?

Rob Pritchard

Yes but it relies on a system column which I need to avoid in the source sheet.

Paul Newcome

But if you use that solution in both sheets, it shouldn't cause any issues.

Rob Pritchard

i am not sure it does ? when we have an autonumber column in both sheets, when you move them across they keep the same number from the source sheet even when the destination sheet has its own autonumbering scheme ?

Genevieve P.

Hi @Rob Pritchard

In your second sheet, you can hide the auto-number column that contains the number from your first sheet. Then use the formula to reference that hidden column but have a new numbering system, based on the row number in that second sheet:

="P" + MATCH(Autonumber@row, Autonumber:Autonumber, 0)

Again, keep in mind this formula is dynamic, so if you moved Row 5 up to Row 2, it would change the P number associated with that row.