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 theicon 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.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
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 A; 06-28-2011 at 11:30 PM.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
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.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
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)
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks