Changing Sheet Referencec In This Formula?

[jaedmar]

I use a large spreadsheet to track fitness assessment results for our fire department.
What I want to do now is create a formula to identify each person who achieves minimum results. So far, no problem. Each person has his/her own sheet. The sheets are numbered sequentially from 1 to 100.

Here is the formula I have created to use on the Awards sheet:

=IF(AND('1'!$B$3="Female",'1'!$M$21>='Current Average Comparisons'!$B$12, '1'!$M$30>='Current Average Comparisons'!$B$20, '1'!$M$31>='Current Average Comparisons'!$B$21, '1'!$M$32>='Current Average Comparisons'!$B$22, '1'!$M$33>='Current Average Comparisons'!$B$23), "IRONMAN","")

What this does is identify a person who has met all the six criteria as an Ironman Award winner.

The '1's in the above formula identify that it is the sheet entitled "1" from which the the information is pulled.

I would like to be able to cut and paste this formula down a column (next to people's names), and have the sheet reference change to 2, 3, 4...

What adjustments/additions do I need to do to this formula to keep me from manually having to change six sheet references in every cell?

I have read similar posts on this message board, but cannot get seem to get any of the recommendations to work here.

 

[drsarao]

Easiest probably will be to use ROW() in building the references.
eg ROW() & "!$B$3"="Female" instead of '1'!$B$3="Female" for the first comparison.
Presuming of course that this formula is on the row number 1.
You can subtract to correct. eg ROW()-2 & "!$B$3"="Female" - if the formula is on row 3.
Now when you copy down the formula, sheet numbers will increment in the references.
HTH

 

[Andrew Poulsom]

Try:

=IF(AND(INDIRECT("'"&ROWS(A$1:A1)&"'!$B$3")="Female",INDIRECT("'"&ROWS(A$1:A1)&"'!$M$21")>='Current Average Comparisons'!$B$12,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$30")>='Current Average Comparisons'!$B$20,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$31")>='Current Average Comparisons'!$B$21,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$32")>='Current Average Comparisons'!$B$22,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$33")>='Current Average Comparisons'!$B$23), "IRONMAN","")

 

[jaedmar]

Thank you. That did work.

As part of the AND statement, I need to add one more condition (an OR statement with four possibilties). I thought this would be easy, but apparently was wrong.

This is what I have written:
OR(INDIRECT("'"&ROWS(A$1:A1)&"'!$M$22")>='Current Average Comparisons'!$C$59,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$23")>='Current Average Comparisons'!$C$60,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$24")>='Current Average Comparisons'!$C$61,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$25")>='Current Average Comparisons'!$C$62))

Does this look written correctly? How would I insert this into the previous formula?

 

[jaedmar]

Try:

=IF(AND(INDIRECT("'"&ROWS(A$1:A1)&"'!$B$3")="Female",INDIRECT("'"&ROWS(A$1:A1)&"'!$M$21")>='Current Average Comparisons'!$B$12,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$30")>='Current Average Comparisons'!$B$20,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$31")>='Current Average Comparisons'!$B$21,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$32")>='Current Average Comparisons'!$B$22,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$33")>='Current Average Comparisons'!$B$23), "IRONMAN","")

I am closer.

Here is what I have now:

=IFERROR(IF(AND(INDIRECT("'"&ROWS(A$1:A1)&"'!$B$3")="Male",INDIRECT("'"&ROWS(A$1:A1)&"'!$m$8")>=25,INDIRECT("'"&ROWS(A$1:A1)&"'!$m$8")<30,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$21")>='Current Average Comparisons'!$D$58,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$30")>='Current Average Comparisons'!$D$66,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$31")>='Current Average Comparisons'!$D$67,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$32")>='Current Average Comparisons'!$D$68,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$33")>='Current Average Comparisons'!$D$69, OR(INDIRECT("'"&ROWS(A$1:A1)&"'!$M$22")>='Current Average Comparisons'!$D$59,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$23")>='Current Average Comparisons'!$D$60,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$24")>='Current Average Comparisons'!$D$61,INDIRECT("'"&ROWS(A$1:A1)&"'!$M$25")>='Current Average Comparisons'!$D$62)),"IRONMAN",""),"")

My problem now is with the OR statements which are part of the AND conditions. The goal is to count it as a true statement if any of the four OR statements is greater or equal to four other cells' value. However, three of the four are likely to be blank cells, which yields an error.

How do I get the formula to only consider the four OR conditions if there is a value?

 

[Andrew Poulsom]

Your formula works for me. Blank cells don't cause an error.

 

[jaedmar]

