| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help w/ INDIRECT
I get "No Show" everytime for the following:
=IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Hard to tell what you're trying to do but the error is generated if any cell
is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
It could also be caused by any cell in that range having an error value. But
since the OP specified "No Show" when all columns are blank in X33:IV33, I assume that was the purpose of the ISERROR in the original formula and not because those cells can actually evaluate to errors. "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Your array formula seems to be going in the right direction. $X$26:$IV$26
are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there
any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for
excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Try this array formula:
=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
It works wonderfully! Thanks.
Okay, maybe you can help with a very similar problem. The follow works, but as I'm finding out it's causing a lengthy calculation time. =INDIRECT(ADDRESS(26,MAX(COLUMN(V33:IV33)*(V33:$IV 33<"")))) I'm reading that the INDIRECT function is what is causing the problem. Can you modify the your formula to now find the last date the student attended class? "T. Valko" wrote: Try this array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
While we're at it, this version will replace your current formula for the
MAX date: Still an array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33: IV33)),"No Show") Format as DATE Biff "T. Valko" wrote in message ... Try this array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
YOUR GOOD! Thank you!!! : )
"T. Valko" wrote: While we're at it, this version will replace your current formula for the MAX date: Still an array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33: IV33)),"No Show") Format as DATE Biff "T. Valko" wrote in message ... Try this array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Still an array formula:
=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33 :IV33)),"No Show") Check that! That formula does not need to be array entered. It can be normally entered. Biff "T. Valko" wrote in message ... While we're at it, this version will replace your current formula for the MAX date: Still an array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33: IV33)),"No Show") Format as DATE Biff "T. Valko" wrote in message ... Try this array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
You're welcome. Thanks for the feedback!
Biff "St@cy" wrote in message ... YOUR GOOD! Thank you!!! : ) "T. Valko" wrote: While we're at it, this version will replace your current formula for the MAX date: Still an array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33: IV33)),"No Show") Format as DATE Biff "T. Valko" wrote in message ... Try this array formula: =IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| INDIRECT.EXT | Excel Worksheet Functions | |||
| INDIRECT.EXT | Excel Worksheet Functions | |||
| indirect using name | Excel Worksheet Functions | |||
| INDIRECT Help | Excel Worksheet Functions | |||
Linear Mode
