View original (French)

Dropdown menu to select a sheet

Solved
mathieu4012 Posted messages 4 Status Member -  
Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   -
Hello,

I am currently creating an Excel file to track the synthetic yield rate of a production site, but I’m running into a problem. The site has 4 presses, each with an identical Excel sheet; only the values change. Therefore, I’d like you to tell me how to create a dropdown list containing the sheet names and have this choice affect the sheet used in the calculations, for example below.

Thank you in advance.

=+SOMME.SI.ENS('PRESSE 1'!$M$4:$M$3000;'PRESSE 1'!$A$4:$A$3000;$B$12;'PRESSE 1'!$O$4:$O$3000;C28)

Configuration: Windows / Firefox 59.0

4 answers

  1.
    Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
     
    Hello,

    If the drop-down list (data validation) is in A1, with a formula using INDIRECT():
    =SOMME.SI.ENS(INDIRECT("'" & A1 & "'!$M$4:$M$3000");INDIRECT("'" & A1 & "'!$A$4:$A$3000");$B$12;INDIRECT("'" & A1 & "'!$O$4:$O$3000");C28)

    Best regards
    Patrice
    1
    1. mathieu4012 Posted messages 4 Status Member
       
      Hello Patrice, First of all I wanted to thank you for your response which solves my first problem; however, despite my research I don't know how to create a dropdown list to select my sheets ('PRESSE 1'!), ('PRESSE 3'!), ('PRESSE 4'!), ('PRESSE 5'!). Could you explain it to me? Thank you in advance for your help.
      0
    3. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
       
      PS: With the previous formula, the dropdown must not contain any apostrophe or exclamation point, that is, in the source field:
      PRESSE 1;PRESSE 3;PRESSE 4;PRESSE 5
      0
  2. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello.
    Rather than a dropdown list, it would be much more practical and appropriate to insert hyperlinks.

    --
    It’s good, retirement! Especially in the Caribbean...
    Raymond (INSA, AFPA)
    1
    1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
       
      Hello Raymond

      The links won’t fit: « how to create a dropdown list [...] and that this choice modifies the page taken into account in the calculations ...»

      Kind regards
      Patrice
      0
    2. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      Exact ...
      0
  3. mathieu4012 Posted messages 4 Status Member
     
    Hello,

    I created hyperlinks as you advised, however I can’t get the formula to work and I get a #REF error. I’m attaching a screenshot to help you understand the issue.
    Thanks in advance for your help!

    0
  4. mathieu4012 Posted messages 4 Status Member
     
    Hello,

    I finally managed to get everything working thanks to your advice. I appreciate both of your help.
    0