ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Please help w/ INDIRECT (https://www.excelbanter.com/excel-worksheet-functions/143452-please-help-w-indirect.html)

St@cy
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.

T. Valko
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.



JMB
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.




St@cy
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.




T. Valko
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.

St@cy
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.

T. Valko
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.

St@cy
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.

T. Valko
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.

St@cy
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.

T. Valko
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.

T. Valko
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.


All times are GMT +1. The time now is 02:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com