How to synchronize two files with each other
PerretteQ
Posted messages
28
Status
Membre
-
PHILOU10120 Posted messages 6463 Registration date Status Contributeur Last intervention -
PHILOU10120 Posted messages 6463 Registration date Status Contributeur Last intervention -
Hello,
I would like to make it so that when I make changes in a first file, those changes appear in a second file, where the first three columns are identical starting from line 7 (frozen pane for the header of my table).
I have already tried pasting with a link or even some attempts with VBA.
My problem is the following: It often happens that lines are added to the first table, and I would like them to automatically be added to my second table.
Do I need to check something specific in the paste special or is there a code for VBA?
Thank you in advance :)
I would like to make it so that when I make changes in a first file, those changes appear in a second file, where the first three columns are identical starting from line 7 (frozen pane for the header of my table).
I have already tried pasting with a link or even some attempts with VBA.
My problem is the following: It often happens that lines are added to the first table, and I would like them to automatically be added to my second table.
Do I need to check something specific in the paste special or is there a code for VBA?
Thank you in advance :)
8 réponses
Hello
You can use the following formula in workbook 2 in columns A, B, C.
example column A
=IF([Workbook1]Sheet1!$A$8<>"",[Workbook1]Sheet1!$A$8,"")
Remember to change the names of the workbooks and extend the formula for the same number of rows as in workbook 1, the source workbook
--
Practice makes perfect. - It's when you hit a wall that you see the bricklayer - you always learn from your mistakes.
You can use the following formula in workbook 2 in columns A, B, C.
example column A
=IF([Workbook1]Sheet1!$A$8<>"",[Workbook1]Sheet1!$A$8,"")
Remember to change the names of the workbooks and extend the formula for the same number of rows as in workbook 1, the source workbook
--
Practice makes perfect. - It's when you hit a wall that you see the bricklayer - you always learn from your mistakes.
Hello,
I made an example spreadsheet that I would like to update.
http://www.cjoint.com/data/0BclyLu04JE.htm
In fact, the first column starting from A7 has merged cells.
The source file has exactly the same formatting (I did it on purpose thinking it would make things easier). When I test Philou's formula from post1, it returns #Name in column A.
Is there a solution to my problem? :)
I made an example spreadsheet that I would like to update.
http://www.cjoint.com/data/0BclyLu04JE.htm
In fact, the first column starting from A7 has merged cells.
The source file has exactly the same formatting (I did it on purpose thinking it would make things easier). When I test Philou's formula from post1, it returns #Name in column A.
Is there a solution to my problem? :)
http://www.cjoint.com/data/0Bcn4Tyq4ZH.htm
Here is the attached link with the example.
I am not sending you two files because both have really the same structure, only the parameters behind change.
I will explain my request again to start off on the right foot and to try to make myself understood :)
So, I would like my target file to contain exactly the same information as in my source file for the first 3 columns (which, once entered, do not change). The thing is that in my source file, new rows are regularly added. I would like these rows to be automatically added to my target file, but only for the first 3 columns! Because my two files allow me to summarize information on different parameters.
I still want it to add rows, not just intersperse cells which would consequently shift all the aligned information.
I hope I have been as clear as possible :/
Thank you very much for your help :)
Here is the attached link with the example.
I am not sending you two files because both have really the same structure, only the parameters behind change.
I will explain my request again to start off on the right foot and to try to make myself understood :)
So, I would like my target file to contain exactly the same information as in my source file for the first 3 columns (which, once entered, do not change). The thing is that in my source file, new rows are regularly added. I would like these rows to be automatically added to my target file, but only for the first 3 columns! Because my two files allow me to summarize information on different parameters.
I still want it to add rows, not just intersperse cells which would consequently shift all the aligned information.
I hope I have been as clear as possible :/
Thank you very much for your help :)
Hello
Here are the 2 files
https://www.cjoint.com/?3BcpvmK366D
https://www.cjoint.com/?3BcpvP5LkB7
--
Practice makes perfect. - It's in hard times that you see the mason - you always learn from your mistakes.
Here are the 2 files
https://www.cjoint.com/?3BcpvmK366D
https://www.cjoint.com/?3BcpvP5LkB7
--
Practice makes perfect. - It's in hard times that you see the mason - you always learn from your mistakes.
I thank you for taking the time to show me how to do it, but despite your valuable advice, I can't manage to do it :'(
So instead of continuing to take up your time, I will abandon this idea! Too bad ...
So instead of continuing to take up your time, I will abandon this idea! Too bad ...
Hello
I am attaching the destination file with information to help you
https://www.cjoint.com/?3BdlEjNFNLj
--
Practice makes perfect. - You see the bricklayer when you are up against the wall - you always learn from your mistakes
I am attaching the destination file with information to help you
https://www.cjoint.com/?3BdlEjNFNLj
--
Practice makes perfect. - You see the bricklayer when you are up against the wall - you always learn from your mistakes
Thank you :) but I think the problem comes from the fact that I don't want to manually add rows to my destination workbook.
In fact, it is in the source workbook that rows are added, and I would like those to be automatically added to the destination (or target) workbook. I don't know if this is possible?
In fact, it is in the source workbook that rows are added, and I would like those to be automatically added to the destination (or target) workbook. I don't know if this is possible?
I reiterate my question:
- Is there a way to synchronize two Excel files from my file 1 to my file 2?
- And if in my file 2 it is possible for rows to be automatically added if there are rows manually added in file 1? (and therefore that the number of rows automatically increases in my file 2 if it increases in my file 1)
So Philou, I would like to be able to answer you, but that corresponds to my question :/
- Is there a way to synchronize two Excel files from my file 1 to my file 2?
- And if in my file 2 it is possible for rows to be automatically added if there are rows manually added in file 1? (and therefore that the number of rows automatically increases in my file 2 if it increases in my file 1)
So Philou, I would like to be able to answer you, but that corresponds to my question :/
I haven't created any path to retrieve information from the source file except for the formula you gave me, which is: =IF('[PathWorkbook2]'!A7="" ,"";'[PathWorkbook1]Sheet'!A7) in each cell in columns A, B, and C as in your example
But the rows are not added automatically, and I didn't think it was necessary to put another access path?
But the rows are not added automatically, and I didn't think it was necessary to put another access path?
Hello,
I just saw your link posted at 12:33!
I would like to give it a try, but before I dive in, can you just let me know if adding rows in workbook 1 automatically leads to adding rows in workbook 2? Because in your example, you add data at the end, but for me, it will be rows added in the middle of my table; if so, that would be really great :)
I just saw your link posted at 12:33!
I would like to give it a try, but before I dive in, can you just let me know if adding rows in workbook 1 automatically leads to adding rows in workbook 2? Because in your example, you add data at the end, but for me, it will be rows added in the middle of my table; if so, that would be really great :)
No, no inserted lines
This is not what was requested initially.
"It regularly happens that lines are added to the first table"
So, another solution:
in the destination workbook, in A2
=IF(ISTEXT(OFFSET([workbook1.xlsx]Sheet1!A$1,ROW()-1,0)),OFFSET([workbook1.xlsx]Sheet1!A$1,ROW()-1,0),"")
if the source workbook is called workbook1.xlsx
we drag the handle to column C, then the three to 1000 if we want
Supports insertions
This is not what was requested initially.
"It regularly happens that lines are added to the first table"
So, another solution:
in the destination workbook, in A2
=IF(ISTEXT(OFFSET([workbook1.xlsx]Sheet1!A$1,ROW()-1,0)),OFFSET([workbook1.xlsx]Sheet1!A$1,ROW()-1,0),"")
if the source workbook is called workbook1.xlsx
we drag the handle to column C, then the three to 1000 if we want
Supports insertions
workbook 1 https://www.cjoint.com/c/EBdqVnSlZD1
workbook 2 https://www.cjoint.com/c/EBdqYktDay9
you open both workbooks, you go to the first one and make changes (insertion, addition) and you check the second one.
workbook 2 https://www.cjoint.com/c/EBdqYktDay9
you open both workbooks, you go to the first one and make changes (insertion, addition) and you check the second one.
Hello
Seeing that you also have problems with DjiDji59430
I would like you to check the options in your Excel
1 - Can you check in the menu
Excel 2010
File
Options
Advanced options
Section When calculating this workbook
Update links to other documents
Save external link values
These 2 lines must be checked
2 - Open both workbooks at the same time and in the destination workbook
paste this formula in A9
'=IF(ISTEXT(OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0)),OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0),"")
then remove the prefix before the equal sign and validate
Then copy this one in B9
'=IF(ISTEXT(OFFSET('[source workbook2.xlsx]Sheet1'!$B$1,ROW()-1,0)),OFFSET('[source workbook2.xlsx]Sheet1'!$B$1,ROW()-1,0),"")
also remove the prefix
And finally in C9 paste this one
'=IF(ISTEXT(OFFSET('[source workbook2.xlsx]Sheet1'!$C$1,ROW()-1,0)),OFFSET('[source workbook2.xlsx]Sheet1'!$C$1,ROW()-1,0),"")
The prefix is there to convert the format to text, so the formula does not change when sent to you
The file
https://www.cjoint.com/?3BekhUOetkg
with the formulas in text format
--
Practice makes perfect. - You only see the mason when you're up against the wall - you always learn from your mistakes
Seeing that you also have problems with DjiDji59430
I would like you to check the options in your Excel
1 - Can you check in the menu
Excel 2010
File
Options
Advanced options
Section When calculating this workbook
Update links to other documents
Save external link values
These 2 lines must be checked
2 - Open both workbooks at the same time and in the destination workbook
paste this formula in A9
'=IF(ISTEXT(OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0)),OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0),"")
then remove the prefix before the equal sign and validate
Then copy this one in B9
'=IF(ISTEXT(OFFSET('[source workbook2.xlsx]Sheet1'!$B$1,ROW()-1,0)),OFFSET('[source workbook2.xlsx]Sheet1'!$B$1,ROW()-1,0),"")
also remove the prefix
And finally in C9 paste this one
'=IF(ISTEXT(OFFSET('[source workbook2.xlsx]Sheet1'!$C$1,ROW()-1,0)),OFFSET('[source workbook2.xlsx]Sheet1'!$C$1,ROW()-1,0),"")
The prefix is there to convert the format to text, so the formula does not change when sent to you
The file
https://www.cjoint.com/?3BekhUOetkg
with the formulas in text format
--
Practice makes perfect. - You only see the mason when you're up against the wall - you always learn from your mistakes
Have you tried copying the formula into the destination file
with both workbooks open.
You can display them side by side using the View menu under "Arrange Vertically"
When your two files are in position
select the part of the formula in bold and go to the source workbook with the mouse and click on A1
=IF(ISNUMBER(OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0));OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0);"")
Then select the second part in bold
=IF(ISNUMBER(OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0));OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0);"")
and select the source A1 again and confirm
the formula should display the source value
with both workbooks open.
You can display them side by side using the View menu under "Arrange Vertically"
When your two files are in position
select the part of the formula in bold and go to the source workbook with the mouse and click on A1
=IF(ISNUMBER(OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0));OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0);"")
Then select the second part in bold
=IF(ISNUMBER(OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0));OFFSET('[source workbook2.xlsx]Sheet1'!$A$1,ROW()-1,0);"")
and select the source A1 again and confirm
the formula should display the source value
I would understand if you tell me that I'm not very clear :)