Large Excel file: Suddenly the links are shifted to the cell in the preceding row.

Hans-Heinrich Hartmann 0

I have a large Excel file (8025 lines). (Microsoft® Excel® 2021 MSO (Version 2405 Build 16.0.17628.20006) 64 Bit)

One column (column C) contains names of historical persons; example: Victoria (Vereinigtes Königreich)

To each cell (= historical person) belongs a link to the respective Wikipedia-article; example: https://de.wikipedia.org/wiki/Victoria_(Vereinigtes_K%C3%B6nigreich)

Out of the sudden is something wrong:

The links in two cells (lines 6139 and 6140) have disappeared. (This can be corrected easily; this is not really a problem.)

But:

The links of all the following lines (6141 ….) have shifted by one: The link in line n refers to the person which is listed in line n+1.

How can this happen?

Viorel 117K Reputation points

2024-06-06T10:58:33.04+00:00

How did you check that the file is valid (but is loaded incorrectly)?
Hans-Heinrich Hartmann 0 Reputation points

2024-06-06T12:23:03.6766667+00:00

If I close the file and open it again everthing remains as it is.

Everythin is as it shold be - exept that the links have shifted by one line.
Hrag Hayrabedian 0 Reputation points

2024-06-15T12:25:37.5833333+00:00

I am having this exact issue that started in the last week or 2. On 2 systems. It is very reproducible. If you add rows or columns in quick succession, using repeat last action (ctrl+y) for example, at some point one of the insert row actions won't actually add a row, but the screen will kind of refresh. That's when something somewhere has shifted in the formulas. It can happen to formulas many rows below where you are actually inserting the new rows.

I noticed it at some point when rows of values on my file suddenly didn't make sense, and I noticed that the row of data was now linking to another row than it was initially set at

I am on Excel Version 2405 Build 16.0.17628.20006

2 answers

Jiajing Hua-MFST 9,750 Reputation points Microsoft Vendor

2024-06-06T08:33:13.9666667+00:00

Hi @Hans-Heinrich Hartmann

This situation is indeed strange. I suggest you wait and see if it will recur.

Currently, it's recommended to back up this workbook first.

Then select the cells from 6141 and the end, go to formula bar, modify the cell "n+1" to "n", then directly press Ctrl and Enter to apply the formula.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Hans-Heinrich Hartmann 0 Reputation points

2024-06-06T10:07:52.2333333+00:00

I made a bach up of the file - no problem.

But I did not understand what I should do next.

The file has a length of 8 MB and does not contain any secrets. If you wish I can send you the file by email.

Regards

Hans

Jiajing Hua-MFST 9,750 Reputation points Microsoft Vendor

2024-06-07T05:42:43.96+00:00

Hi @Hans-Heinrich Hartmann

Thanks for your response.

According to your description, does this issue can be reproduced after reopening this file?

If so, you may save it as .xml file, and try to upload here.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Hans-Heinrich Hartmann 0 Reputation points

2024-06-07T21:56:58.41+00:00

My last response was deleted due to “a violation of our Code of Conduct”. I do not know what was wrong. The only thing which was special is: I inserted a link to my “Google Drive” so that you could download the file.

This was the message which I tried to send to you:

I could store the file as .xml file but I could not upload the file. I got an error-message.

There is another possibility: I have stored the file in my “Google Drive”. This is the link so that you can download the file:

(I think that my message was deleted due to the link. I think it would be helpful if you can look at the original file. It is a hobby-file. There are no secrets in the file. The upload of the .xml did not work. What other possibilities do we have? Email?)

Open the file and go to "Stammbaum", Column "C".

Click at any cell (= person) and you will get the respective Wikipedia-article.

But beginning with line 6141 the links are shifted by one.

In the meantime, I have a theory of what might have happened:

The file contains historical persons.

The persons are sorted according their date of birth.

If I want to insert a new person I have to insert a line at the right place.

Due to the file length the “insert line” operation takes about one minute.

In the first step Excel moves the names of the persons to the following line. (?)

At the end of the process the links are moved to the following line. (?)

Perhaps the “insert line” operation was somehow interrupted before the links had been moved to the following line.

