| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with code | Excel Discussion (Misc queries) | |||
| CODE PROBLEM | Excel Discussion (Misc queries) | |||
| VB Code Problem | Excel Discussion (Misc queries) | |||
| XLS to CSV Code Problem | Excel Worksheet Functions | |||
| Little problem with this code... | Excel Discussion (Misc queries) | |||
Linear Mode
