Disappearing data

I am completely new to spreadsheets only ever making one very basic one which just has columns of numbers.

I have been struggling for many months with trying to create a Library of books which has many columns of information. My first problem was with ISBN numbers which my friend sorted out but I thought the problem was back again because new data that I was putting in kept disappearing sporadically, so I deleted the ISBN column completely thinking that would sorted it out. Yesterday, I finally finished my Library of over 3,000 books. This morning however data is missing again.

The columns with the problem have headings of all letters. The actual columns have data such as 1p or h9 and sometimes both. The p and h are always used but the numbers might be different. I hope this makes sense. Is there something I should be doing please?

Thank you for replying. Spreadsheets are completely foreign to me, so sorry I cannot answer your questions.

I tried to copy and past a bit of my spreadsheet, but even that is beyond my knowledge. So I have attached a sample - the details of which are probably not correct because I was in a hurry.

Ooops....I cannot see any way of attaching it. I clicked advance but when I tried to open 'attach' it showed only a skinny little box that I cannot do anything with

I will try to add the attachment to this

It is so hard to tell what is missing without going through all of the books again for the fifth time. I think the ones I am having a problem with now seem to be columns J through O. This morning after just looking at the first hundred books that is where the data is missing. Seems to be column O mostly, but the other ones as well. I will go through some more now and make notes then get back to you

I always save it when I close it but as of yesterday, I am now saving every once in a while during entering data as well

Okay, so far most of the missing data seems to be in column O (bth dr) but it is sporadic. One missing in column K (bag) and in the WHERE column one 'bag6' is missing, but that one could be my error. I will continue looking

Not that I know what I am doing, but I just right clicked, then Format Cells on columns I through O.
On the Number tab/ General, the Sample in Column I (WHERE) shows bth dr and Column O (bth dr) shows 1p. All of the others columns between show the Sample empty. Does this mean anything?

Oh wait. I see what is happening. The sample is showing what is in the first row of that column. Darn, I thought I found the culprit

Yes, I saw that but have no idea what it means. If this is the cause, what can I do about it please?

Originally Posted by oeldere

Please Login or Register  to view this content.

No this is NOT the cause, since there is no VBA code in your file..

Oh my...sorry, I read that thinking it should have a VGA code. I did a search about it and found out how to check if there was one on my ss and it is empty.

Originally Posted by xladept

Do you have a touch pad and a mouse? The row is still there with only sporadic fields missing? My touch pad bedevils me when I'm careless.

I have a touch pad, no mouse and yes the row is still there with sporadic fields missing. My touch pad and I get along very well, so that would not be the problem.

I put put a '?' in each of the missing fields yesterday in the first 500 rows to see if there would be further changes. I had to turn my laptop off last night because OH insisted (a worrier!). This morning I had a look through the first 500. There were three more empty fields this morning, two in Column O and one in Column N and they were not ones that I put a '?' in yesterday, so they are new.

Originally Posted by TMS

Really struggling with what might be happening here. Excel doesn't generally randomly lose data that has been entered, regardless of whether or not it is a specific column. Suspect it must be something you are doing.

Thoughts:

1. Formatting, for example, both font and background colour set to white.
2. Formatting, for example, numeric format interpreting your input wrongly and blanking it
3. Conditional Formatting
4. VBA deleting entries ... but why? And there is no VBA in the example you uploaded.
5. Escaping out of a cell rather than using Enter or Tab
6. User error ... maybe losing track of which cells have been entered (apologies, no insult intended, just exploring possibilities)

1. That is not it. I can immediately fill in data which shows. Also, I have every-other row a different colour - grey and white and the font is black. Sorry, I did not do that in the sample. I am pretty sure I did not miss something else in it though.

2&3. I am not sure where to check these. In each row I did randomly check something in each column, rt clicked then Format Cells. All fields (? sorry if I get the terminology wrong) show the same: Number tab / General / Sample as what shows in the field. As for Conditional Formatting, could what I mentioned above having alternative rows grey and white be this? It is a long time ago that I started this ss, so I cannot remember how I went about that.

4. If I am correct with the way I went about it, I did check my ss and the VBA is blank

5. I am not sure what you mean by 'escaping' out of a tab. I do use Enter and Tab but very occasionally will arrow down after entering data.

6. Yes, I too would think it is something I am doing. This is another foreign language for me. I wonder why it is only in certain columns unless whole rows are vanishing? I will keep my eye out for that.

Originally Posted by oeldere

Please reply (point to point) to the offered solution in #23 by TMS.

You did, so please ignore the above message.



1) CF is Start (1 tab) => conditional format

2) What is ss?

1. I am not sure what you mean, sorry

2. ss = spreadsheet

Originally Posted by TMS

If it's a list of books, is the information sensitive? Could you upload the actual workbook rather than a sample?

Yor profile says you are using 2010 and the sample file was a .xlsx file which is NOT macro enabled. If your workbook were macro enabled, it would be a .xlsm or .xlsb file extension

Do you have any merged cells?

Is the workbook shared? (Regardless of whether or not anyone else uses it)

- I do not know what Macro enabled is, but I do have Excel 2010 and the extensions on both are .xlsx
- Merged cells? I do not know how to do that, so I would imagine there are none.
- I am not sure this is considered sharing, but the library was started on my old HP which died. The ss was on a backup and is now on our other old laptop - I am working on it from a shortcut on that. Both laptops used the one copy of MS Office 2010 but they were not linked.

