Code problem
 

Hi, i have this code:
=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired", INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8))))-TODAY()),"")
This code it's working only in A1:D1 range, but in U8:X8 range gives me
#REF! error.
Can this be fixed?
Thanks!

Code problem
 

Try this array formula which should work on any range.

=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired", INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8)))-COLUMN(U8:X8)+1)-TODAY()),"")

PS: The reason is that COLUMN() returns the column number which is in the
range of 20+ where as the formula uses INDEX() which has got only 4 columns

If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code:
=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired", INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8))))-TODAY()),"")
This code it's working only in A1:D1 range, but in U8:X8 range gives me
#REF! error.
Can this be fixed?
Thanks!

Code problem
 

Beautifull!!
Thanks!

"Jacob Skaria" a scris:

Try this array formula which should work on any range.

=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired", INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8)))-COLUMN(U8:X8)+1)-TODAY()),"")

PS: The reason is that COLUMN() returns the column number which is in the
range of 20+ where as the formula uses INDEX() which has got only 4 columns

If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code:
=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired", INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8))))-TODAY()),"")
This code it's working only in A1:D1 range, but in U8:X8 range gives me
#REF! error.
Can this be fixed?
Thanks!

Code problem
 

On Fri, 3 Jul 2009 03:36:01 -0700, puiuluipui
wrote:


Hi, i have this code:
=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired" ,INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8))) )-TODAY()),"")
This code it's working only in A1:D1 range, but in U8:X8 range gives me
#REF! error.
Can this be fixed?
Thanks!


Try this:

=IF(COUNT(U8:X8),IF(TODAY()=MAX(U8:X8),"Expired", INDEX(U8:X8,,MIN(IF(U8:X8TODAY(),COLUMN(U8:X8)-COLUMN(U8)+1)))-TODAY()),"")

Hope this helps / Lars-Åke