How do I reference multiple cells in one sheet in one cell in another sheet?

Question

I am using Google Sheets and I have two sheets in one document (SHEET1 and SHEET2). I want to be able to use a formula that allows me to:

Can this be done?

Jacob Jan Tuinstra · Accepted Answer · 2013-04-15 11:22:06Z

1

In SHEET2 in cell A1, add the following formula:

=IF(AND(Sheet1!A1="s";Sheet1!B1="s";Sheet1!D1="s";Sheet1!F1="s");"s";"invalid")

Is says:

If A1, B1, D1 and F1 in sheet 1 all contain an s then write s else write invalid

answered Apr 15, 2013 at 5:29

22.8k16 gold badges101 silver badges191 bronze badges

1

I think you are missing the fact that the cells A1, B1, etc. are in a different sheet (ie. SHEET1). In which case you need to prefix the cell references with SHEET1!.
– MrWhite
Apr 15, 2013 at 11:18
@w3d I think you just added the 100th upvote on the google-spreadsheets tag
– Jacob Jan Tuinstra
Apr 15, 2013 at 11:25

Add a comment |

pnuts · Accepted Answer · 2017-12-15 12:48:50Z

0

Slightly shorter:

=if(countif({Sheet1!A1:B1,Sheet1!D1,Sheet1!F1},"s")=4,"s","invalid")

answered Dec 15, 2017 at 12:48

pnuts

17.4k5 gold badges52 silver badges103 bronze badges

Add a comment |

2 Answers 2