Contact a local school/college saying you have a small ICT project that'd be perfect for an A-Level ICT student.zenie said:I dont know any ICT students
I have had this suggested to me - but I'm not sure if you can change the layout to make it look more like the company branding?
http://www.freedownloadscenter.com/Business/Accounting_Tools/Enquiry___Quote_Generator_Download.html
Anyone really good at Excel (Macros)
- Thread starter zenie
- Start date
Hollis said:Ah..Learn how to use Sage then!
Its the sort of thing which may have a module allowing you to put quotes together and then, if the actual project occurs, you can convert it into 'jobs' etc..with the associated cost centre etc... iirc.
Hence, you may be duplicating stuff in excel than can be done on the main package.
Alternatively you might want get a unique project number from Sage, use it for the Excel quote, and then revert back to Sage when/if the job goes live.
()
http://www.sage.co.uk/productsandservices/home.aspx?tid=131897
sleaterkinney
Well-Known Member
You need to have a spreadsheet somewhere on the network and everytime you save a quote, do a new worksheet in this spreadsheet and save the work to it.
On the first sheet of the spreadsheet have a cell with the number which you increment each time.
On the first sheet of the spreadsheet have a cell with the number which you increment each time.
sleaterkinney
Well-Known Member
I guess so, or she could just fill in a line with the quote numbersXanadu said:
free spirit
more tea vicar?
OK here's a bodge that should work if you really can't get access, and bear in mind I no nowt about macros etc.
OK you need 2 sheets open in excel, one's going to be your form, the 2nd is your database.
1 - set your form out how you like with all the relevant sections
2 - set you database sheet out with one column to match each of the data entry sections of your form, with the first column being kept for the order number / id number.
3 - use the formulas to link the relevant cell in the form with the top cell in each column - ie for the order number the cell value would be =worksheet2!a2
and so on so that all the data bits of the form are linked to the relevant column of the 2nd line in the second sheet.
4 - At line 4 in the 2nd sheet, start your records with number 1, if you want to auto number the first column simply fill out 1, 2, 3 in the cells, then highlight all 3 cells, click in the bottom right corner, hold it down and pull the mouse down the sheet, this should autonumber the cells all the way down.
5 - each time you want to create a new record simply input the data into the next line on the data sheet. Once you've inputed the data, copy the entire line, then paste that into line 2 on the sheet. This should then be linked to the form, and will automatically fill in the relevant bits in the form. Print off 2 copies of the form, and make sure you keep a paper copy.
6 - to create the next record just repeat the process.
this means you'll keep a record of all the info for all quotes you ever do on sheet, though you won't actually have an electronic version of the actual form you printed, you will have kept a printed version filed properly (won't you?), and in any case can easily generate a new copy of the form by simply copying and pasting.
It's a bodge, but providing you get the columns right in the first place, it will be in a format that means you can easily transfer the data to access.
It also means you won't have to type the info out twice, just one cut and paste will sort it.
hope this helps, i'm off to the pub now, and expect this idea to have been picked apart by the time I return
OK you need 2 sheets open in excel, one's going to be your form, the 2nd is your database.
1 - set your form out how you like with all the relevant sections
2 - set you database sheet out with one column to match each of the data entry sections of your form, with the first column being kept for the order number / id number.
3 - use the formulas to link the relevant cell in the form with the top cell in each column - ie for the order number the cell value would be =worksheet2!a2
and so on so that all the data bits of the form are linked to the relevant column of the 2nd line in the second sheet.
4 - At line 4 in the 2nd sheet, start your records with number 1, if you want to auto number the first column simply fill out 1, 2, 3 in the cells, then highlight all 3 cells, click in the bottom right corner, hold it down and pull the mouse down the sheet, this should autonumber the cells all the way down.
5 - each time you want to create a new record simply input the data into the next line on the data sheet. Once you've inputed the data, copy the entire line, then paste that into line 2 on the sheet. This should then be linked to the form, and will automatically fill in the relevant bits in the form. Print off 2 copies of the form, and make sure you keep a paper copy.
6 - to create the next record just repeat the process.
this means you'll keep a record of all the info for all quotes you ever do on sheet, though you won't actually have an electronic version of the actual form you printed, you will have kept a printed version filed properly (won't you?), and in any case can easily generate a new copy of the form by simply copying and pasting.
It's a bodge, but providing you get the columns right in the first place, it will be in a format that means you can easily transfer the data to access.
It also means you won't have to type the info out twice, just one cut and paste will sort it.
hope this helps, i'm off to the pub now, and expect this idea to have been picked apart by the time I return
(i do have A-Level ICT students) but its the sort of thing i would suggest using a database for, rather than excel.
Much easier. However... I'm sure ther is a way of doing it in excell, however saying that would (as said) require VBA.
MS access would be your best bet, if you are looking for a low cost solution (do you have that at the office?)
Much easier. However... I'm sure ther is a way of doing it in excell, however saying that would (as said) require VBA.
MS access would be your best bet, if you are looking for a low cost solution (do you have that at the office?)
free spirit
more tea vicar?
zenie said:LOL
Well it's all my own fault really
I *suggested* to my boss that we really should be attaching quote numbers so that we can track the quotes and have some kind of system of when quotes get turned into jobs, and so that we can realte the jobs back to an actual quote.
The way we used to do it at my last place was just by having a quote spreadsheet - hand done in Excel.
I did this for him and he said it seemed to make more work not make things easier.
He suggested using the form functon in word but I cant find a field that generates a new number - you can only use things like drop downs etc.
really I want to be able to open up a template in word it generates a Quote number, you fill in all the info using a combination of drop downs and filled in text, word then send this info to an excel spreadsheet.
You then save the quote and send it to the client.
er
access / accounts package then probably
zenie
>^^<
Loki said:If it comes down to writing a little VB prog with a dialog then someone will have to get the exact requirements down, get test/sample data, develop, test, bugfix, user test, bugfix, final user test, deploy. Not quite as straightforward as hoped I'd imagine
No
But thankyou all for the replies!!
I am gonna make another cup of tea and try and get my head round all of this!!
I left out documentation too.zenie said:No
BUT - there could very well be a much easier way, lacking one or two bells and whistles but still most of it there. Mebbe someone will have a bright idea. You're best placed to judge really as you know the data and the process inside out.
zenie said:I've really got myself in the shit with this one havent I?
What's wrong with doing a blank form with all the standard stuff in then saving as "quote *n+1* for each new quote? Keep the template where no-one else can get it in case they mess up the shared one.
free spirit's version would work too, and you can drag cells after your first formula is in to do the rest of the form - any gaps (blank cells) need sorting by hand - but it seems you're (Zenie) making it very complicated just to add one to a quote number each time.
Ms Ordinary
randompointlesschemistry
zenie said:LOL
Well it's all my own fault really
I *suggested* to my boss that we really should be attaching quote numbers so that we can track the quotes and have some kind of system of when quotes get turned into jobs, and so that we can realte the jobs back to an actual quote.
The way we used to do it at my last place was just by having a quote spreadsheet - hand done in Excel.
I did this for him and he said it seemed to make more work not make things easier.
He suggested using the form functon in word but I cant find a field that generates a new number - you can only use things like drop downs etc.
really I want to be able to open up a template in word it generates a Quote number, you fill in all the info using a combination of drop downs and filled in text, word then send this info to an excel spreadsheet.
You then save the quote and send it to the client.
I'm sure I have done stuff like this using Filemaker Pro - which is very easy to use - but I would have no idea how to do it using Excel.
I need to learn Excel stuff too
Shippou-Sensei
4:1:2.5
if he wants it all linked then it really sounds like a database job
also with a database like that you could do it with php/mysql and wouldn't have to buy and new software
(though if you had access and wanted the excell intergration then you could use that just as easly [though i dunno why you would want to])
however that option of course requiers some expertise in that area to set up
i'd say got for the date time option that way it is also really easy to track as it will be unique (if you go down far enough) and also can be used to trace the moment it was formed
the only reason people use incremental numbers is because they are used to them there is no reason why you cant have an identification number that read
11102006-022004 (11/10/2006 - 2:20:04) that would be unique unless another documment was created that same second add trenths of a second if worried
this will be much simpler to impliment and very easy to trace
also with a database like that you could do it with php/mysql and wouldn't have to buy and new software
(though if you had access and wanted the excell intergration then you could use that just as easly [though i dunno why you would want to])
however that option of course requiers some expertise in that area to set up
i'd say got for the date time option that way it is also really easy to track as it will be unique (if you go down far enough) and also can be used to trace the moment it was formed
the only reason people use incremental numbers is because they are used to them there is no reason why you cant have an identification number that read
11102006-022004 (11/10/2006 - 2:20:04) that would be unique unless another documment was created that same second add trenths of a second if worried
this will be much simpler to impliment and very easy to trace
A Dashing Blade
Takes horses to water but can't make donkeys drink
oicur0t said:
Stick a label on your userform.
In the userform code put this . . .
Private Sub UserForm_Initialize()
Me.Label1.Caption = Format(Now(), "yymmddhhmmss")
End Sub
Job done (?)
What version of Sage is in use ? It could well have the quote-->job functions built in
Or really suggest using Access( instead of Excel
Same as others have said really
Or really suggest using Access
( instead of ExcelSame as others have said really
pootle
little moran
zenie said:LOL
Well it's all my own fault really
I *suggested* to my boss that we really should be attaching quote numbers so that we can track the quotes and have some kind of system of when quotes get turned into jobs, and so that we can realte the jobs back to an actual quote.
The way we used to do it at my last place was just by having a quote spreadsheet - hand done in Excel.
I did this for him and he said it seemed to make more work not make things easier.
He suggested using the form functon in word but I cant find a field that generates a new number - you can only use things like drop downs etc.
really I want to be able to open up a template in word it generates a Quote number, you fill in all the info using a combination of drop downs and filled in text, word then send this info to an excel spreadsheet.
You then save the quote and send it to the client.
If you create a button on sheet 1 to create a new quote you can write a VBA script that will create all the data you want on Sheet 2. You could also have a button to print out a quote using the data from sheet 2. When writing the quote generator script, lookup the highest quote number, then increment that and write it into the location that that number points to. I'll have a play and see waht I can do, and get back to you with a rough copy that can be butchered.
Wintermute
easy tiger
Ok. How about this:
1. Open a new workbook.
2. Go to cell A1 in Sheet 1, enter a "1".
3. Go to the little Excel icon to the immediate left of the "File" menu, right-click on it and select "View Code".
4. In the big white area of the main screen, paste the following:
5. Save and close
Each time you save that file, the value in cell A1 will increment.
Hopefully it's pretty easy to see how to move your "counter" cell to a different one - say, one that you can hide away - (just change the relevant bold bits in the code above) and it should be fairly straightforward to use that value in a formula in another cell that concatenates it into a quote code.
I don't know how you can change it, though, to fire off any event other than a "BeforeSave" (say, BeforeCloseWorkbook or something). I don't know much VBA - anyone know if that event is exposed by Excel?
1. Open a new workbook.
2. Go to cell A1 in Sheet 1, enter a "1".
3. Go to the little Excel icon to the immediate left of the "File" menu, right-click on it and select "View Code".
4. In the big white area of the main screen, paste the following:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("[B][COLOR="Blue"]Sheet1[/COLOR][/B]").Range("[B][COLOR="Blue"]A1[/COLOR][/B]").Value = Range("[B][COLOR="Blue"]A1[/COLOR][/B]").Value + 1
End SubEach time you save that file, the value in cell A1 will increment.
Hopefully it's pretty easy to see how to move your "counter" cell to a different one - say, one that you can hide away - (just change the relevant bold bits in the code above) and it should be fairly straightforward to use that value in a formula in another cell that concatenates it into a quote code.
I don't know how you can change it, though, to fire off any event other than a "BeforeSave" (say, BeforeCloseWorkbook or something). I don't know much VBA - anyone know if that event is exposed by Excel?
