View original (French)

Sync all sheets of an Excel file

ventatto -  
ventatto10 Posted messages 3 Status Membre -
Hello,

I would like to know how to modify all the sheets of an Excel file by only touching the first one. Let me explain: in sheet 1, I add a row in which I only modify columns A-B-C (so cells A12-B12-C12 for example). I would like this row to be created at the same time on all my other sheets, while the data in columns D to Z remains fixed and the data in columns A-B-C is synchronized every time I change it in sheet 1.

Is there a way, a function, something... faster than copy-paste?

Thank you in advance for your responses

Configuration: Macintosh / Safari 10.1

7 réponses

Réponse 1 / 7
DjiDji59430 Posted messages 4277 Registration date   Status Membre Last intervention   716
 
Hello,

You are copying/pasting with column links A B C in all the sheets that interest you.
Best regards
0
Réponse 2 / 7
ventatto
 
The problem is that I have a summary table that averages (on sheet 1) the data from all my other tables (with the same types of columns, on sheets 2-3-4-...-10). These are actually statistics for a sports team. Columns A-B-C concern the number, name, and surname of the players. If I need to add a player at some point during the year (in Excel terms, insert a row), I would like it to be added to all the sheets at the same time (so columns A-B-C with links but not the others from D to Z).

I hope that's clearer. Thanks for the response in advance.
0
Réponse 3 / 7
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 
Hello

You select your first sheet by clicking on the tab at the bottom, then press the Shift key and select the last sheet; all the sheets between these two are selected (the tabs turn white), and any action taken on the first sheet is automatically applied to the others

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein
0
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   7 281
 
Hello,

don’t forget to deselect the 3D selection at the end by clicking on another sheet, or you might be in for a surprise.
eric
0
Réponse 4 / 7
ventatto
 
Hello everyone,

Thank you for your responses, but there is still a problem (perhaps of a different nature). When I perform the action from via55, the "button" that allows me to add rows is grayed out when I am in my table, whereas it is usable when I try to insert a row further down the sheet outside of my table, where all the cells are empty...

Thank you again for your solutions

Best regards
0
Réponse 5 / 7
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 
Hello

Can you insert a line with my manipulation
Which button are you talking about?
Inserting lines may be blocked if one of the sheets is protected

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein
0
Réponse 6 / 7
ventatto10 Posted messages 3 Status Membre
 
(It's still me, I just created an account in the meantime and had to change my username a bit)

I'm talking about the button above on the right in the "Home" tab then the sub-division "cell". Nothing is protected in my sheets...

Furthermore, when I want to modify a cell in one sheet so that it is changed in all selected sheets, this message appears:

"<i>Cannot make changes to a table or XML mapping when multiple sheets are selected</i>"

Best regards

ventatto
0
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   7 281
 
Hello,

the message is clear.
You can do it as long as it doesn't affect a table, these sheets should be done individually.
Hence the fact that these options are grayed out.
Eric
0
ventatto10 Posted messages 3 Status Membre > eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention  
 
Alright, thank you very much, so I deleted all my tables to manage to do this manipulation... Phew finally!

Thank you!
0
Réponse 7 / 7
ventatto10 Posted messages 3 Status Membre
 
However, a small problem... (again...): the formulas in my sheet 1 depend on the other sheets. In fact, when I modify a relevant cell in my other sheets, the cell in my sheet 1 that takes this cell into account in its formula does not update. To make it work, I have to double-click on this cell and then press "enter", and then it works... not very practical to do this every time.

If anyone has another solution for me so that the update happens automatically as it usually does.

Thanks in advance again.

Best regards.
0