If or conditional formatting?
in [Functions]
|
Prev: BIN2DEC conversion for large binary numbers
Next: Pivot Tables - get a total different than the summarize field sett
From: Twiggy on 15 Dec 2009 12:37 Hi Folks Can some one please help me on a formula? I've been messing with it but can't quite get there. I have two coloumns with dates or blanks and I need a formula to bring back the status of the data A B Status date Blank 1p payable date date 1p paid Blank Blank 1o outstanding I have tried an if statement but can't get it to bring back the status of the blank cells, can anyone help please?
From: Jacob Skaria on 15 Dec 2009 12:50 Try the below formula... =CHOOSE(SUMPRODUCT(--(ISNUMBER(A1:B1)))+1,"1o outstanding", "1p payable","1p paid") -- Jacob "Twiggy" wrote: > Hi Folks > > Can some one please help me on a formula? I've been messing with it but > can't quite get there. > > I have two coloumns with dates or blanks and I need a formula to bring back > the status of the data > > A B Status > date Blank 1p payable > date date 1p paid > Blank Blank 1o outstanding > > I have tried an if statement but can't get it to bring back the status of > the blank cells, can anyone help please?
From: Twiggy on 15 Dec 2009 13:10 Thanks Jacob Not quite working though, it's bring back 1o outstanding to all the cells, any other ideas? I really appreciate your help Thanks "Jacob Skaria" wrote: > Try the below formula... > > =CHOOSE(SUMPRODUCT(--(ISNUMBER(A1:B1)))+1,"1o outstanding", > "1p payable","1p paid") > > -- > Jacob > > > "Twiggy" wrote: > > > Hi Folks > > > > Can some one please help me on a formula? I've been messing with it but > > can't quite get there. > > > > I have two coloumns with dates or blanks and I need a formula to bring back > > the status of the data > > > > A B Status > > date Blank 1p payable > > date date 1p paid > > Blank Blank 1o outstanding > > > > I have tried an if statement but can't get it to bring back the status of > > the blank cells, can anyone help please?
From: Jacob Skaria on 15 Dec 2009 13:18 Do you have the dates in excel date format? or else try the below version and copy down =CHOOSE(COUNTA(A1:B1)+1,"1o outstanding","1p payable","1p paid") -- Jacob "Twiggy" wrote: > Thanks Jacob > > Not quite working though, it's bring back 1o outstanding to all the cells, > any other ideas? I really appreciate your help > > Thanks > > "Jacob Skaria" wrote: > > > Try the below formula... > > > > =CHOOSE(SUMPRODUCT(--(ISNUMBER(A1:B1)))+1,"1o outstanding", > > "1p payable","1p paid") > > > > -- > > Jacob > > > > > > "Twiggy" wrote: > > > > > Hi Folks > > > > > > Can some one please help me on a formula? I've been messing with it but > > > can't quite get there. > > > > > > I have two coloumns with dates or blanks and I need a formula to bring back > > > the status of the data > > > > > > A B Status > > > date Blank 1p payable > > > date date 1p paid > > > Blank Blank 1o outstanding > > > > > > I have tried an if statement but can't get it to bring back the status of > > > the blank cells, can anyone help please?
From: Twiggy on 15 Dec 2009 13:21
Yey worked that time! Jacob, you are a legend, thank you so much!!!!!! "Jacob Skaria" wrote: > Do you have the dates in excel date format? > > or else try the below version and copy down > > =CHOOSE(COUNTA(A1:B1)+1,"1o outstanding","1p payable","1p paid") > > -- > Jacob > > > "Twiggy" wrote: > > > Thanks Jacob > > > > Not quite working though, it's bring back 1o outstanding to all the cells, > > any other ideas? I really appreciate your help > > > > Thanks > > > > "Jacob Skaria" wrote: > > > > > Try the below formula... > > > > > > =CHOOSE(SUMPRODUCT(--(ISNUMBER(A1:B1)))+1,"1o outstanding", > > > "1p payable","1p paid") > > > > > > -- > > > Jacob > > > > > > > > > "Twiggy" wrote: > > > > > > > Hi Folks > > > > > > > > Can some one please help me on a formula? I've been messing with it but > > > > can't quite get there. > > > > > > > > I have two coloumns with dates or blanks and I need a formula to bring back > > > > the status of the data > > > > > > > > A B Status > > > > date Blank 1p payable > > > > date date 1p paid > > > > Blank Blank 1o outstanding > > > > > > > > I have tried an if statement but can't get it to bring back the status of > > > > the blank cells, can anyone help please? |