Microsoft Excel - Excel 2007 keeps changing my formatting to date

First, I apologize for the length and detail, but I know it’s hard to answer a vague question.  I have created a few spreadsheets to help me do my communications job since we have over 46,000 telephone numbers.  It is a very basic spreadsheet (mostly) with about 5 lines of VBA programming (which works flawlessly – which I obtained from this site).  I also figured out (in a novice way, but it works) how to display the Julian date with the fiscal and calendar year that auto-updates, and added a drop down list (with VLOOKUP assistance) of about 55 sites with their communications purchase order numbers, which works flawlessly.  I downloaded the CSV file from our communications database, saved it as a .XLSM file and added it as a tab, along with a couple of other tabs which are purely for reference. 

 

Process:  I enter a telephone number in a cell with hyphens, periods, spaces etc, and the VBA program just stripes the pure integers out of the string returning the 10 digit number without spaces, hyphens etc.  Pretty much the rest of the spreadsheet are cells referencing that 10 digit number and returning values from various tabs in the workbook with information such as the address, customer name, account number, service provider and phone number, total costs, billing telephone number etc., all through VLOOKUP. I set the formatting appropriately for zip code, numbers, dates, general etc., saved the form and it worked fine for a while, and then one day the formatting changed to DATE on the spreadsheet.  I fixed the formatting, and a few weeks later it happened again, and again.  Since this originally started I have upgraded PC’s, upgraded from Windows XP Pro to Windows 7, had MS Office wiped and reinstalled by our IT team (multiple times) and the problem keeps coming back.  I have even right clicked and set the default NORMAL format default to GENERAL, set the entire spreadsheet to NORMAL, but within a few weeks the cells revert back to a DATE format.  I’ve tried everything short of prayer that I could think of, but I’m starting to think I may need some divine intervention.  Any ideas what is going on or how to fix it (permanently)?

Troy,

Is this a seemingly random occurance when it changes or are you bringing in data from a new CSV file when it happens?
It is strange that it would effect formatting on a grand scale like that without reason.  Which particular set of data is it changing the 10 digit number? or all of it?

If this is a spread sheet that you use as more of a lookup device maybe you could 'lock the range of cells that keep changing and that might prevent them from changing.  but leave the rest of the sheet unlocked for your work purpose.

Don

Thank you Don,
 

My Excel crashes on occasion, but if that is the cause of the formatting changes, it doesn’t do it all the time.  I’ll reformat all of the cells and put a lock on every other cell.  As long as I can copy the information and paste it into an e-mail, that should be all I need.  Now that you mentioned this is a possible solution, I feel kind of silly overlooking such a simple answer.

For all intents and purposes, this is just a 5Mb reference/lookup spreadsheet, and I almost never save changes when I close the spreadsheet unless I’ve added some contact information for the vendor POC list.  The cell where I enter the phone number for lookup or the cell where I enter the District name for the purchase order number are the only cells that I change. 

 

One quick question while I’m talking about this spreadsheet…one of the tabs is a master list of almost 20,000 phone numbers, some 10 digit, 7 digit, 4 digit and 3 digit and some voice circuit ID’s, such as Y2430744 or ZKHM8691MB.  As long as I have the column sorted in lowest to highest and use the FALSE value to return only exact matches using VLOOKUP, any number in the list should be a valid lookup, no matter the length – as long as it is sorted lowest to highest value.  Is that correct?  The circuit ID’s with letters are at the very bottom, so the lookup shouldn’t reach that far, plus the VBA strips non-integers anyway.

Troy,

Thank you and yes I learned from Wally the hard way the the Vlookup does not like data the is not sorted, I used to work for the phone company and we had one exchange 10k all listed in 4 zones and broken out by number on one sheet and by zone and calbe and building on the other.  so I simply used CTRL+F - find when I looked for circut ID or Number or building or cable and did not even try to use a vlookup then I did not have to worrk about sort order. 

I am pretty sure you can lock the sheet and still do the "find"

Don

Don,

Having worked for a phone company, you can understand the hassle of keeping up with a lot of numbers. Between POTS lines and PRI’s, we have about 46,000 numbers, plus about 28,000 wireless devices. It makes for big spreadsheets. And thanks to President Bush forcing us to start our enterprise IT solution about 4 years before it should have been ready, we’re doing what we can to make things work, thus, why a communications person is building his own spreadsheets. :0)

I use the VLOOKUP because on top of the 46,000 numbers, I have married other spreadsheets with agency hierarchy codes, vendor telephone numbers, PIC codes etc., plus I was working on moving many of the lines to a new LD provider, and with the number of lines I was handling, it was easier to arrange the cells where I could put in the number for lookup and have the results consolidated so I could easily copy/paste addresses, AHC’s and so forth for the LD carrier.

And thanks to all of you wizards here, I bought an Excel VBA Programming for Dummies book this weekend…although until I was a few pages in it didn’t mention it was for 2000-2003, not 2007 (which I have now).