I have a very basic excel calc :
- many fixed texts.
- a table with some data to be filled in many columns.
- one more column with automatic calculations, according to other columns.
So, you see, that is not terrific.
But still, there is something I dont how to do it, could you help me :
- 1 cell is called : "BILL NUMBER = "
- the next cell is blank square, and we are used to fill it manually, by
remembering what was the previous Bill Number we have used previously, and
then we simply increment that number, manually, for each new Bill.
- our format of bill number is = "KHA001", "KHA002", etc...
So, now, is it possible to program that cell, to automatically generate a
bill number in the same format as we already use, but not randomly, and using
numbers following each others, incrementally, increasing of 1 unit, from
previous bill, to the new bill ?
How to do that ?
Auto Calculation ?
Special Function ?
Macro ?
VBA ?
External File ?
Data Base ?
Access OLE ?
What else ?
Thank you so much to help me simplify my daily life...

Hi,

Assume A1 contains the bill number which is increased by 1 each time a bill
is produced

i.a range("A1")=range("A1")+1

If B1 contains "BILL NUMBER = " then in C1 put
=CONCATENATE("KHA",TEXT(A1,"0000")

HTH

If A1 contains 123, C1 will be KHA0123


HTH

"bestofcomputer" wrote:

I have a very basic excel calc :
- many fixed texts.
- a table with some data to be filled in many columns.
- one more column with automatic calculations, according to other columns.
So, you see, that is not terrific.
But still, there is something I dont how to do it, could you help me :
- 1 cell is called : "BILL NUMBER = "
- the next cell is blank square, and we are used to fill it manually, by
remembering what was the previous Bill Number we have used previously, and
then we simply increment that number, manually, for each new Bill.
- our format of bill number is = "KHA001", "KHA002", etc...
So, now, is it possible to program that cell, to automatically generate a
bill number in the same format as we already use, but not randomly, and using
numbers following each others, incrementally, increasing of 1 unit, from
previous bill, to the new bill ?
How to do that ?
Auto Calculation ?
Special Function ?
Macro ?
VBA ?
External File ?
Data Base ?
Access OLE ?
What else ?
Thank you so much to help me simplify my daily life...

Typo:

Should be =CONCATENATE("KHA",TEXT(A1,"0000"))

"Toppers" wrote:

Hi,

Assume A1 contains the bill number which is increased by 1 each time a bill
is produced

i.a range("A1")=range("A1")+1

If B1 contains "BILL NUMBER = " then in C1 put
=CONCATENATE("KHA",TEXT(A1,"0000")

HTH

If A1 contains 123, C1 will be KHA0123


HTH

"bestofcomputer" wrote:

I have a very basic excel calc :
- many fixed texts.
- a table with some data to be filled in many columns.
- one more column with automatic calculations, according to other columns.
So, you see, that is not terrific.
But still, there is something I dont how to do it, could you help me :
- 1 cell is called : "BILL NUMBER = "
- the next cell is blank square, and we are used to fill it manually, by
remembering what was the previous Bill Number we have used previously, and
then we simply increment that number, manually, for each new Bill.
- our format of bill number is = "KHA001", "KHA002", etc...
So, now, is it possible to program that cell, to automatically generate a
bill number in the same format as we already use, but not randomly, and using
numbers following each others, incrementally, increasing of 1 unit, from
previous bill, to the new bill ?
How to do that ?
Auto Calculation ?
Special Function ?
Macro ?
VBA ?
External File ?
Data Base ?
Access OLE ?
What else ?
Thank you so much to help me simplify my daily life...

bestofcomputer Wrote:
So, now, is it possible to program that cell, to automatically generat
a
bill number in the same format as we already use, but not randomly, an
using
numbers following each others, incrementally, increasing of 1 unit
from
previous bill, to the new bill ?


I don't know how do you "generate" a new bill? By each of the time
that the file is opened? Or by clicking the sheet?

If yes, I am sure that there shuold be easier way to do.

However, I do not recommend this way of generating a new bill becaus
it is easy to make a mistake that some one may "open" or "click
without intention to make a new bill.

I think you can write a small VBA code in Sheet1 (called "NewBill")
and create a button to assign this macro. The second macro calle
"Delete1bill", and create a button to assign this macro. In column A o
sheet2, you store the sequence number of the Bills that created.

The "NewBill" have 3 simple tasks:
1- Look at the final cell of column A in sheet2, add 1 unit, then writ
this value to the place of the new bill.
2- Delete all the "written" content of the previous bill to create
new blank bill.
3- Write the number of this new bill to the last cell of column A i
sheet2

The "Delete1bill" have 1 simple tasks:
1- Delete the last cell in column A in sheet2.
This macro will hepl you to delete a bill that by mistake, th
"NewBill" button is clicked.

These are simple macro so I hope you can write these yourself. If not
just tell so that we can help you

--
Roc

-----------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...fo&userid=2972
View this thread: http://www.excelforum.com/showthread.php?threadid=49898

I am using Excel 2000 SP3.
And I have never used VBA, or even Macros...
I am ashamed, and very sorry...
So, your programming suggestions were very good, thanks a lot, but I am
unable to program these...
So, could you give me some details about how to do that ?
I would like also to send you my calc, dear Rock, but could you give me your
email ?
Or, I can give you mine, so that you can contact me, and I will forward you
my calc, so that you can include the codes, and mail it back to me.
My email : contact at bestofcomputers . com
Thanks.

"Rock" wrote:


bestofcomputer Wrote:
So, now, is it possible to program that cell, to automatically generate
a
bill number in the same format as we already use, but not randomly, and
using
numbers following each others, incrementally, increasing of 1 unit,
from
previous bill, to the new bill ?


I don't know how do you "generate" a new bill? By each of the times
that the file is opened? Or by clicking the sheet?

If yes, I am sure that there shuold be easier way to do.

However, I do not recommend this way of generating a new bill because
it is easy to make a mistake that some one may "open" or "click"
without intention to make a new bill.

I think you can write a small VBA code in Sheet1 (called "NewBill"),
and create a button to assign this macro. The second macro called
"Delete1bill", and create a button to assign this macro. In column A of
sheet2, you store the sequence number of the Bills that created.

The "NewBill" have 3 simple tasks:
1- Look at the final cell of column A in sheet2, add 1 unit, then write
this value to the place of the new bill.
2- Delete all the "written" content of the previous bill to create a
new blank bill.
3- Write the number of this new bill to the last cell of column A in
sheet2

The "Delete1bill" have 1 simple tasks:
1- Delete the last cell in column A in sheet2.
This macro will hepl you to delete a bill that by mistake, the
"NewBill" button is clicked.

These are simple macro so I hope you can write these yourself. If not,
just tell so that we can help you.


--
Rock


------------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
View this thread: http://www.excelforum.com/showthread...hreadid=498985

Hi,

I tried to send you my email by mesage of this forum, but it doesn't
work.
The message is "not found" for the user name of "bestofcomputer". I
also send an email to , I hope you got it!


However, the macros look like this:

Sub Newbill()

i = 1
'Look for the code of the last bill
Do
BillCode = Worksheets("Sheet2").Range("a1:a65000").Cells(i)
i = i + 1
Loop Until BillCode = ""

LastBill = Worksheets("Sheet2").Range("a1:a65000").Cells(i - 1)
BillCode = LastBill + 1

'Assumed that the place for bill code is in cell C3
'Remember to format this cell to KHA 000. Go to "Format" to do this
'Or you can use CONCATENATE() function to do this
Worksheets("Sheet1").Range("c3") = BillCode

'Delete the content of previous bill. Assumed they are in B3, B5, B7
Worksheets("Sheet1").Range("b3").Value = Clear
Worksheets("Sheet1").Range("b5").Value = Clear
Worksheets("Sheet1").Range("b7").Value = Clear

Worksheets("Sheet2").Range("a1:a65000").Cells(i) = BillCode
Worksheets("Sheet1").Range("a1").Select

End Sub
Sub DeleteBill()

i = 1
'Look for the code of the last bill
Do
BillCode = Worksheets("Sheet2").Range("a1:a65000").Cells(i)
i = i + 1
Loop Until BillCode = ""

Worksheets("Sheet2").Range("a1:a65000").Cells(i - 1).Value = Clear

End Sub

Good luck


--
Rock


------------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
View this thread: http://www.excelforum.com/showthread...hreadid=498985

Dear Microsoft Newsgroup Administrators and Moderators :

A friend "Rock" has replied to my post in the Excel Progamming Forum,
on the 1/8/2006 6:49 AM PST, and, unfortunately, he has written in his post
my full Professional Email ID !
After this, I know what will hapen :
I will receive dayly may spams, junks, trojans, spywares, and viruses, from
very dangereous hacker groups (those who target the MS Newsgroup Posts !) !
So, PLEASE :
Do remove this previous post as soon as possible, and take all necessary
action, so that my Email ID remains protected from those predators !
Thank you so much for doing that.
I forgive to my friend for having done such a thing, because he is very kind
and very helpfull to me.
Bye.
Frederic Laroche.

NB : never post an Email ID in a Forum. Or, if you have to, encrypt it,
please !

I am very sorry Frederic Laroche!

I removed it !

Again sorry


--
Rock


------------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
View this thread: http://www.excelforum.com/showthread...hreadid=498985