MBRGriffin
New Member
- Joined
- Nov 11, 2014
- Messages
- 17
Hello...I have 3 formulas that I am having trouble getting to do what I want that have been in use for quite some time. Any quick assistance would be very much appreciated!
1- I am trying to get the last populated cell value in a series of non consecutive cells in a row to return. The formula I wrote below works to return the single value (which is all they have been to this point), however, now I need it to accommodate a double digit number. (Note, there will actually be about 25 cells in each row for this formula to look through)
=if(iserr(mid((ad23&aj23&ap23),len(ad23&aj23&ap23),1)),"",mid((ad23&aj23&ap23),LEN(ad23,aj23,ap23),1))
2 - I have a date field that is returning the 1/0/1900 date, which I know how to adjust in the custom cell formatting to show as blank, but I also have a formula in these cells adding 14 days or subtracting 7 days, which then it is no longer considered '0'. how can I make this show as a blank when the date value is actually '0' but my formula is making it return with +14 (b15 is where my +14 is coming from) or -7?
=if($f19="Supplier",if(t19>1,t19-7,""),"") --returns 1/7/1900
=if($j19>0,if(n19="1",0,if(n19="4",0,if(o19>p19,0,max($p19+$b$15,$Q19,R19)))),0) - returns 1/14/1900
3 - I am counting the number of times in a column that 'NIS' is returned.... but I now have instances where the NIS I am clearing in conditional formatting if it is not applicable because it is an internal document but it is still being counted. what do I need to adjust to make this work?
My count if formula is this: =countif($x$19:$x$298,"NIS)
Here Is the formula in the x column: =if($k19=0,0,if($o19=0,NS",if($n19="1","APPROVED",if($n19="2"RESUBMIT",if($n19="3","RESUBMIT",if($n9="4",INFORMATION",""))))))
and my conditional formatting in x column says this: =$I19="Internal" format as white.
Any help is very much appreciated!!!
1- I am trying to get the last populated cell value in a series of non consecutive cells in a row to return. The formula I wrote below works to return the single value (which is all they have been to this point), however, now I need it to accommodate a double digit number. (Note, there will actually be about 25 cells in each row for this formula to look through)
=if(iserr(mid((ad23&aj23&ap23),len(ad23&aj23&ap23),1)),"",mid((ad23&aj23&ap23),LEN(ad23,aj23,ap23),1))
2 - I have a date field that is returning the 1/0/1900 date, which I know how to adjust in the custom cell formatting to show as blank, but I also have a formula in these cells adding 14 days or subtracting 7 days, which then it is no longer considered '0'. how can I make this show as a blank when the date value is actually '0' but my formula is making it return with +14 (b15 is where my +14 is coming from) or -7?
=if($f19="Supplier",if(t19>1,t19-7,""),"") --returns 1/7/1900
=if($j19>0,if(n19="1",0,if(n19="4",0,if(o19>p19,0,max($p19+$b$15,$Q19,R19)))),0) - returns 1/14/1900
3 - I am counting the number of times in a column that 'NIS' is returned.... but I now have instances where the NIS I am clearing in conditional formatting if it is not applicable because it is an internal document but it is still being counted. what do I need to adjust to make this work?
My count if formula is this: =countif($x$19:$x$298,"NIS)
Here Is the formula in the x column: =if($k19=0,0,if($o19=0,NS",if($n19="1","APPROVED",if($n19="2"RESUBMIT",if($n19="3","RESUBMIT",if($n9="4",INFORMATION",""))))))
and my conditional formatting in x column says this: =$I19="Internal" format as white.
Any help is very much appreciated!!!
Last edited:
I appreciate all your help!!
