Help me with excel pls

[Bri]

I've gone brain dead today, I'm putting some new templates together, my bus partner does things long winded. I want to make a quote book in Excel 2008 with an auto increment (I found quotes going out with the same quote number, luckily no harm done).

Is there an easy method please?:|

[Postforce]

Hi there

Not sure this is exactly what you are after - but try this. Click on the cell with the initial number you want to start with - drag the little square in the bottom right hand corner down through the cells you want to cover - then click the funny box that appears - then click fill series. Good luck

[Bri]

Not quite what I wanted to achieve but it has helped for now, thanks for the help.

[JElder]

Have a look at using a database (such as Access) as this will provide auto incremented number automatically.

It's also more flexible for searching and sorting.

[spreadsheetsdirect]

If you're just using a table and the quote number is a general number without letters then you should be able to use =cellref+1 e.g. if A2=1000 in A3 put =A2+1. When you drag it down it will add 1 on to the previous cell.

[alanc]

I did this once, but I had to use the VBA facility built into Excell (requires programming knowledge). From memory, had a 'Produce Quote' button that read in the number from a little file, incremented it and wrote it back to the file. That way, all copies of the spreadsheet had a unique number. I think this is what you're after.

[Bri]

Thanks again all, I've mocked up one to trial just using fill, this will give me time to set up all your suggestions. I'll probably try the Access one first that sounds more in line with what I'm trying to do as that will help with two other options I want to include.

[FreelanceSoftwareDeveloper]

I think I know what you require. You will need another Excel file with a quote number in, put it in A1 for simplicity. When you open the template the macro will open the other file, add 1 to a cell, copy the value into a location in your new file, save the number file and close it.

[Bri]

I think I know what you require. You will need another Excel file with a quote number in, put it in A1 for simplicity. When you open the template the macro will open the other file, add 1 to a cell, copy the value into a location in your new file, save the number file and close it.

I'm gonna reread this in the morning, sort of lost me there but thanks.:|

[FreelanceSoftwareDeveloper]

I probably made it sound quite complicated but it isn't.

You need to create another Excel file with your latest quote number in, Just put the number in Cell A1 and save it somewhere.

Then using the Developer Tab Insert a Button on your template sheet.

Put this code in as the Button Click

Sub Button1_Click()
Dim wbk1 As Workbook
Set wbk1 = Workbooks.Open(Filename:="C:\number.xlsx")
wbk1.Activate
Range("A1").Value = Range("A1").Value + 1
Range("A1").Copy
ThisWorkbook.Activate
Range("B2").Select
ActiveSheet.Paste
wbk1.Save
wbk1.Close

End Sub

Set the filename path to the number file you created, when you click the button it opens the file, adds 1 to the number in my example in Cell A1, copy/paste it into your quote Cell B2 in my example. Saves and closes the number file.

If you need anymore help just PM me. I'm just starting a new business developing Excel Applications.

You could add the code to run automatically when you create a new file, but using a button will give you the chance to try it out and see if it's working OK

I hope that helps.

Matt

[spreadsheetsdirect]

If your version is Excel 2008 for a Mac then that code won't work as MS didn't bundle Visual Basic with it. You'll have to translate it into Applescript.

[Bri]

If your version is Excel 2008 for a Mac then that code won't work as MS didn't bundle Visual Basic with it. You'll have to translate it into Applescript.

its XP mate.........having another dabble with it this weekend.