Cell Offset Issue
Thomasbisch4485
Posted messages
3
Status
Membre
-
JvDo Posted messages 1924 Registration date Status Membre Last intervention -
JvDo Posted messages 1924 Registration date Status Membre Last intervention -
Hello,
I have an Excel table with a number of information (building permit number, name, address, phone number....).
In a second table, I refer to certain data from the first table that I need (for example, the permit number) using a formula like =[REGISTRE DOSSIERS URBANISME.xlsx]urba'!F438. I complete the row with other information (tax amounts, payment dates...).
The problem is that when I insert a row in the middle of my first table, the values in my second table that are linked shift down logically. However, the other information (tax amounts, payment dates...) does not shift and is no longer next to the correct permit number.
Is there a solution to keep the cells of a row together so that the shift occurs for all the cells in the row?
Thank you for your responses.
Thomas
I have an Excel table with a number of information (building permit number, name, address, phone number....).
In a second table, I refer to certain data from the first table that I need (for example, the permit number) using a formula like =[REGISTRE DOSSIERS URBANISME.xlsx]urba'!F438. I complete the row with other information (tax amounts, payment dates...).
The problem is that when I insert a row in the middle of my first table, the values in my second table that are linked shift down logically. However, the other information (tax amounts, payment dates...) does not shift and is no longer next to the correct permit number.
Is there a solution to keep the cells of a row together so that the shift occurs for all the cells in the row?
Thank you for your responses.
Thomas
6 réponses
Hello
you will need to revisit your explanations, or provide a file, because if you indeed insert a complete row into the sheet, all the formulas in the shifted rows are also shifted.
This problem can only occur if you insert a single cell in a column.
Looking forward to hearing from you
--
To err is human, to persist is diabolical
you will need to revisit your explanations, or provide a file, because if you indeed insert a complete row into the sheet, all the formulas in the shifted rows are also shifted.
This problem can only occur if you insert a single cell in a column.
Looking forward to hearing from you
--
To err is human, to persist is diabolical
Attached is the table.
The data in columns A to K refers to a first table. The data from L to W is added to this table.
For example, if I insert a new row at the beginning of my first table, the data in columns A and K shifts down one row in my second table, but not the data in columns L to W since they are not linked to the first table.
I’m not sure if I’ve been clearer.
The data in columns A to K refers to a first table. The data from L to W is added to this table.
For example, if I insert a new row at the beginning of my first table, the data in columns A and K shifts down one row in my second table, but not the data in columns L to W since they are not linked to the first table.
I’m not sure if I’ve been clearer.
I don't have the attachment, but when I replicate your situation, that is, when I insert a row in the source column, the consequence in the copy column is different:
The offset does NOT replicate in the second column. However, the formula is modified from the row affected by the insertion to match its previous value.
For example, if I insert a row in A2 (I get a new A2 and the old one becomes A3), then in the copy table, the data remains the same since the formula from the second row is automatically adjusted. Thus, the formula in A2 on the copy sheet changes from =Feuilsource!A2 to =Feuilsource!A3, and displays the same value.
P.S.: Deleting the inserted row does not cause any issue, the auto-adjustment happens as well in the other direction. However, deleting a row that wasn't inserted (which already existed when you set the copy formulas) causes a problem because the affected copy cell displays #REF!
You can use the site https://www.cjoint.com/ to share an Excel file that you have taken care to anonymize.
See you!
The offset does NOT replicate in the second column. However, the formula is modified from the row affected by the insertion to match its previous value.
For example, if I insert a row in A2 (I get a new A2 and the old one becomes A3), then in the copy table, the data remains the same since the formula from the second row is automatically adjusted. Thus, the formula in A2 on the copy sheet changes from =Feuilsource!A2 to =Feuilsource!A3, and displays the same value.
P.S.: Deleting the inserted row does not cause any issue, the auto-adjustment happens as well in the other direction. However, deleting a row that wasn't inserted (which already existed when you set the copy formulas) causes a problem because the affected copy cell displays #REF!
You can use the site https://www.cjoint.com/ to share an Excel file that you have taken care to anonymize.
See you!
http://www.cjoint.com/data3/3AipSrn578l.htm
Above is the link to my file.
For example, line 4 of my source file corresponds to the permit number PC04. In my second table in the link, column A4 will therefore have the permit number PC04. In this same table, PC04 will correspond to the sanitation file number 12/2014 (a number that I added manually in this second table).
The problem arises if I add a line in my source table before line 4. In my second table, the permit number PC04 will then normally shift to line 5. However, my sanitation file number 12/2014 will remain on line 4 and will no longer be next to the correct permit.
In relation to my table, the entire "urbanism part" shifts if I add a line in the source file, but the "sanitation part" does not shift as it is not linked to the first file.
I hope this is clearer.
See you later.
Above is the link to my file.
For example, line 4 of my source file corresponds to the permit number PC04. In my second table in the link, column A4 will therefore have the permit number PC04. In this same table, PC04 will correspond to the sanitation file number 12/2014 (a number that I added manually in this second table).
The problem arises if I add a line in my source table before line 4. In my second table, the permit number PC04 will then normally shift to line 5. However, my sanitation file number 12/2014 will remain on line 4 and will no longer be next to the correct permit.
In relation to my table, the entire "urbanism part" shifts if I add a line in the source file, but the "sanitation part" does not shift as it is not linked to the first file.
I hope this is clearer.
See you later.
Re
I will leave you with more competent colleagues, but I don't believe there is a solution to your problem, except for not modifying the sorting of the original workbook. I don't see how to handle row insertion in workbook X when in workbook Y half of the table is manually entered.
The best option would be to enter everything in a single workbook and send back to the second one the part that interests you.
Good luck
Best regards
To err is human, to persist is diabolical.
I will leave you with more competent colleagues, but I don't believe there is a solution to your problem, except for not modifying the sorting of the original workbook. I don't see how to handle row insertion in workbook X when in workbook Y half of the table is manually entered.
The best option would be to enter everything in a single workbook and send back to the second one the part that interests you.
Good luck
Best regards
To err is human, to persist is diabolical.
Hello,
Your first column is an index column that must remain fixed, without duplicates and should match those from the source file.
Then you use VLOOKUP() to retrieve the data associated with the building permit from column A.
Your added data will thus remain linked to their building permit.
Best regards
Your first column is an index column that must remain fixed, without duplicates and should match those from the source file.
Then you use VLOOKUP() to retrieve the data associated with the building permit from column A.
Your added data will thus remain linked to their building permit.
Best regards