I have this file...........see enclosed........cell A1 looks blank........but the function ISBLANK in C1 indicates that the cell is NOT blank.
any ideas?
I have this file...........see enclosed........cell A1 looks blank........but the function ISBLANK in C1 indicates that the cell is NOT blank.
any ideas?
Last edited by welchs101; 06-29-2011 at 06:20 AM.
The ISBLANK() function can be fooled by things under the hood like autofilter extract remnants and I'm sure many other things.
You can test A1 this way to get a true answer:
=A1=""
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
That might be zero length null string., like when we use in formulas =""
If you use this in C1,
=A1=""
will give you TRUE. If you want to convert select the range go to;
Data >> Text to Columns
Then click finish.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
the problem is when i use the countA function it "counts" this blank cell as NOT blank.
not sure how to get around this.
anyone have any ideas?
If the range contains only text not numbers (mixed alphabets & numbers OK), you can use this formula.
=COUNTIF(A:A,"?*")
If the range mixed with numbers & texts try this;
=SUM(COUNTIF(A:A,{"?*","<9E300"}))
Last edited by Haseeb Avarakkan; 06-28-2011 at 11:30 PM.
that works! thanks!
when i use that formula in a defined name for plotting...........i am getting some really wierd results. i am enclosing a file for help explaining.
WHen you open the file do the following:
1) Change the named range "chrt_M_uat"
From:
OFFSET(Summary_UAT!$H$1,COUNTA(Summary_UAT!$H:$H)-1,0,-MIN(Summary_UAT!chrt_M_uat_len,COUNTA(Summary_UAT!$H:$H)-1),1)
To:
OFFSET(Summary_UAT!$H$1,SUM(COUNTIF(h:h,{"?*","<9E300"}))-1,0,-MIN(Summary_UAT!chrt_M_uat_len,SUM(COUNTIF(h:h,{"?*","<9E300"}))-1),1)
2) Next try to update the graph as follows:
click on one of the bars in the graph, then rt-click and goto select data. Then edit data for header1. Under teh series values change it from
Summary_UAT!$I$2:$I$9
to
Summary_UAT!chrt_M_uat_A
when you try to do this you get formula error. Now open up name manager and edit name "chrt_M_uat"...............its no longer the same? its somehow changed
can anyone explain it?
forgot to attach file
if i marked this as solved...........should i start a new thread?
You have valid date in Col_H, just use COUNT instead of COUNTA, not necessary for COUNTIF(H:H,{"?*".....)
Firstly define a name, Length
=MAX(1,COUNT(Summary_UAT!$H:$H))
Then change chrt_M_uat formula to;
=OFFSET(Summary_UAT!$H$1,1,,MIN(chrt_M_uat_len,Length))
Change $H$1 to $I$1, $J$1, $K$1, $L$1 for chrt_M_uat_A, B, C and D ranges.
See the attached.
thanks.
for some reason i keep getting an error
"A formula in this worksheet contains one or more invalid references"
any ideas why?
Change Length formula to;
=MAX(1,MIN(COUNT(Summary_UAT!$H:$H),chrt_M_uat_len))
Then change chrt_M_uat formula to;
=OFFSET(Summary_UAT!$H$1,1,,Length)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks