Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula problem
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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula problem
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" wrote in message ... 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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula problem
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: 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" wrote in message ... 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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula problem
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(Sheet 1!A1:G1TODAY(),COLUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- If this post helps click Yes --------------- Peggy Shepard "puiuluipui" wrote: 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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Formula problem | Excel Worksheet Functions | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Discussion (Misc queries) | |||
Problem with formula =MAX(A1-40,0) | Excel Discussion (Misc queries) | |||
Formula problem | Excel Discussion (Misc queries) |