What I am getting is a #N/A. If fugured because I am asking it to do something if the referenced cell equals or is greater than another number, but the referenced cell does not have any number in it.

Here is a much more detailed description of what I am looking at. I wrote this today to post on another board when Mr. Excel was down.

I am working on a fitness spreadsheet for my fire department. The hole is getting deeper and deeper. Before you start laughing if I am way off, I am a firefighter...
The following formula almost works for what I need it to do. There are a total of 12 conditions, however, the last four are part of an OR statement. Each of the four conditions in the OR statement need to have an AND as a condition that some number/value is actually in the referenced cells. Else, it would yield #N/A before ever yielding the desired "IRONMAN". The fact is that only one of the four referenced cells (M22, M23, M24, M25) is likely to have a number in it. The others are most likely going to be blank.
I have tried to bold the OR conditions to which I am referring.
So, how can I add some type of ISTEXT in an AND to the OR? Or, what else can I do to this formula yield "IRONMAN" if all initial conditions are met, and if at least one of the OR conditions has a value which meets it's condition?


=IFERROR(IF(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$B$3")="Male",INDIRECT("'"&ROWS(A$1:A6)&"'!$m$8")>=35,INDIRECT("'"&ROWS(A$1:A6)&"'!$m$8")<40,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$21")>='Current Average Comparisons'!$F$58,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$30")>='Current Average Comparisons'!$F$66,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$31")>='Current Average Comparisons'!$F$67,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$32")>='Current Average Comparisons'!$F$68,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$33")>='Current Average Comparisons'!$F$69, OR(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$22")>='Current Average Comparisons'!$F$59,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$23")>='Current Average Comparisons'!$F$60,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$24")>='Current Average Comparisons'!$F$61,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$25")>='Current Average Comparisons'!$F$62)),"IRONMAN",""),"")

Being over my head, here is somewhat of what I am thinking:
OR(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$22")>='Current Average Comparisons'!$F$59),(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$22")=ISTEXT)),(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$23")>='Current Average Comparisons'!$F$60),(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$23")=ISTEXT)),(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$24")>='Current Average Comparisons'!$F$61),(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$24")=ISTEXT)),(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$25")>='Current Average Comparisons'!$F$62),(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$25")=ISTEXT)))),"IRONMAN",""),"")

I tried this recommendation that someone gave, but it does not yield "IRONMAN" even for those who meet all the conditions. If I remove the IFERROR, it just yields #N/A unless I go add numbers to the empty (M-column) cells or to those (M-column) cells that have #N/A also.

=IFERROR(IF(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$B$3")="Male",INDIRECT("'"&ROWS(A$1:A6)&"'!$m$8")>=35,INDIRECT("'"&ROWS(A$1:A6)&"'!$m$8")<40,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$21")>='Current Average Comparisons'!$F$58,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$30")>='Current Average Comparisons'!$F$66,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$31")>='Current Average Comparisons'!$F$67,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$32")>='Current Average Comparisons'!$F$68,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$33")>='Current Average Comparisons'!$F$69, OR(AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$22")),INDIRECT("'"&ROWS(A$1:A6)&"'!$M$22")>='Current Average Comparisons'!$F$59),AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$23")),INDIRECT("'"&ROWS(A$1:A6)&"'!$M$23")>='Current Average Comparisons'!$F$60),AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$24")),INDIRECT("'"&ROWS(A$1:A6)&"'!$M$24")>='Current Average Comparisons'!$F$61),AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$25")),INDIRECT("'"&ROWS(A$1:A6)&"'!$M$25")>='Current Average Comparisons'!$F$62))),"IRONMAN",""),"")

 

[jaedmar]

Figured it out. Here it is.

=IFERROR(IF(AND(INDIRECT("'"&ROWS(A$1:A6)&"'!$B$3")="Male",INDIRECT("'"&ROWS(A$1:A6)&"'!$m$8")>=35,INDIRECT("'"&ROWS(A$1:A6)&"'!$m$8")<40,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$21")>='Current Average Comparisons'!$F$58,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$30")>='Current Average Comparisons'!$F$66,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$31")>='Current Average Comparisons'!$F$67,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$32")>='Current Average Comparisons'!$F$68,INDIRECT("'"&ROWS(A$1:A6)&"'!$M$33")>='Current Average Comparisons'!$F$69, OR(AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$22"))>='Current Average Comparisons'!$F$59),AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$23"))>='Current Average Comparisons'!$F$60),AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$24"))>='Current Average Comparisons'!$F$61),AND(ISNUMBER(INDIRECT("'"&ROWS(A$1:A6)&"'!$M$25"))>='Current Average Comparisons'!$F$62))),"IRONMAN",""),"")