I will try to upload the ss which would definitely give you a better idea of what I am doing wrong.

I am now on my Mac, so I will get back to you as soon as I can tear my PC away from other half.

A friend came by, backed it up and deleted many here and there to make it smaller to upload.

Books in Kitchen should not show where and the few books at the end are out of order on purpose.

I hope this will be more than sufficient to give you an insight to my madness.

Just so you know, they are not my personal books....

I would be happy to try changing the CF, but could you please talk me through it so I don't make any major mistakes?

Originally Posted by newdoverman

Is this file being saved to a network drive and not your local computer?

If this on a network, there could be something wrong with the backup procedures that affect the folder where you save your data to and this will take the IT department (if you have one) to sort out.

You could do a test by creating a small file where you enter the dates and times in order. Save the file to the exact location where you save your troublesome file. If this file loses data over several days, you will have located a possible source of the problem.

The IT department where I used to work had a new employee who instead of backing up data was restoring data from previous backups so we were losing data at a fantastic rate.

You could also try saving the file to a separate location under a different name (in addition to saving the file with its usual name and location) when you are finished data entry. If data is missing from the main file, check the copy to see if the same data is missing.

Not shared. Please see #30

Originally Posted by newdoverman

I did not mention sharing. The file started on one laptop and is now on another accessed by a shortcut. I still sounds like the file is being worked on from one computer but stored on another and is accessed via a shortcut. The actual physical setup is not clear. Is only 1 computer involved or is one computer accessing files from another computer "The ss was on a backup and is now on our other old laptop - I am working on it from a shortcut on that. Both laptops used the one copy of MS Office 2010 but they were not linked." If not linked via a network how is the file being accessed? If the file is physically located on the computer accessing the file that is one thing but that is not at all clear.

I thought 'network' would be the same, sorry. I guess it is confusing with the way I tried to explain it.
The file was started on one laptop which is dead so I am using another laptop. Now the ss is only on that laptop and the shortcut is on that PC's desktop and no other PC is involved. The backup is on the external hard drive.
Is that better?

I will go through what you posted tomorrow newdoverman as it is PM for me and I am always busy then.

I have a question though. Could my Excel be corrupt??? Is there any way you gurus could copy and paste my ss to your Excel to see if any changes occur after a few days? Would that tell us what wrong?

Originally Posted by newdoverman

If you have other Excel files, inspect them for changes being made that are unexplained. If there are changes, something is wrong but what, is the question. Copying and pasting won't solve anything. The original file posted can be worked with to see if there is something out of the ordinary. This could possibly be a corrupt file that you are dealing with especially if other Excel files behave properly. If other Excel files behave properly, I doubt that Excel is corrupted.

Post a copy of your entire original file then we can work with it to see if anything out of the ordinary takes place.

I only had one other file which had simple sums but that vanished with my dead PC. I might have it backed up, I will look.
As for the original ss, my friend who owns the books has that on his external drive and is travelling - I cannot contact him.
I will work on creating another couple of files over the weekend to see what happens though and will get back to you.

If, as you say, it might be a corrupted file, would it not be possible for me to copy and paste a block of columns or rows at a time - or the entire column/row maybe without the heading - onto a new ss?

Originally Posted by newdoverman

I think that you should be able to copy your data into a new workbook. By this I mean a completely new file and not just a worksheet within the same file that is giving you problems. Then save under a new name.

Thanks. I have put it on a new workbook. All looks calm at the moment. The only thing that has changed making a new one is that the Headings now have drop down arrows which I did not have originally and row 2 under the heading was much higher than before so I changed the height to match the others. I am now going to back up and also finally make my Win 10 bootable disk, etc.

I will get back to you to let you know if the worksheet is stable over the weekend.

Well, all is stable. Fingers x'd it will stay that way.

Thank you all so much for your help in this. It really has driven me crazy for years starting from the ISBN problem then on to the vanishings! I know I should take excel lessons, but I honestly have not needed to use it since my first MS Office which I think was 2005. Having said that, I am now going to try to help someone with his extensive music library who wants a similar ss because he too has them dotted all over his house. I think I will have to add columns like 'bath tub' though : D

Originally Posted by TMS

Forgot to say, I bought a software tool called All My Books which is specifically designed to create libraries of books using ISBN numbers. It can automatically pull the information from various ISBN libraries. It wasn't that expensive and I decided it was easier than doing it all manually. Not tested in anger yet but it looks good.

Thank you for that TMS. My first attempt in sorting out his books was with iBookshelf which I had on my mobile and was only about $2(?). You could scan the barcode and it worked well if you did them a couple at a time. I am going back a few years, but I think the problem I had was that my friend still wanted his collection on Excel and I think I downloaded a cvs file (?) to it, but problems started with the ISBN number. If I am correct it had to do with scientific notation? My friend in the States gave me the solution for that but I stopped using iBookshelf because I did not have a lot of space on my mob and could not get any answers from them re any problems that would occur. I then joined Goodreads online and have an app on my mobile which is fantastic and it is free. I still had the same ISBN problem with that on Excel though. Since I did not use Excel often enough to remember what to do, I probably made a mess of things. Maybe my goofs made a mess of the file and it rebelled!

If you have not seen it, join Goodreads and have a look around...