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

%3CLINGO-SUB%20id%3D%22lingo-sub-1754951%22%20slang%3D%22en-US%22%3ETRUE%20checkbox%20value%20auto-populates%20adjacent%20cell%20from%20another%20Sheet%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1754951%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20spreadsheet%20has%202-sheet%20tabs%3A%20'Sheet1'%20and%20'Sheet2'.%3C%2FP%3E%3CP%3EIn%20'Sheet2'%20cell%20'A1'%20there's%20a%20checkbox.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20put%20a%20check%20(True)%20in%20the%20checkbox%20in%20'Sheet2'%20cell%20'A1'%2C%20is%20there%20a%20way%20to%20auto-populate%20'Sheet2'%20cell%20'B1'%20with%20a%20value%20from%20'Sheet1'%20cell%20'A1'%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1754951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1755179%22%20slang%3D%22en-US%22%3ERe%3A%20TRUE%20checkbox%20value%20auto-populates%20adjacent%20cell%20from%20another%20Sheet%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1755179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823817%22%20target%3D%22_blank%22%3E%40jegarner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20assume%20that%20you%20have%20created%20a%20checkbox%20from%20the%20Form%20Controls.%3C%2FP%3E%0A%3CP%3ELink%20the%20checkbox%20to%20a%20cell%20on%20Sheet2%2C%20for%20example%20A1%20itself%20(if%20you%20assign%20a%20fill%20color%20to%20the%20checkbox%2C%20you%20won't%20see%20the%20value%20through%20it).%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20the%20following%20formula%20in%20B1%3A%3C%2FP%3E%0A%3CP%3E%3DIF(A1%2CSheet1!A1%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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'?

4 Replies
Highlighted

@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,"")

Highlighted

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'?

Highlighted

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.....

Highlighted

@Hans Vogelaar 

 

I figured it out. I had to change the conditional formatting to text for the 0 and it worked.

Thanks again for your help with this Hans.