• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Are There Any

dparteka

dparteka

Member
I have a workbook that has three identical worksheet tabs named 01, 02, 03 and one other named Totals. Cells A1 thru J1 in the 01, 02, 03 worksheets will have Accept or Reject text in them. In cell A1 of the Totals worksheet I need a formal that will display Accept if all 30 of the cells from 01, 02, 03 are Accept but display Reject if any one of the 30 cells has a Reject... thank-you
 
=IF(SUMPRODUCT(COUNTIF(INDIRECT({"01","02","03"}&"!A1:J1"),"Reject")),"Reject","Accept")
 
Hui... awesome, works great... another question, I've changed the names of the worksheet tabs from "01", "02", "03" to "01-10", "11-20", "21-30" and the formula no longer works, I'm getting the error "Invalid Cell Reference Error", any ideas on that?
 
Hi Dparteka,


Yes, Hui created a beauty of a formula there!


My guess is that Excel interprets your new sheet names as dates.

Name your sheet like this: 01_10 and 11_20 and 21_30 and update the formula as seen below.


=IF(SUMPRODUCT(COUNTIF(INDIRECT({"01_10","11_20","21_30"}&"!A1:J1"),"Reject")),"Reject","Accept")


This works for me :)


Cheers,

Kevin
 
Dparteka


The formula Kevin posted should do the trick


The Indirect function part of the formula takes text values like "01" or "01_10" and converts them with the other text "!A1:J1" into valid ranges

It wasn't taking sheets 1, 2 & 3 it was taking sheets named 01, 02 and 03


I Hope thats helps unconfuse you


ps: I don't think unconfuse is a good word

pps: Turns out it is a valid word after all and I've used it correctly
 
You must log in or register to reply here.
Back
Top