Recurring Numbers

[albertmb]

Hi Everyone,

I have a mini invoicing system and I would like to insert invoice numbers
that would increment automatically.

At the moment I am using this formula:=IF(K10>0,'01'!L7+1,"")

'K10' is the cell I insert the date in so I can see the invoice number only
after I insert the date. At the moment I am using about 300 workshets but I
can not find a way of how to copy the formula and changing the previous sheet
number.

Any help will be greatly appreciated

Thanks
Albert

 

[ShaneDevenshire]

Hi,

Exactly how do you want your number to increment. What is the first number
and what is the second and so on...

What does the copying of spreadsheets have to do with the incrementing? Do
you mean that when you copy a sheet you want the incrementing to occur?

 

[RagDyeR]

See if this web page of John McGimpsey helps:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi Everyone,

I have a mini invoicing system and I would like to insert invoice numbers
that would increment automatically.

At the moment I am using this formula:=IF(K10>0,'01'!L7+1,"")

'K10' is the cell I insert the date in so I can see the invoice number only
after I insert the date. At the moment I am using about 300 workshets but I
can not find a way of how to copy the formula and changing the previous
sheet
number.

Any help will be greatly appreciated

Thanks
Albert

 

[Gord Dibben]

Assuming L7 on each sheet is where you want the incremented number..........

Copy/paste this Function to a general module in your workbook.

Function PrevSheet(rg As Range)
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Enter the number 1 in L7 on first sheet.

Select second sheet then SHIFT + Click on last sheet to group these sheets.

Select L7 on active sheet and enter this formula which will be entered on all
selected sheets.

IF(K10>0,prevsheet(L7) +1,"")

Ungroup the sheets and you're done.


Gord Dibben MS Excel MVP

 

[albertmb]

Thank you for your help, it worked perfectly. May I take this opportunity to
wish you and your family a most prosperous new year.

 

[albertmb]

Thank you very much for your concern. May I take this opportunity to wish you
and your family a most prosperous new year.

 

[albertmb]

Thank you very much for your concern. May I take this opportunity to wish you
and your family a most prosperous new year.

 

[tourpro]

could you please give more detail on this...
1. Copy/paste this Function to a general module in your workbook... what is
a general module?

2. Copy/paste this Function to a... is this mean a single cell or many cells?

3. Select second sheet then SHIFT + Click on last sheet to group these
sheets. not sure what this means?

4. how do you group and ungroup sheets?

thank you

 

[Gord Dibben]

See reply to your other post.

Gord

could you please give more detail on this...
1. Copy/paste this Function to a general module in your workbook... what is
a general module?

2. Copy/paste this Function to a... is this mean a single cell or many cells?

3. Select second sheet then SHIFT + Click on last sheet to group these
sheets. not sure what this means?

4. how do you group and ungroup sheets?

thank you