Shortly before I remarked the link-problem there was an inconsistency in the formulas of column A. But I could repair this very easily. (“Content of the cell in line n” is equal the “contend of the cell in line n-1” +1)

Hans-Heinrich Hartmann 0 Reputation points

2024-06-10T07:46:45.5833333+00:00

Despite the fact that the links should not be shifted in such a faulty way:

I found a procedure to repair the file. Now all the links are at the right place again.

This is the procedure:

Insert an additional (auxiliary) column besides the column with the shifted links.

Copy the column with the faulty links into the auxiliary column. The two columns contain now the identical data.

Use the function “insert content” (German: Inhalte einfügen) to insert the “values” (Werte) and formats (Formate) so that they match to the links in the respective cells.

Delete the auxiliary column.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Barbara Verplancken 0 Reputation points

2024-06-07T13:32:00.3533333+00:00

The exact same issue has just happened with a large Excel file of mine. In one of the tabs with about 700 lines each link has shifted to the cell above it -- so ALL of my links are now incorrect.
Please sign in to rate this answer.
Hans-Heinrich Hartmann 0 Reputation points

2024-06-07T22:07:02.25+00:00

Hi Barbara

Did you do an “insert line” operation just before the fault occurred? … or “delete line”?

Hans

Hans-Heinrich Hartmann 0 Reputation points

2024-06-10T07:48:41.3133333+00:00

Despite the fact that the links should not be shifted in such a faulty way:

I found a procedure to repair the file. Now all the links are at the right place again.

This is the procedure:

Insert an additional (auxiliary) column besides the column with the shifted links.

Copy the column with the faulty links into the auxiliary column. The two columns contain now the identical data.

Use the function “insert content” (German: Inhalte einfügen) to insert the “values” (Werte) and "formats" (Formate) so that they match to the links in the respective cells.

Delete the auxiliary column.

Barbara Verplancken 0 Reputation points

2024-06-10T15:58:15.74+00:00

Hello Hans,

Thanks very much for your response - I appreciate it!

Indeed this happened to me when inserting a line (which I've been doing really frequently for years with no problems). And today, after inserting more lines, the links have shifted a further 4 lines (only those below where I have inserted).

That looks like a nice workaround you found to fix it. Unfortunately in my table the text typed into the cells with links do not equal the URLs that they were linking to. My cells contain the name of a file, for example "20241006-Filename.pdf" and the link is a URL to that file in Dropbox.

I'm happy your problem is sorted though :) I can't imagine having over 6000 links to sort through!

As for me, I think I'm just going to stop linking in this large Excel file. It was useful, but not crucial.

Again thanks so much!

Barbara

Hans-Heinrich Hartmann 0 Reputation points

2024-06-11T21:23:16.8533333+00:00

Hello Barbara

I must admit that I do not understand why you cannot repair your Excel-table.

As I understand your faulty table is like this.

                               Visible content of the cell     Link which is connected to the cell

Line n                    Filename_001                               Link to 20241006-Filename.pdf

Line n+1              Filename_002                                Link to xxx

Line n+2              Filename_003                                 Link to xxx

Line n+3              Filename_004                                 Link to xxx

Line n+4              20241006-Filename.pdf              Link to xxx

In words:

If you click on the visible content (text) in line n (“Filename_001.pdf”) then the link leads to the file “20241006-Filename.pdf” which is the visible content of line n+4.

Is it like this?

Why should the following not be possible:

You copy (CTRL C) the cell of line n+4. (20241006-Filename.pdf ; Link to xxx)

Then you go the cell in line n.

Then you use the function “insert content” (German: Inhalte einfügen) and you replace the visible content of the cell (Filename_001) by the text which you took from line n+4 (20241006-Filename.pdf).

Then you have:

Line n                   20241006-Filename.pdf              Link to 20241006-Filename.pdf

… which is correct.

In my message of 10 June 2024, I proposed an “auxiliary column”. Then the source (auxiliary column) and the destination (normal column) are separated. This makes the data transfer a bit clearer. In my case I had to transfer both the “visible content of the cell” and the “format” (background colour of the cell).

Regards

Hans
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Large Excel file: Suddenly the links are shifted to the cell in the preceding row.

2 answers

Your answer