How do I override a cell that is set to Auto Number?

egarczynski

I have two sheets with an auto number column named "Work ID". The rows from one sheet get moved over to the other, and then the new sheet adds children to that row. I need to keep the column set to Auto Number in the second sheet in order to be able to move from the first, but I don't actually want to use the Auto Number properties on the second sheet. I want to be able to override the cells to have a formula instead. Is there a way to do this?

Brad Klodowski

Unfortunately - not that I'm aware of. The Copy Row and Move Row automations will fail if the destination sheet doesn't have an auto-number row in the column that the source sheet does, and that precludes entering any sort of formula (or anything for that matter) in the auto-number column.

The closest thing you can get to a workaround is using the auto-number and a second text column to generate the 'real' auto-number in the second column - depending on requirements this can be as easy as a direct reference in a column formula, e.g. '=[Auto Number]@row'. Then hide the original Auto Number column in both sheets, which leaves you with the following:

Sheet 1: Auto-number column (hidden) that generates the number + Text/Number column that references it via column formula (which you can use for whatever business purpose you need).

Sheet 2: Auto-number column (hidden) which exists to satisfy the move/copy requirement + Text/Number column with the same name as the one in Sheet 1 that you can now use whatever formula is desired in.

Please let me know if this doesn't make sense and I can explain further!

Brad Klodowski

Unfortunately - not that I'm aware of. The Copy Row and Move Row automations will fail if the destination sheet doesn't have an auto-number row in the column that the source sheet does, and that precludes entering any sort of formula (or anything for that matter) in the auto-number column.

The closest thing you can get to a workaround is using the auto-number and a second text column to generate the 'real' auto-number in the second column - depending on requirements this can be as easy as a direct reference in a column formula, e.g. '=[Auto Number]@row'. Then hide the original Auto Number column in both sheets, which leaves you with the following:

Sheet 1: Auto-number column (hidden) that generates the number + Text/Number column that references it via column formula (which you can use for whatever business purpose you need).

Sheet 2: Auto-number column (hidden) which exists to satisfy the move/copy requirement + Text/Number column with the same name as the one in Sheet 1 that you can now use whatever formula is desired in.

Please let me know if this doesn't make sense and I can explain further!