View original (French)

Automatic Excel table copy without macro

Solved
sodwac Posted messages 7 Status Member -  
sodwac Posted messages 7 Status Member -
Hello,

After a lot of searching, I’m turning to your expertise to help me solve a problem in Excel. First of all, the prerequisites are:
_ no possibility of using macros,
_ file available on a shared network so processing times must be relatively short.

I have a workbook where the first sheet (F1) contains the following columns:
Collaborator Action January February March ...
Jacques Dev 1
Serge Dev 6
Emile Test 2 4

I am looking to display exactly the same table in another sheet (F2). It should update automatically when there’s a change, an addition, or a deletion.

Here are the solutions I’ve tried:
_ Copy/Paste special/Create link: When inserting in F1, F2 does not update

_ Formula in all cells of F2 like: =F1!A1 (for example, A1 in F2)=> Works for an insert but not for a deletion (resulting in #REF in the F2 row).

_ Formula in all cells of F2 like: =INDEX('F1'!$A:$Q;ROW();COLUMN())
This solution works!!!! Addition, deletion, modification, everything is fine. Except when I place the file on the network and share it, inserting a row takes 45 seconds => Not viable.

I also tried using a pivot table but unfortunately, it aggregates my values and I no longer have the level of detail expected...

Thank you in advance for your responses.

Sodwac

5 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Re,

    Well, you've tried everything or almost, and without macros it limits you.

    To test:
    =INDIRECT("Sheet1!B2")
    which should withstand insertions and deletions.
    But you're going to have fun entering everything, the incremental fill is ineffective.

    or:
    =INDIRECT("Sheet1!"&ADDRESS(ROW();COLUMN()))
    which will return the same row, same column and the fill works.
    You can add line/column offsets:
    =INDIRECT("Sheet1!"&ADDRESS(ROW()+3;COLUMN()+2))
    An empty cell will return 0, no choice.
    eric
    --
    Never respond to an unsolicited private message...
    Well, that's done.
    2