Forum Discussion

jegarner's avatar
jegarner
Copper Contributor
Oct 07, 2020

TRUE checkbox value auto-populates adjacent cell from another Sheet tab

My spreadsheet has 2-sheet tabs: 'Sheet1' and 'Sheet2'.

In 'Sheet2' cell 'A1' there's a checkbox. 

 

If I put a check (True) in the checkbox in 'Sheet2' cell 'A1', is there a way to auto-populate 'Sheet2' cell 'B1' with a value from 'Sheet1' cell 'A1'?

excel

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 07, 2020

    jegarner 

    I assume that you have created a checkbox from the Form Controls.

    Link the checkbox to a cell on Sheet2, for example A1 itself (if you assign a fill color to the checkbox, you won't see the value through it).

    You can then use the following formula in B1:

    =IF(A1,Sheet1!A1,"")

    • jegarner's avatar
      jegarner
      Copper Contributor
      Oct 07, 2020

      Thanks Hans, it worked as you suggested.

       

      Is there also a way to change the formula so that if the checkbox is not checked, it displays a '0'?

      • jegarner's avatar
        jegarner
        Copper Contributor
        Oct 07, 2020

        I figured it out by changing the formula to this: =IF(A1,Sheet1!A1,"0")

         

        However, when I now try to change the background color with conditional formatting equal to '0', it doesn't work. Not sure why.....

Resources