Code problem

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

Hi, i have this code:
=IF(COUNT(U8:X8),IF(TODAY()>=MAX(U8:X8),"Expired",INDEX(U8:X8,,MIN(IF(U8:X8>TODAY(),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 array formula which should work on any range.

=IF(COUNT(U8:X8),IF(TODAY()>=MAX(U8:X8),"Expired",INDEX(U8:X8,,MIN(IF(U8:X8>TODAY(),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
 
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:X8>TODAY(),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
Click to expand...
 
Hi, i have this code:
=IF(COUNT(U8:X8),IF(TODAY()>=MAX(U8:X8),"Expired",INDEX(U8:X8,,MIN(IF(U8:X8>TODAY(),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!
Click to expand...


Try this:

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

Hope this helps / Lars-Åke
 
Back
Top