Auto-format - 2003
Dec 5, 2008
I am pasting output from SAS into a spreadsheet. The data I am pasting cannot be put into columns beforehand. When I do text-to-columns, the data are auto-formatted. This is how it looks before text-to-columns:
159 xx Char 2 2. $2. xx
This is how it is auto-formatted:
159xxChar22$2.00 xx
The loss of the period [2 to 2.] and the addition of zeros [$2. to $2.00] is a disaster because it makes my SAS code non-functional. Is there anyway to stop auto-formatting in text-to-columns? Is there a more user-friendly software besides Excel that would be better to use?
View 3 Replies
ADVERTISEMENT
Jan 28, 2012
In sheet 1, I have a list of data in A2:D21. In sheet 2, I have formulas in the same range that "paste link" the data. If, there is no data entered in any entire row of the same range in sheet 1, then I want the entire row to hide automatically in sheet 2. In fact, unless there is something entered to start with, I do not want any of the rows to be visible. Is this possible and how?
View 2 Replies
View Related
Sep 12, 2013
I've got a spread sheet at work that is shared between 4 people. It's password protected and its been protected so only four of us can edit it. It was working fine until yesterday for no reason the auto filter stopped working. The drop down arrows are still there but nothing happens when you click on them?
The sheet was made on excel 2003 but one of the users uses the newer excel which we will all eventually get.
View 2 Replies
View Related
Jul 2, 2014
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get the minor files (68 seperate files) to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make matters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
View 7 Replies
View Related
Jan 7, 2010
I have it set to format of mm:ss but whenever I enter a time of greater than 23:59 it subtractes 23:59 from the time autopmatically. any ideas how to stop this from happening?
View 9 Replies
View Related
Jun 17, 2014
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
formula or vba using Excel 2003.
View 2 Replies
View Related
Feb 24, 2009
I've been asked to change a massive batch of Excel 2007 files to 2003 format (to send to a client who doesn't have the newer version). Apart from going into these files (there's over 500 of 'em ), can anyone suggest a means of doing this? I know that Microsoft has a Migration Manager tool, but it appears this only converts the other way around.
View 2 Replies
View Related
Dec 2, 2009
I enter in numbers, eg: 1, 2, 10, 24, 100, 1000 in a consecqutive cell range
They display as: 0.01, 0.02, 0.1, 0.24, 1, 10. Default cell format is general. If I change cell format to Number (with default display of 2 decimal points) it displays: 0.01, 0.02, 0.10, 0.24, 1.00, 10.00. If I also specify no decimal points, it displays: 0,0,0,0,1,10
The only way I can get the numbers I enter to display (and store) as 1,2,10,24, 100, 1000 is to change cell format to text and THEN retype th numbers into the cells. But, then it creates the green error comment in top left of each cell.
View 3 Replies
View Related
Jan 23, 2012
I'm working with three large data sets covering a month of data in 5 minute intervals. There is a single date and time column which I am using some custom formats to list Time in 12 hour format, AM or PM and Day of the week.
Based upon the various analysis scenarios, adding some Autofilter columns is meeting most of my needs. One issue I am having is that I can filter on a single day using the built in list that Autofilter provides. But I have been asked to look at weekend vs weekday.
Since Excel 2003 only offers two filter criteria, I thought I could use a custom filter with Saturday and Sunday as include or exclude criteria. But, what I am findings is that while the Autofilter shows each day in the dropdown, certain attempts to use them within the custom autofilter do not work.
For example, equals Saturday and equals Sunday correctly returns only weekend data. However, if I try changing both to Does not equals, the result is that no data is filtered.Some of my research indicates that the problem is that the date information being presented as days of the week would need to be text strings . This seems to shed some light on the issue as any attempts at using wildcards fail.
View 3 Replies
View Related
Feb 14, 2012
I have several sources of information for client files. the number format includes a reference number and a month, eg 123/11, 456/02. In some cases, the numbers may be presented including the company initials, eg ABC/00123/11. I want to make sure all numbers on all sources are the same as I am using Vlookup and this reference no to copy information from one source to another. Sometimes when I change the format Excel sees this as a date, eg 11/11 is 11-Nov, and in some cases Excel sees a fraction, eg 123/11 is 123 11ths or 123 divided by 11.
I can easily take out the reference no with a find and replace FIND ABC/ replace with . But this often results in a number with leading zeroes, eg ABC/0123/06 will leave 0123/06.
I am using Excel 2003.
View 1 Replies
View Related
Apr 11, 2012
I have been struggling to get dates to remain in a consistent format within my Excel 2003 worksheets. I would like to work out how to keep them permanently in the "dd/mm/yyy" format. I have tried several formatting options. The current method I will explain below:
I have several forms into which the 'date' is automatically entered using the - Format(Date, "dd/mm/yyyy") code. This is then saved into cells on a worksheet using the code
ws.Cells(iRow, 1).NumberFormat = "dd/mm/yyyy"
ws.Cells(iRow, 1).Value = CDate(Me.Date1.Caption)
At this point if I do a search for a date, lets say 23/03/2012 I can retrieve it and everything related to it - all seems fine (also all dates are visually in the correct format in my cells etc).
The problem arises when I exit Excel and save the workbook. When I go back into the workbook at a later time and try and search for the same date it won't work. Although the dates in the cells still read for example 23/03/2012, the search requires me to now use the format 03/23/2012 to retrieve the information (It seems like in the background the date has reverted to the US date format upon saving and yet visually it is still how I've set it up).
View 1 Replies
View Related
Jun 28, 2012
I am looking to standardise data entry for a cell, so that it appears as such: 2011/031T/0003
Data may be entered as 11/031T/03 and is not always consistent.
I am hoping that a custom cell format will do the trick, to keep it simple however the combinations that I have tried have not worked.
This format needs to be Excel 2003 friendly.
View 9 Replies
View Related
Oct 25, 2012
Is there a way to scan a document into excel 2003 and be able to keep the current format ? or some way to be able to get it into excel and the easliy put it back to the format I need?
View 2 Replies
View Related
Apr 11, 2013
Excel 2003 / WinXP. I have a list object with 5 columns, A - E, and all cells have a border round them. I need a macro which will:
1. insert a row below the current row (whatever cell happens to be selected)
2. then remove the left hand border of the cell in column B in the new row,
3. then select the adjacent cell in column C in the new row ready for data entry.
Obviously I cannot specify any absolute addresses in the code, and this is where all my attempts to record a suitable macro fall down.
View 7 Replies
View Related
Mar 27, 2013
Is there a way to disable the Convert button on the File menu and also to limit the Save As file types? I have a workbook that I maintain in Office 2003 that gets filled out by customers and returned. When a user with a newer version of Excel converts the file, it doesn't function properly when I go open it. I believe the conversion issue has to do with Active X Controls, but I'm using them to do some things that won't work with Forms Controls, so replacing them would be a last resort. Also, upgrading Office isn't an option at this point.
View 1 Replies
View Related
Sep 17, 2008
I have a VBA subroutine that builds a list of dates in an array and then copies this to the worksheet using worksheetfunction.transpose. The array is two dimensional and therefore uses the variant data type.
This works fine in Excel 2002. In Excel 2003 the array is fine until the Transpose function is used, at this point they are changed from dd/mm/yyyy to mm/dd/yyyy. This means that some dates are changed (eg: 01/Mar/2008 becomes 03/Jan/2008) and some are written as strings (eg: 17/Mar/2008 becomes the string "17/03/2008" and cannot be operated on as a date).
I'm aware that there was a hotfix to deal with a similar issue regarding recalculation but this is on Excel 2003 SP3 which should already have that hotfix in place.
I have reduced my code down to a basic demonstration showing the problem. This assumes the worksheet contains some dates in "A2:A32". I have also attached the workbook containing this code.
VB:
Private Sub CommandButton1_Click()
Dim a() As Variant, c As Integer
Redim a(1 To 31)
For c = 1 To 31
a(c) = Cells(1 + c, 1)
Next c
Range("D2:D32") = WorksheetFunction.Transpose(a)
End Sub
I have fixed this problem by looping through each element of the array and writing them individually in to cells but this is far slower so I'd like to know if there is a better solution than that.
View 2 Replies
View Related
Jun 9, 2008
I've got a simple macro for asking a user to locate a file of choice and inserting it into specific cell.
Is there a way I can code it so that regardless of the image it 'fits to fill' the cell? The images may vary
The size of the cell will be fixed, but I don;t know who to ascertain that information or tell VBA what it is?
Sub InsertLogo()
Range("A1:E5").Select 'merged cell'
Application.Dialogs(xlDialogInsertPicture).Show
'Object.AutoSize = True
End Sub
I tried using the 'Object.AutoSize = True, but kept getting stumped on a run time error.
View 9 Replies
View Related
Jan 27, 2013
I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.
Here's what I have as start info and where I want to get to.
Sheet1
A
B
1
Ranges
[Code] ......
Excel 2003
View 2 Replies
View Related
Mar 20, 2009
I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically...
1. Merge & center F & G in the Auto-Sum row with the text "Totals".
2. Change the row height of the Auto-Sum row to 44 pixels.
3. Center the vertical alignment of the Auto-Sum row.
4. Put the thickest border around the Auto-Sum row in cells A-K.
View 14 Replies
View Related
Jun 16, 2013
I have three columns in Excel
Column state "5K", Column B state number in text format, Column C state text.
I would like to auto extract the number in below sequence by sorting "Column B" to Column D format.
How to do it?
Text
Text
Text
Column D
5K
01
B
5K01B-5K03B
[Code] .........
View 1 Replies
View Related
Nov 14, 2006
I am having trouble with using the auto filter, and I suspect it is due to my data format.
I am importing data from an outside source (I have no control on how the data is output). The data in the column I am concerned with contains various formats, such as numbers, letters, and a combo of the two. e.g.:
12345
sample
sam123ple
I have been taking the entire column and formatting it as text to make all of the data consistent. The problem I see comes when I go to use the autofilter, to say custom>begins with "123". The value 12345 does not show up in my filter. However, if I double click the cell 12345, then hit enter, and re-run my autofilter, it shows up.
Does anyone know the reason why this double-click-enter method forces Excel to recognize the value? And is there a way to mass-produce this command for the entire data set? Right now, I'm up to 5000 lines of data, so to do this on each would take a while.
View 9 Replies
View Related
Nov 6, 2006
I am trying to make a column of names appear in upper case (the first letter). I know about Upper(). I cant put that formula in this column because the names are typed there, (it would erase formula). How can I do this as well as have them alphabetically ordered?
View 2 Replies
View Related
May 7, 2014
I have created a workbook with 31 worksheets (they represent each day of the month). I'd like to know how I can auto fill the date in cell A1 across the 31 worksheets without having to type in each day myself. I need the date format to look like this: Wednesday 7th May 2014
In other words, cell A1 in worksheet 1 needs to say Thursday 1st May 2014, cell A1 in worksheet 2 needs to say Friday 2nd May 2014, cell A1 in worksheet 3 needs to say Saturday 4th May 2014 and so on for the remainder of the worksheets for that month.
View 4 Replies
View Related
May 7, 2014
I have created a workbook with 31 worksheets (they represent each day of the month). I'd like to know how I can auto fill the date in cell A1 across the 31 worksheets without having to type in each day myself. I need the date format to look like this: Wednesday 7th May 2014
In other words, cell A1 in worksheet 1 needs to say Thursday 1st May 2014, cell A1 in worksheet 2 needs to say Friday 2nd May 2014, cell A1 in worksheet 3 needs to say Saturday 4th May 2014 and so on for the remainder of the worksheets for that month.
View 6 Replies
View Related
Oct 15, 2009
I have the following list of numbers
0000 0000 0000 0000 0000 0100
0000 0000 0000 0000 0000 0101
0000 0000 0000 0000 0000 0102
and I need to continue the list to
0000 0000 0000 0000 0000 0326
I tried dragging by the grip in the bottom right hand corner but it changes the first didit only. I tried to put it the numbers in the format
100
101
102
and using a custom format number to put in the extra zeros. This worked fine until I used a countif (or it may of been a lookup) statement in my code to look for 0000 0000 0000 0000 0000 0100 and it can't find it as the number is 100. Is there away around this that will save me typing in an awful lot of zeros.
View 2 Replies
View Related
Dec 20, 2012
I've been having a problem lately with Excel automatically converting all of the cells on my spreadsheet from standard number format to dates. I've created several spreadsheets for calculations and all the cells in the number formats that I wanted. It's been saved and actually reopened and used several times. I opened it this morning and now half of numbers are now converted to dates. I've had this happen several times on several different spreadsheets. Why is this happening? Is there a setting somewhere that I need to change?
View 1 Replies
View Related
Dec 29, 2008
I run a macro that imports a table from web page:
[url]
I import only the table with no formatting.
Excel tries to be helpful, and formats some of the cells in scientific notation.
I have tried to format the column to the "Text" format prior to the import with
Columns(2).NumberFormat = "@"
This does not work. If you import the table you can see that rows 1405, 1406, 1407, 1408 all have the same scientific notation value. Their true values should be 0E0, 0E4, 0E8, 0E9 respectively.
There are quite a few other instances in the column when a text value has "E" for the second character that Excel will format on its own.
View 6 Replies
View Related
Apr 6, 2014
System is generating IDs like 1-OCTO33, 1-NOVE44, which on exporting in excel converts to 1-OCT-33 & 1-NOV-44. How can this auto-formatting be restricted in excel?
To test, you can try entering in excel 1-OCTO33, it converts to 1-OCT-33.
I am looking for some setting in excel, which would prevent it on exporting to excel. Other options like changing column format to text or using a limiter ' will not work in this case.
View 1 Replies
View Related
Apr 2, 2007
I have been working on a spreadsheet but I use autoformat for.
For example if the date in the cell matches today's date then it turns the text read and makes it bold.
I was wondering if there was a way to format several cells in a row if a certain criteria was met.
for example
A B C
1 Item Cost Quantity
2 Car £11,520 2
3 Bike £7,500 1
4 Tyres £50 4
Now for example if I set a condition whereas the quatity is greater than 3 then it would highlight the whole row.
So in the example above I would like to to highlight row 4 and Fill colour cells A4,B4 & C4 with a yellow background and Red Text and make the texted bold.
View 14 Replies
View Related
Mar 8, 2013
When i enter a value in an excel sheet cell ,Excel auto detect the data type and format that cell to that data type.
For example if i enter 4/4001, excel read it as date and convert the cell format from general to date and displays 4-Aprl.
However if i precede with " ' " this problem is solved but my question ios that can i permanently disable this auto detect option?
View 4 Replies
View Related
