Forum Discussion

Jacqueline28's avatar
Jacqueline28
Copper Contributor
Jan 26, 2021

Inserting one checkbox and linking to multiple cells to trigger sums

Hi - I am adding checkboxes that are linked to cells to calculate costs. So far I have one check box per cell. I was wanting to link 1 checkbox to 6 cells so that I can check one box and select them ...
excel
Formulas and Functions
Jacqueline28's avatar
Jacqueline28
Copper Contributor
Jan 28, 2021

Hi NikolinoDE - Could I send you and example of what I am working on to see if you could give me specific insights to the document?

 

Thanks in advance!

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jan 28, 2021

Jacqueline28 

 

Here are two files in this direction as an example.

 

I hope that you will take a step back into your project with these suggested solutions.

 

I wish you continued success with Excel

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

 

  • Jacqueline28's avatar
    Jacqueline28
    Copper Contributor
    Jan 28, 2021

    Thank you. NikolinoDE Please see attached link. For example, I am trying to get the box in the merged cell of A3 and A4 when checked to highlight both rows as well as trigger both J3 and J4 to be "true" and add both cost amounts to the final "total" at the bottom of the sheet.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 29, 2021

      Jacqueline28 

      With dynamic arrays support that could be transformed to

      =SUM(FILTER(Table133[Column9],'Group 3'!$A$3:$A$61))+
 SUM(FILTER(Table133[Column9],'Group 3'!$A$2:$A$60))

      or, more reliable

      =SUM(FILTER(Table133[Column9],
            INDEX('Group 3'!A:A,ROW(Table133[[#Headers],[Column1]])+1):
            INDEX('Group 3'!A:A,ROWS(Table133[Column1])+
                                ROW(Table133[[#Headers],[Column1]]))))+
 SUM(FILTER(Table133[Column9],
            INDEX('Group 3'!A:A,ROW(Table133[[#Headers],[Column1]])):
            INDEX('Group 3'!A:A,ROWS(Table133[Column1])+
                                ROW(Table133[[#Headers],[Column1]])-1)))
      • Jacqueline28's avatar
        Jacqueline28
        Copper Contributor
        Feb 03, 2021

        SergeiBaklan Thank you for all of your help. I have attached another sheet. I am trying to get a formula for the Conditional Formatting that when I check the box in the merged cells that it highlights all the 8 rows (instead of just row 3) as well as recognizes all 8 values in the total line. Thanks in advance for any help!

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      Jan 29, 2021

      Jacqueline28 

      I think I couldn't offer you a better description and better approach than Mr. JMB17 - Jeff Blakley has offered you.

       

      Thank you for your patience and time.

       

      Wish you a nice day / night with lots of health, joy and love.

       

      Nikolino

      I know I don't know anything (Socrates)

    • JMB17's avatar
      JMB17
      Bronze Contributor
      Jan 29, 2021

      Jacqueline28 

       

      First, I would put the number of rows per group into a cell so that if you change the number of rows per group (3) to something else at a later date, it should hopefully be easy to update (I put it in cell A62).

       

      I believe this will work - use offset and deduct one for the row offset if the remainder of the row count / 3 is greater than zero (so A6 references A6, A7 references A6, and A8 references A7).

       

      =OFFSET($A3,-(MOD(ROWS($A$3:$A3)-1,$A$62)>0),0)

       

      For your conditional sum, I personally would un-merge Column A and link the lower cell to the upper cell. Then, I think it would be a straightforward sumif.

       

      Otherwise, you could try (and this would benefit from newer functions like LET, UNIQUE, FILTER, and SEQUENCE, which I don't have yet):

       

      =SUM(((INT((ROW(A3:A61)-ROW(A$3))/A62)+1)=MMULT(TRANSPOSE(--(A3:A61)),--(INT((ROW(A3:A61)-ROW(A$3))/A62)+1=TRANSPOSE(ROW(INDIRECT("1:"&INT(ROWS(A3:A61)/A62)+1)))))*TRANSPOSE(ROW(INDIRECT("1:"&INT(ROWS(A3:A61)/A62)+1))))*Table133[Column9])

       

      And, you may need to hit Ctrl+Shift+Enter after typing/copying into the formula bar (if you have  dynamic arrays, you may not need to).

       

       

       

Resources