IF I think?
Posted by Lulu on June 03, 2001 1:38 PM
Hi.. hope someone can help me..
What Im trying to do is an IF function
.. I think.
Sheet one column A I have order numbers, column B I have all pay-codes (Discover, Visa, Master Card etc.
) in column C I have status of the orders (canceled, in production, on hold, invoiced) and column D I have how many days its been in that status
(2days, 3days ect
)
Sheet two, I have a grid. Top row are all my pay-codes and column A I have all my statuses
and the data in the middle are numbers representing number of days that I consider that status in that pay-code outstanding.
What Im trying to do is come up with something on sheet one in column E that would tell me if that order in that specific pay-code is outstanding by using that grid in sheet two
Thanks for all your help..
Lulu
Posted by Aladin Akyurek on June 03, 2001 2:29 PM
I have to guess the organization of your data. The following formula will do, if my guess is not off the mark.
In E2 enter: =IF(ISNUMBER(MATCH(B2,PAYCODES,0)),IF(VLOOKUP(C2,DATA,MATCH(B2,PAYCODES,0)+1,0)<=D2,"Outstanding","Not Oustanding"),"")
where I assume that you have on sheet1 order numbers in A from A2 on, the pay-codes in B from B2 on, the status values in C from C2 on, and finally the elapsed times in days in D from D2 on. And I also assume that, on sheet2, you have the pay-codes in row 1 from B1 on, the status values in A from A2 on, and the norm days fills up the matrix Status X Pay-codes.
In order to use the above formula, select the range of pay-codes on sheet2 and name the selection PAYCODES via the Name Box or via Insert|Name|Define and DATA the range that starts at A2 (that is, row 2) covering all norm numbers.
The formula compares by the way the day value for an order number with a corresponding norm value that is retrieved by VLOOKUP.
If this is way off what you want, post some snippet of your data in the follow-up.
Aladin
=============== What Im trying to do is an IF function .. I think. Sheet one column A I have order numbers, column B I have all pay-codes (Discover, Visa, Master Card etc. ) in column C I have status of the orders (canceled, in production, on hold, invoiced) and column D I have how many days its been in that status (2days, 3days ect ) Sheet two, I have a grid. Top row are all my pay-codes and column A I have all my statuses and the data in the middle are numbers representing number of days that I consider that status in that pay-code outstanding. What Im trying to do is come up with something on sheet one in column E that would tell me if that order in that specific pay-code is outstanding by using that grid in sheet two Lulu
Posted by Lulu on June 03, 2001 3:02 PM
IT WORKED!!!!!! THANK YOU THANK YOU THANK YOU!! : : What Im trying to do is an IF function .. I think. : : Sheet one column A I have order numbers, column B I have all pay-codes (Discover, Visa, Master Card etc. ) in column C I have status of the orders (canceled, in production, on hold, invoiced) and column D I have how many days its been in that status (2days, 3days ect ) : Sheet two, I have a grid. Top row are all my pay-codes and column A I have all my statuses and the data in the middle are numbers representing number of days that I consider that status in that pay-code outstanding. : What Im trying to do is come up with something on sheet one in column E that would tell me if that order in that specific pay-code is outstanding by using that grid in sheet two : Lulu
