Ducati Sporting Club UK
General Motorcycles only
General motorcycle issues, including non-Ducati, but keep it motorcycles!
 
 
Thread Tools Search this Thread Display Modes
  #11  
Old 10-May-2006, 11:31
Paul James's Avatar
DSC Member Paul James Paul James is offline
Founder Member
Ducati Meccanica
 
Posts: 2,994
Join Date: May 2001
Quote:
Originally posted by antonye
Quote:
Originally posted by andyb
God it must be exciting at work...................

Haven't you got some paperwork to fill out?

Or some old dears with no lights on their bikes to go and nick
Quote+Reply
  #12  
Old 10-May-2006, 11:33
Paul James's Avatar
DSC Member Paul James Paul James is offline
Founder Member
Ducati Meccanica
 
Posts: 2,994
Join Date: May 2001
Quote:
Originally posted by antonye
You may need to set the cell formatting on the range once you've copied the formulas over. Excel can be funny like this sometimes as it constantly tries to do a "best guess" about the formatting of a cell when the contents change. It's a right pita for us developers!

Spot on again; it tried to set the format to Greek for some of the cells !! All sorted now so I'm going to risk merging the database to my labels and inspection reports.
Quote+Reply
  #13  
Old 10-May-2006, 11:37
andyb's Avatar
andyb andyb is offline
Registered Forum User
BSB Star
 
Posts: 6,720
Join Date: Feb 2002
Mood: Has change happened...must of missed it!
Quote:
Originally posted by antonye
Quote:
Originally posted by andyb
God it must be exciting at work...................

Haven't you got some paperwork to fill out?

They make us do it in excel now.................any help?
Quote+Reply
  #14  
Old 10-May-2006, 11:44
Paul James's Avatar
DSC Member Paul James Paul James is offline
Founder Member
Ducati Meccanica
 
Posts: 2,994
Join Date: May 2001
Quote:
Originally posted by andyb
Quote:
Originally posted by antonye
Quote:
Originally posted by andyb
God it must be exciting at work...................

Haven't you got some paperwork to fill out?

They make us do it in excel now.................any help?

Probably not cos the zeros keep getting added at the other end when it comes to speeding fines :P:P
Quote+Reply
  #15  
Old 10-May-2006, 11:46
Paul James's Avatar
DSC Member Paul James Paul James is offline
Founder Member
Ducati Meccanica
 
Posts: 2,994
Join Date: May 2001
Quote:
Originally posted by Paul James
Quote:
Originally posted by antonye
You may need to set the cell formatting on the range once you've copied the formulas over. Excel can be funny like this sometimes as it constantly tries to do a "best guess" about the formatting of a cell when the contents change. It's a right pita for us developers!

Spot on again; it tried to set the format to Greek for some of the cells !! All sorted now so I'm going to risk merging the database to my labels and inspection reports.

Spoke too soon saved the database then tried to merge and the leading zeroes dissapeared. Checked the format again and the whole column has reformatted to Greek. Arghhhhh!!! Bl**dy Microsoft !!!!*"+***
Quote+Reply
  #16  
Old 10-May-2006, 14:13
moto748 moto748 is offline
Registered Forum User
Big Twin
 
Posts: 1,385
Join Date: Apr 2004
Only consolation is, it's still a much better piece of software than Word!
Quote+Reply
  #17  
Old 10-May-2006, 14:17
guest1 guest1 is offline
Registered Forum User
Big Twin
 
Posts: 1,173
Join Date: Mar 2008
Paul, you need to include text in your formula and copy that formula to all of your cells.
e.g. in cell C8 I have entered 7425
I have formatted cell C8 to 6 0's (custom format)
in cell C9 I have entered a number 12 (no custom formatting)
in cell C10 I would normally have entered =C8*C9 and seen a result of 89100
Custom formatting cell C10 could produce 089100 with your leading zero's, but you could also use the following formula instead:
=TEXT(C9*C8,"000000")

Unfortunately that does turn the cell into text but you can still use that text in another formula e.g. =C10*1 = 89100

Merging into Word you will need to alter the field formatting withinthe word document - use F9 to reveal field codes
Quote+Reply
  #18  
