1

This is current formula
=IF(Sheet1!A1="?","Check",IF(Sheet2!A2="?","Check","")) (nested IF formula)

This is what formula should do: If any A1 cell from Sheet1 to Sheet100 has ? written in cell, than write in cell in which is this code word Check.

Question
How to write fast this formula without having to type all 100 IF formulas? And if so, any tip to write them faster? Best would be if we can skip VBA since I can't use it on android Excel version.
Thanx

Denis
  • 13
  • 4
  • Yes, that can be option. – Denis Mar 13 '17 at 19:55
  • 2
    since you don't want to / can't use VBA, have a look here: http://stackoverflow.com/questions/21919461/excel-using-countif-countifs-across-multiple-sheets-same-column. reference the 2nd answer (the one w/ 8 upvotes). – sous2817 Mar 13 '17 at 20:05

2 Answers2

3

Use this formula:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet" & ROW(1:100) & "!A1"),"?"))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    I was halfway through figuring this one out. I just knew when I hit refresh you or jeeped would have it. I'm learning just following you two around. – Jacob Edmond Mar 13 '17 at 20:15
  • 1
    @JacobEdmond keep going and learning, you may get faster than me. I learned most of what I know from here. – Scott Craner Mar 13 '17 at 20:18
  • Very nice formula, i did not had that idea that @sous2817 proposed in my mind at all :) Thanx Scott Craner for answering question and sharing formula. – Denis Mar 13 '17 at 20:22
0
=IF(OR(Sheet1!A1="?",Sheet2!A1="?",Sheet3!A1="?"),"Check","")

I think this format is going to be the quickest way to do it with a formula.

Jacob Edmond
  • 808
  • 2
  • 7
  • 14