Formula problem

[puiuluipui]

Hi, i have a problem with this code:
=IF(NOT(SUMPRODUCT(--(Sheet1!A1:G1>=TODAY()),--(Sheet2!A1:G1<>""
))),"Please pay",
IF(COUNT(Sheet1!A1:G1),IF(TODAY()>=MAX(Sheet1!A1:G1),"Expired",
INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1>TODAY(),COLUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),""))

The "Expired" doesn't appear. This function does not work.
Can somebody help me with this?
Thanks!

 

[Bernie Deitrick]

The short answer is - your formula logic is incorrect. But your really need to explain - in words -
what you want to do rather than show what doesn't work - it doesn't work because the logic is
flawed, and we cannot figure out what you actually want to do from the flawed formula.

HTH,
Bernie
MS Excel MVP

 

[puiuluipui]

Hi Bernie, this formula is working ok, except for the "Expired" part of the
code.
I have in sheet1!A1:G1 some dates. In sheet2!A1:G1 i have receipt nr. and
date.
For each date in sheet1, i need to enter a receipt nr/date to sheet2 same
cell.

Ex:
sheet 1
A1 B1
01.08.2009 01.09.2009

sheet 2
A1 B1
024/01.08.2009 045/01.09.2009

If i dont write nr/date in sheet 2 cell B1, the formula display "please pay".
After i write nr/date, the formula is is looking to next cell in sheet 1 (C1).
I need the formula to display "Expired" if "C1" is empty(if there is no date
after B1)
The formula is working ok as long as cells in sheet1 contains dates. If
there is no more dates in sheet 1, in range, i need the formula to display
Expired"
Can this be done?
Thanks!



"Bernie Deitrick" a scris:

 

[pshepard]

Hi puiuluipui,

From the original formula, :

IF(COUNT(Sheet1!A1:G1) - this logical test is not complete - the count
should equal, be greater than or less than something in order to proceed to
if this test is met, to check for the next test of
IF(TODAY()>=MAX(Sheet1!A1:G1),"Expired"

=IF(NOT(SUMPRODUCT(--(Sheet1!A1:G1>=TODAY()),--(Sheet2!A1:G1<>""
))),"Please
pay",IF(COUNT(Sheet1!A1:G1),IF(TODAY()>=MAX(Sheet1!A1:G1),"Expired",INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1>TODAY(),COLUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),""))