Old 10-May-2006, 15:06
Paul James's Avatar
DSC Member Paul James Paul James is offline
Founder Member
Ducati Meccanica
 
Posts: 2,994
Join Date: May 2001
Quote:
Originally posted by guest1
Paul, you need to include text in your formula and copy that formula to all of your cells.
e.g. in cell C8 I have entered 7425
I have formatted cell C8 to 6 0's (custom format)
in cell C9 I have entered a number 12 (no custom formatting)
in cell C10 I would normally have entered =C8*C9 and seen a result of 89100
Custom formatting cell C10 could produce 089100 with your leading zero's, but you could also use the following formula instead:
=TEXT(C9*C8,"000000")

Unfortunately that does turn the cell into text but you can still use that text in another formula e.g. =C10*1 = 89100

Merging into Word you will need to alter the field formatting withinthe word document - use F9 to reveal field codes


Thanks Alan, interesting option but what I'm trying to do is make up a simple column (which I can do if there are no leading zeros) so that I have a production date say 030406, with 01 to 99 behind that to give me up to 99 batches per day. Then by using the first entry in the column say 03040601 I can put a simple formula in the next row down =that cell+1. This when copied and pasted into the required number of cells gives a simple chronological record, when the date changes by a day it's a simple matter of changing one cell and carrying on copying the +1 formula. Once I've saved the excel sheet I can use the info in all sorts of ways by merging to word documents. Labels, production test records, delivery notes etc.

Just tried amending to suit my application and by formatting all the cells as text it seems to be working.

Thanks again to all for the advice

[Edited on 10-5-2006 by Paul James]
Quote+Reply
  #19  
Old 10-May-2006, 19:34
guest1 guest1 is offline
Registered Forum User
Big Twin
 
Posts: 1,173
Join Date: Mar 2008
Paul, use of formatting can extend to dates as text.
I'll speak in excel cell terms
a table with three columns, starting at C5.
Cell C5 - enter the date, format the cell as custom with ddmmyy
C5 should now say 030406
Cell D5 - enter the number 1, format the cell as custom with 00
Cell D5 should now say 01
Formatting of these two cells is pretty irrelevant really because our formatting will take place in the result cell E5

Cell E5 - enter the following formula: =TEXT(C5,"ddmmyy")&TEXT(D5,"00")
Cell E5 should now contain the text 03040601

Should now be a case of just copying the relevant rows down as far as you need and adding your own formulas.
e,g,
Cells C6 to C100 enter formula =$C$5
Cell D6 enter = D5+1
And just drag down the E cell formulas.

If you get stuck send us a u2u
Quote+Reply
  #20  
Old 11-May-2006, 15:24
Paul James's Avatar
DSC Member Paul James Paul James is offline
Founder Member
Ducati Meccanica
 
Posts: 2,994
Join Date: May 2001
Quote:
Originally posted by guest1
Paul, use of formatting can extend to dates as text.
I'll speak in excel cell terms
a table with three columns, starting at C5.
Cell C5 - enter the date, format the cell as custom with ddmmyy
C5 should now say 030406
Cell D5 - enter the number 1, format the cell as custom with 00
Cell D5 should now say 01
Formatting of these two cells is pretty irrelevant really because our formatting will take place in the result cell E5

Cell E5 - enter the following formula: =TEXT(C5,"ddmmyy")&TEXT(D5,"00")
Cell E5 should now contain the text 03040601

Should now be a case of just copying the relevant rows down as far as you need and adding your own formulas.
e,g,
Cells C6 to C100 enter formula =$C$5
Cell D6 enter = D5+1
And just drag down the E cell formulas.

If you get stuck send us a u2u

Seems to be all working now so fingers crossed and thanks again for all the info, much appreciated.
Quote+Reply
Reply
  
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector
Switch to Vertical postbit Use Vertical Postbit

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Recent Posts - Contact Us - DSC Home - Archive - Top
Powered by vBulletin 3.5.4 - Copyright © 2000 - 2024, Jelsoft Enterprises Ltd. - © Ducati Sporting Club UK - All times are GMT +1. The time now is 12:06.