View original (French)

Automatic update of multiple Excel sheets with different number of rows

Nat -  
 Nat_0362 -
Hello,

I am new to the forum. I would like to know what the method is so that when I update my first Excel sheet, it automatically updates the other sheets without me having to copy everything. I have already tried selecting all my sheets and making my change, but since the number of rows is different on the Excel sheets, my data ends up being placed somewhat randomly. Is there a way to remedy my problem? Thank you for your advice.

Configuration: Windows / Firefox 72.0

3 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello and welcome

    It all depends on how you want to structure your file:
    - either it is possible in the other sheets to call data from Sheet1 by using formulas like =Sheet1!B3
    - or you can create a macro that, upon changes in specific cells in Sheet1, will fill specific cells in the other sheets, which will then have a value without formulas

    You can post an example of your file by uploading it to mon-partage.fr, copying the created link, and pasting it back here

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. Nat
       
      Thank you for your response.

      Here is the link to my file: https://mon-partage.fr/f/mw0vkyrV/

      If I add a vehicle to my general list, I would like it to appear in the other sheets when I need it (technical inspection, repairs, and mileage).

      Thank you for your help.

      Best regards.

      Nathalie
      0
  2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Re,

    As it stands, your file is unusable because your general list does not include all the vehicles from the other sheets.

    For the CT Sheet, since it is a copy of the list, you can use the formula ='LISTE GENERALE'!C2 and drag it to the right and down.
    You can do the same for the KMS sheet.

    For the REPARATION sheet, it's different because you need to be able to choose a vehicle from a list for each line. So you need to create a drop-down list in the Denomination column from the general list once it is complete, and then we can retrieve the registration number in the next column using a VLOOKUP formula.

    If your general list doesn’t change, you can name the necessary range for the drop-down list (for example, name the range I2:I7 vehicles).
    If your general list might grow, you need to name the range dynamically with the formula =OFFSET('LISTE GENERALE'!$I$2;;;COUNTA('LISTE GENERALE'!$I:$I)-1), your drop-down list will adjust accordingly.

    If you can't get everything set up, once your general list is complete, send me the file back, and I will take a look at it.

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. Nat
       
      Thank you for your quick response. I'm trying to do it, and if I can't, I'll send you my file, but since there are license plate numbers, it's complicated to send it as is. I need to change the plates just in case because it's on the internet. Do you have a solution for that? Anyway, thank you very much, I'm trying your solutions.
      0
    2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > Nat
       
      Erase all columns with the plates and leave them blank; I will put dummy numbers to make the tests. When I send you the file back, you will only have to copy and paste the registration column from your file to this one in the general list.

      And if you want your file not to go through the forum, you can send me the link via private message (click on my username at the top of this message).
      0
    3. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482 > via55 Posted messages 14393 Registration date   Status Member Last intervention  
       
      ... but Nat will need to start by registering as a member to gain access to Private Messaging (among other benefits)!
      0
    4. Nat > Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention  
       
      Hello Raymond,

      I'm signing up right away, sorry for not having thought of it earlier.

      Best regards.

      Nathalie.
      0
    5. Nat_0362 Posted messages 2 Status Member > via55 Posted messages 14393 Registration date   Status Member Last intervention  
       
      Good evening,

      I am only focused on the first sentence of your explanation, which is to make a copy of the general list with the formula. It works, but I have a lot of 0s in all my rows and columns, and no matter how hard I try to right-click and delete, nothing gets deleted. If I delete manually, there’s nothing left, but there are no formulas either. I'm stuck. Anyway, thank you for your help. And I have registered well.... Best regards. Nathalie
      0
  3. Nat_0362 Posted messages 2 Status Member
     
    I just signed up but now my username is N_0362.

    Thank you for your help.

    I will finish working with the information you provided and I will get back to you if I can't manage.

    Best regards.

    Nathalie
    0