How to synchronize two files with each other

PerretteQ Posted messages 28 Status Member -
PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention - 4 Feb 2015 at 12:06

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 :)

8 answers

Answer 1 / 8
PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835

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.
1
1. PerretteQ Posted messages 28 Status Member
  
  Thanks Phil, I'll try right away, just a small clarification: The first workbook in your formula corresponds to the 2 in my case and the 2nd workbook then corresponds to the first one?
  I would understand if you tell me that I'm not very clear :)
  
  0
Answer 2 / 8
PerretteQ Posted messages 28 Status Member

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? :)
0
1. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835
  
  The problem comes from the merged cells, can you remove this formatting?
  
  0
2. PerretteQ Posted messages 28 Status Member
  
  Yes, I can remove it (I tested it while waiting for your response :) )
  But now, this technique doesn't seem to work if I add lines in my source spreadsheet (I just tested it as well). Am I doing something wrong?
  
  0
3. PerretteQ Posted messages 28 Status Member
  
  Moreover, it does not give me back all the values from my source table.
  
  0
4. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835
  
  Can you put an excerpt from your 2 files on Cjoint.com
  To help us solve your issue
  copy and paste the link obtained in your response here on this site
  
  0
5. PerretteQ Posted messages 28 Status Member
  
  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 :)
  
  0
Answer 3 / 8
PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835

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.
0
Answer 4 / 8
PerretteQ Posted messages 28 Status Member

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 ...
0
1. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835
  
  No, we must not give up; it's by forging that one becomes a blacksmith.
  
  0
2. PerretteQ Posted messages 28 Status Member
  
  Hello,
  Unfortunately, no matter how closely I follow the example binders you provided, when I add a row to my source binder, it doesn't appear in my target binder!
  When I put in my formulas and everything, there's no problem; Excel doesn't signal any errors, so I don't understand :/
  
  0
Answer 5 / 8
PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835

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
0
1. PerretteQ Posted messages 28 Status Member
  
  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?
  
  0
2. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835 > PerretteQ Posted messages 28 Status Member
  
  And how are they included in the destination workbook, do you pull the formulas?
  
  0
3. PerretteQ Posted messages 28 Status Member
  
  There is my whole question...
  
  0
4. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835
  
  And what's the answer?
  
  0
5. PerretteQ Posted messages 28 Status Member
  
  So either I don't understand your question, or I think I came to this forum to get that answer :)
  
  0
Answer 6 / 8
PerretteQ Posted messages 28 Status Member

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 :/
0
1. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835
  
  I showed you the method, now how is your destination file structured to retrieve information from the source file?
  That's the question, what's blocking?
  If you don't have access, it will be difficult to link the cells.
  
  0
2. PerretteQ Posted messages 28 Status Member
  
  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?
  
  0
  1. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835 > PerretteQ Posted messages 28 Status Member
    
    The information lines from the source file are automatically transferred to the destination file with the formulas; it is enough to have at least the same number of lines in the source and the destination.
    
    0
3. DjiDji59430 Posted messages 4278 Registration date Status Member Last intervention 717
  
  Nothing
  
  0
Answer 7 / 8
DjiDji59430 Posted messages 4278 Registration date Status Member Last intervention 717

And my solution, does it not work?
0
1. PerretteQ Posted messages 28 Status Member
  
  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 :)
  
  0
2. DjiDji59430 Posted messages 4278 Registration date Status Member Last intervention 717 > PerretteQ Posted messages 28 Status Member
  
  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
  
  0
3. PerretteQ Posted messages 28 Status Member
  
  It regularly happens that lines are added to the first table, and I would therefore like them to be automatically added to my second one.
  :)
  
  0
4. PerretteQ Posted messages 28 Status Member
  
  Is it normal that nothing is showing up in my boxes? :(
  
  0
5. DjiDji59430 Posted messages 4278 Registration date Status Member Last intervention 717 > PerretteQ Posted messages 28 Status Member
  
  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.
  
  0
Answer 8 / 8
PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835

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
0
1. PerretteQ Posted messages 28 Status Member
  
  Hello :)
  The options you specified are checked.
  However, the problem remains the same :(
  No error message from Excel and the formula is correctly displayed in the formula bar but nothing in the cell!
  
  0
  1. PHILOU10120 Posted messages 6463 Registration date Status Contributor Last intervention 835 > PerretteQ Posted messages 28 Status Member
    
    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
    
    0
2. PerretteQ Posted messages 28 Status Member
  
  Moreover, I have a cell where I made sure to count the number of cells with a value in my column B, and it correctly shows 424 (which is the right number I need to obtain).
  
  0
  1. DjiDji59430 Posted messages 4278 Registration date Status Member Last intervention 717 > PerretteQ Posted messages 28 Status Member
    
    Hello,
    
    Could you send a piece of your work binder? (the one that has the issue)
    
    0
  2. PerretteQ Posted messages 28 Status Member > DjiDji59430 Posted messages 4278 Registration date Status Member Last intervention
    
    Sorry, I can't share my work folder with you; it's confidential data :/
    
    0

How to synchronize two files with each other

8 answers

Antivirus problem

Wifi connection issue

Connecting a sony handycam video 8 ccd f555e to an hd tv

Hello sir

Incompatible display driver

Sensitive sites blocking tool

Ink absorber

Phone battery life

Pin code (windows hello)

Android smartphone hacked