up vote 8 down vote favorite
1
share [g+] share [fb]

I have a spreadsheet which has data linked in from other tabs on the same spreadsheet. Each of the source tabs has a column formatted as a number with no decimal. The tab they're linked to has the same exact column formatting. However, the formatting keeps reverting back to a date format. Why won't this keep the formatting I originally specified for the entire column?

link|improve this question
What do you mean by "reverting back"? When does it do such? If you change the value? Copy the cell? Edit a formula? – Howard Jun 3 '11 at 5:15
I will enter data into one of my tabs. That cell is linked to my "roll-up" tab. The cells in both tabs are formatted as a number. The data looks good. I save the file. Then, I'll update a row several lines down in that same tab. When it links to the "roll-up" tab, it is now showing as a date. I've had this happen on another file that has no linked data. I change an entire column to be formatted as a date (formatted like 3/14/11). It seems that every once in a while... when I'm entering a date, the format will spontaneously change to show a format like 3/14/2011. – Sarah Jun 3 '11 at 5:49
Which version of Excel are you using? On what operating system? What are your language / locale setting? In particular, which symbol is used for decimal point, thousands separator, date separator? – Jonas Heidelberg Jun 3 '11 at 11:50
in the "roll-up" is where the format is not being maintained correct? or both? When you check the format properties of the offending cells what type format do they show? we know there are no macros at work? – datatoo Jun 4 '11 at 16:39
by "linked in from other tabs" do you mean using a reference such as =Sheet2!D6 or using a VLookup() or similar function, or...? – StevenV Aug 4 '11 at 14:17
feedback

migrated from stackoverflow.com Jun 3 '11 at 12:47

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote

saw this issue way back when -

Whho hoo! I called support and thy solved the problem

Here is the solution. By default whenever you open a database in excel it imports the database in GENERAL format. Once it imports the database in that default GENERAL format you CANNOT format it to make it display the numbers correctly no matter what.

You MUST import the database in the correct format... you can't change it later. The card numbers will remain messed up.

So the VERY simple solution is... when you click OPEN and start importing your database... the very last option... you will see that GENERAL is checkmarked by default and it will by default import your database in GENERAL format. You must check TEXT instead. It will then import your database via TEXT format... and then it displays 16+ digits just fine with no problem.

So it's not that excel only supports 15 digits... it's that you were simply opening the database in the WRONG type of format... once you do... you cannot format the column and change it to TEXT once you already imported your database... then it's already too late.

Geez.... that was simple solution. Glad it works.

Source

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard