How to increment with the ability to 'reset'?

[divide_by_zero]

I am creating a sheet with ~1000 rows and four columns.

One of the columns I want to increment by one (1, 2, 3, etc) but to ascend only to about 36, at which point I will edit the current cell back to '1.'

(This sheet is a record of one calendar year's photos and the column in question is the frame number for slides - hence the need to begin at '1' for each roll of film.)

Is there a way that as I enter my data and tab through that I can have Excel automatically increment until I jump in and edit that cell to begin at "1" and then again continue to increment? Please note that I need to manually reset the value back to '1' because sometimes a roll of film will have 37 or 38 frames.

Thank you in advance for any help.

[AliGW]

Welcome to the forum.

Easily done if you have a helper column where you indicate the beginning of each new film roll.

There are instructions at the top of the page explaining how to attach your sample workbook.

[divide_by_zero]

Thank you for the welcome, Ali.

I've attached the sample.

I have a somewhat related question (I'll be glad to start a new thread if that's more appropriate):

Is there a practical way to automatically populate a cell with the value found directly above the current cell? I would find this convenient for repeating the 'Date captured' since many photos are taken on the same day.

[AliGW]

Sorry - I need a data sample that includes at least two rolls of film in which you show where the second film roll starts.

[bebo021999]

Assum G2 is starting frame#

G3 drag down is running consecutive number from 1 to 36

Please Login or Register  to view this content.

Date in H2

From H3 then drag down:

=H2

You may need to create "Auto Serial" from date and frame#

I2 then drag down:

Please Login or Register  to view this content.

[divide_by_zero]

Included is the updated data sample - I hope it's enough. Sorry for the trouble - I'm not very tech savvy.

You'll notice beginning with serial _0037 the date is not chronological. That's because in film days I used two cameras concurrently.

bebo021999: thank you for your reply - I'll address it after I get more information.

[Pete_UK]

Where do you want the formula to go? in column B?

Pete

[divide_by_zero]

Where do you want the formula to go? in column B?

Pete

The formula to increment by '1' until manually reset - yes, column B.

[Pete_UK]

Put your starting number in B2, then you can use this formula in B3:

=IF(A3="","",B2+1)

Copy this down as far as you need to (it will keep incrementing). Then when you want to reset it (e.g. in cell B40) you just need to enter a 1 in that cell and subsequent numbers will adjust automatically.

Hope this helps.

Pete

[ChemistB]

Is there a practical way to automatically populate a cell with the value found directly above the current cell? I would find this convenient for repeating the 'Date captured' since many photos are taken on the same day.

CNTRL + D will automatically populate the cell with what is above it. If you are picking multiple cells, you need to include the cell which contains what is being copied but if it's a single cell, then just CNTRL D works.

[divide_by_zero]

Thank you Ali, Quang, Pete, and ChemistB for your help - you all made my day!

[Pete_UK]

You're welcome - thanks for the rep.

Pete