How can I make a Chart data series treat blanks as "Empty" cells

My Data Series contains blanks as the result of formula calculations.  The 
location and number of blanks can change with each recalculation as input 
criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
my cells still contain the formula which created the blank ("") cell and 
therefore, not truely empty.  I want to plot the series, about 7000 values, 
without the blanks showing up as zeros and ruining my trendline fit.  
Removing those rows is not an option as I have to retain the x-axis value as  
place holder on the chart.

Thanks for your help; nothing is as simple as it seems when you start

Dan
0
XLADLK (4)
8/29/2005 11:10:02 PM
excel.charting 18370 articles. 0 followers. Follow

12 Replies
684 Views

Similar Articles

[PageSpeed] 44

In article <48B9A1EC-2AD0-4CC3-8CD7-9AD36BE631CD@microsoft.com>, 
XLADLK@discussions.microsoft.com says...
> My Data Series contains blanks as the result of formula calculations.  The 
> location and number of blanks can change with each recalculation as input 
> criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> my cells still contain the formula which created the blank ("") cell and 
> therefore, not truely empty.  I want to plot the series, about 7000 values, 
> without the blanks showing up as zeros and ruining my trendline fit.  
> Removing those rows is not an option as I have to retain the x-axis value as  
> place holder on the chart.
> 
> Thanks for your help; nothing is as simple as it seems when you start
> 
> Dan
> 
Replace the "" with NA().  I don't know the effect on the trendline and  
if any whether it will be acceptable to you.  It's something you will 
have to check on and decide for yourself.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
0
8/30/2005 2:14:59 AM
That is certainly an improvement, thanks a lot.

Dan

"Tushar Mehta" wrote:

> In article <48B9A1EC-2AD0-4CC3-8CD7-9AD36BE631CD@microsoft.com>, 
> XLADLK@discussions.microsoft.com says...
> > My Data Series contains blanks as the result of formula calculations.  The 
> > location and number of blanks can change with each recalculation as input 
> > criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> > my cells still contain the formula which created the blank ("") cell and 
> > therefore, not truely empty.  I want to plot the series, about 7000 values, 
> > without the blanks showing up as zeros and ruining my trendline fit.  
> > Removing those rows is not an option as I have to retain the x-axis value as  
> > place holder on the chart.
> > 
> > Thanks for your help; nothing is as simple as it seems when you start
> > 
> > Dan
> > 
> Replace the "" with NA().  I don't know the effect on the trendline and  
> if any whether it will be acceptable to you.  It's something you will 
> have to check on and decide for yourself.
> 
> -- 
> Regards,
> 
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
> 
0
XLADLK (4)
8/30/2005 12:44:01 PM
Have you tried hiding the offending rows ?

Jo

--
Jon Quixle
-----------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2580
View this thread: http://www.excelforum.com/showthread.php?threadid=40030

0
8/30/2005 1:23:56 PM
There are several thousand of them scattered about and they change with each 
itereation so there would have to be an automatic way to do it.

Thanks for the reply

"Jon Quixley" wrote:

> 
> Have you tried hiding the offending rows ?
> 
> Jon
> 
> 
> -- 
> Jon Quixley
> ------------------------------------------------------------------------
> Jon Quixley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25803
> View this thread: http://www.excelforum.com/showthread.php?threadid=400300
> 
> 
0
XLADLK (4)
8/30/2005 2:15:08 PM
 > Removing those rows is not an option as I have to retain the x-axis
 > value as place holder on the chart.

So you're making a line chart and not an XY chart? This may play havoc 
with your trendline formula, since Excel calculates line chart 
categories as 1, 2, 3, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

XLADLK wrote:

> My Data Series contains blanks as the result of formula calculations.  The 
> location and number of blanks can change with each recalculation as input 
> criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> my cells still contain the formula which created the blank ("") cell and 
> therefore, not truely empty.  I want to plot the series, about 7000 values, 
> without the blanks showing up as zeros and ruining my trendline fit.  
> Removing those rows is not an option as I have to retain the x-axis value as  
> place holder on the chart.
> 
> Thanks for your help; nothing is as simple as it seems when you start
> 
> Dan
0
8/30/2005 3:52:08 PM
Hmm, that may be why the trendline isn't fitting just real well but I don't 
know of a better way.  It's like plotting the number of people injured by 
tornados each month for the last 20 years and then running a trendline 
through only the Cat 5's.  The next iteration might be only Cat 3 and above.  
A given month may or may not have a value depending on your criteria.  See my 
problem?  Suggestions are welcome.

Thanks

"Jon Peltier" wrote:

>  > Removing those rows is not an option as I have to retain the x-axis
>  > value as place holder on the chart.
> 
> So you're making a line chart and not an XY chart? This may play havoc 
> with your trendline formula, since Excel calculates line chart 
> categories as 1, 2, 3, etc.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> XLADLK wrote:
> 
> > My Data Series contains blanks as the result of formula calculations.  The 
> > location and number of blanks can change with each recalculation as input 
> > criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> > my cells still contain the formula which created the blank ("") cell and 
> > therefore, not truely empty.  I want to plot the series, about 7000 values, 
> > without the blanks showing up as zeros and ruining my trendline fit.  
> > Removing those rows is not an option as I have to retain the x-axis value as  
> > place holder on the chart.
> > 
> > Thanks for your help; nothing is as simple as it seems when you start
> > 
> > Dan
> 
0
XLADLK (4)
8/30/2005 4:44:02 PM
I guess I'd try filtering the data, extracting the Category 5s to a new 
sheet and analyzing that subset. Another option would be a pivot table, 
because you can sort the storms by cateogyr, and group the dates by 
month or year. I'd also put the data onto an XY chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


XLADLK wrote:

> Hmm, that may be why the trendline isn't fitting just real well but I don't 
> know of a better way.  It's like plotting the number of people injured by 
> tornados each month for the last 20 years and then running a trendline 
> through only the Cat 5's.  The next iteration might be only Cat 3 and above.  
> A given month may or may not have a value depending on your criteria.  See my 
> problem?  Suggestions are welcome.
> 
> Thanks
> 
> "Jon Peltier" wrote:
> 
> 
>> > Removing those rows is not an option as I have to retain the x-axis
>> > value as place holder on the chart.
>>
>>So you're making a line chart and not an XY chart? This may play havoc 
>>with your trendline formula, since Excel calculates line chart 
>>categories as 1, 2, 3, etc.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>XLADLK wrote:
>>
>>
>>>My Data Series contains blanks as the result of formula calculations.  The 
>>>location and number of blanks can change with each recalculation as input 
>>>criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
>>>my cells still contain the formula which created the blank ("") cell and 
>>>therefore, not truely empty.  I want to plot the series, about 7000 values, 
>>>without the blanks showing up as zeros and ruining my trendline fit.  
>>>Removing those rows is not an option as I have to retain the x-axis value as  
>>>place holder on the chart.
>>>
>>>Thanks for your help; nothing is as simple as it seems when you start
>>>
>>>Dan
>>
0
8/31/2005 12:35:42 AM
You are welcome.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <F9198E37-D2F6-4E53-BD6C-20DA4A347430@microsoft.com>, 
XLADLK@discussions.microsoft.com says...
> That is certainly an improvement, thanks a lot.
> 
> Dan
> 
{snip}
0
9/2/2005 5:17:20 PM
Have you found the answer yet?
I am trying to plot a data series also through months. If the month is not 
here yet, the cell is calculated to be blank, but the chart is plotting it as 
zero. Is this similar to your situation? How did you fix it?
Thanks

"XLADLK" wrote:

> My Data Series contains blanks as the result of formula calculations.  The 
> location and number of blanks can change with each recalculation as input 
> criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> my cells still contain the formula which created the blank ("") cell and 
> therefore, not truely empty.  I want to plot the series, about 7000 values, 
> without the blanks showing up as zeros and ruining my trendline fit.  
> Removing those rows is not an option as I have to retain the x-axis value as  
> place holder on the chart.
> 
> Thanks for your help; nothing is as simple as it seems when you start
> 
> Dan
0
groj (5)
1/5/2006 11:51:01 PM
Option 1: Replace the "" in your formula with NA().

Option 2: If you don't like the ugly #N/A or it messes up downstream 
calculations, use another column in which you have the NA() instead of 
the "".  Plot this new column but use the original for other work.

Option 3: Adapt the ideas behind
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
If you use COUNT() instead of COUNTA(), the solution will include only 
those cells with numbers and exclude the ""s.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <D4279D04-1449-4565-9038-4A7474C39744@microsoft.com>, 
groj@discussions.microsoft.com says...
> Have you found the answer yet?
> I am trying to plot a data series also through months. If the month is not 
> here yet, the cell is calculated to be blank, but the chart is plotting it as 
> zero. Is this similar to your situation? How did you fix it?
> Thanks
> 
> "XLADLK" wrote:
> 
> > My Data Series contains blanks as the result of formula calculations.  The 
> > location and number of blanks can change with each recalculation as input 
> > criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> > my cells still contain the formula which created the blank ("") cell and 
> > therefore, not truely empty.  I want to plot the series, about 7000 values, 
> > without the blanks showing up as zeros and ruining my trendline fit.  
> > Removing those rows is not an option as I have to retain the x-axis value as  
> > place holder on the chart.
> > 
> > Thanks for your help; nothing is as simple as it seems when you start
> > 
> > Dan
> 
0
1/11/2006 4:21:42 AM
Jon, I'm having the same types of trouble here, but I tried using an xy 
chart... It won't let me because my data source is a pivot table.  I don't 
have formula's with blanks or N/As but I am summarizing data that it linked 
to other sheets, and it reads that formula as a vaule...  Is there anything 
else I can do to get it to ignore the zero in my chart?  

"Jon Peltier" wrote:

> I guess I'd try filtering the data, extracting the Category 5s to a new 
> sheet and analyzing that subset. Another option would be a pivot table, 
> because you can sort the storms by cateogyr, and group the dates by 
> month or year. I'd also put the data onto an XY chart.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> 
> XLADLK wrote:
> 
> > Hmm, that may be why the trendline isn't fitting just real well but I don't 
> > know of a better way.  It's like plotting the number of people injured by 
> > tornados each month for the last 20 years and then running a trendline 
> > through only the Cat 5's.  The next iteration might be only Cat 3 and above.  
> > A given month may or may not have a value depending on your criteria.  See my 
> > problem?  Suggestions are welcome.
> > 
> > Thanks
> > 
> > "Jon Peltier" wrote:
> > 
> > 
> >> > Removing those rows is not an option as I have to retain the x-axis
> >> > value as place holder on the chart.
> >>
> >>So you're making a line chart and not an XY chart? This may play havoc 
> >>with your trendline formula, since Excel calculates line chart 
> >>categories as 1, 2, 3, etc.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>XLADLK wrote:
> >>
> >>
> >>>My Data Series contains blanks as the result of formula calculations.  The 
> >>>location and number of blanks can change with each recalculation as input 
> >>>criteria change.  I can make my Chart ignore an "Empty" cell easy enough but 
> >>>my cells still contain the formula which created the blank ("") cell and 
> >>>therefore, not truely empty.  I want to plot the series, about 7000 values, 
> >>>without the blanks showing up as zeros and ruining my trendline fit.  
> >>>Removing those rows is not an option as I have to retain the x-axis value as  
> >>>place holder on the chart.
> >>>
> >>>Thanks for your help; nothing is as simple as it seems when you start
> >>>
> >>>Dan
> >>
> 
0
psipg (2)
6/9/2008 2:14:01 PM
Don't make a pivot chart, make a regular chart. Select a blank cell outside 
the pivot table, start the chart wizard. In step 2, go to the Series tab, 
and one-by-one add and populate the series you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"psipg" <psipg@discussions.microsoft.com> wrote in message 
news:B6A72BC6-EE4C-470B-926D-08782A859837@microsoft.com...
> Jon, I'm having the same types of trouble here, but I tried using an xy
> chart... It won't let me because my data source is a pivot table.  I don't
> have formula's with blanks or N/As but I am summarizing data that it 
> linked
> to other sheets, and it reads that formula as a vaule...  Is there 
> anything
> else I can do to get it to ignore the zero in my chart?
>
> "Jon Peltier" wrote:
>
>> I guess I'd try filtering the data, extracting the Category 5s to a new
>> sheet and analyzing that subset. Another option would be a pivot table,
>> because you can sort the storms by cateogyr, and group the dates by
>> month or year. I'd also put the data onto an XY chart.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>>
>> XLADLK wrote:
>>
>> > Hmm, that may be why the trendline isn't fitting just real well but I 
>> > don't
>> > know of a better way.  It's like plotting the number of people injured 
>> > by
>> > tornados each month for the last 20 years and then running a trendline
>> > through only the Cat 5's.  The next iteration might be only Cat 3 and 
>> > above.
>> > A given month may or may not have a value depending on your criteria. 
>> > See my
>> > problem?  Suggestions are welcome.
>> >
>> > Thanks
>> >
>> > "Jon Peltier" wrote:
>> >
>> >
>> >> > Removing those rows is not an option as I have to retain the x-axis
>> >> > value as place holder on the chart.
>> >>
>> >>So you're making a line chart and not an XY chart? This may play havoc
>> >>with your trendline formula, since Excel calculates line chart
>> >>categories as 1, 2, 3, etc.
>> >>
>> >>- Jon
>> >>-------
>> >>Jon Peltier, Microsoft Excel MVP
>> >>Peltier Technical Services
>> >>Tutorials and Custom Solutions
>> >>http://PeltierTech.com/
>> >>_______
>> >>
>> >>XLADLK wrote:
>> >>
>> >>
>> >>>My Data Series contains blanks as the result of formula calculations. 
>> >>>The
>> >>>location and number of blanks can change with each recalculation as 
>> >>>input
>> >>>criteria change.  I can make my Chart ignore an "Empty" cell easy 
>> >>>enough but
>> >>>my cells still contain the formula which created the blank ("") cell 
>> >>>and
>> >>>therefore, not truely empty.  I want to plot the series, about 7000 
>> >>>values,
>> >>>without the blanks showing up as zeros and ruining my trendline fit.
>> >>>Removing those rows is not an option as I have to retain the x-axis 
>> >>>value as
>> >>>place holder on the chart.
>> >>>
>> >>>Thanks for your help; nothing is as simple as it seems when you start
>> >>>
>> >>>Dan
>> >>
>> 


0
jonxlmvpNO (4558)
6/9/2008 9:53:46 PM
Reply:

Similar Artilces:

Why does Checklinks create empty Batches in Inventory?
Everytime we run Checklinks on Inventory tables, for some reason the checklinks process creates several "empty" batches. Does anyone why this is occuring? The batches are created with usernames of users in GP and when we pull the the batch entry window it has the words "Checklinks" in the Comment field of the batch. We've tried deleting the batch from the "front-end" and we've even tried deleting out the batch from the "back-end" from the SY00500 posting def master table, which stores all the batch info in GP. But when we run checklinks,...

w2k8 R2 wbadmin recovery files with empty spaces problem
I cannot use "wbadmin start recovery" to specify files/folders containing empty spaces in -items or -recoverytarget parameter. I tried both single and double quote surrounding the path but doesn't work. Using old DOS 8.3 convention works but I'm trying to avoid the syntax. Anyone has clue how to support empty space in file/folder path? Thanks -- Fred ...

can IF leave a cell EMPTY ??
I'm attempting some simple (to me at least) data analysis and am running into trouble with zero values in the data array (region). These zero values (null string: "", actually) represent non-existant data that I'd like the chart to ignore, but as the array actually contains formulas the chart engine does not see an empty cell. Is it possible to use IF to cause a cell to appear Empty (ie, IsBlank(cell) == True) ? Something like: " = if (condition, reference, Empty) " My raw data consists of date shipped and date recieved; I have created an array (Mon, Tue...

How to SUM entire column, but only when adjacent cell is not empty
I know, this is kinda cheesy, but seems to work - I'm just missing the right criteria. I have a column of data that continues to grow, so I need to sum the entire thing just using C:C. However, the catch is, I want this sum total to be in the same column, but at the top. This of course causes a circular ref error. In column B is data that goes with the neighboring cell in column C. So, I want to use =SUMIF(B:B, <not-sure-of-criteria> ,C:C), but cannot find the correct criteria to say that if B contains anything, then add the C data. To avoid the circular referen...

import excel spreadsheet without empty rows (causing nulls)
Hi. I am trying to import an excel file into access. The import goes fine or so I thought. When I opened the table it had all my excel data plus over 9000 null valued records.So instead of getting a record set number of 200 records, I got over 9200. I have re imported the table after deleting empty rows (<-it felt pointless because there was nothing that I was deleting, I just figured there was something hidden) in the spreadsheet but it did the same thing. Is there something that attaches to the spreadsheet that causes null records? Please let me know if you know what has happened. Thank...

How to Export "Empty Tables" +queries+forms+macros for new data base ?
OK, we have our Access multiple table data base designed... sorta. The test data base was populated with fake data. How do we export "empty tables" along with all the queries, forms, macro's, code, etc... so we have the data base structure with no data? If we can get here we can then import the correct beginning data into the main table and start using the data base. thanks for any help. Mel Take a look at the TransferDatabase method: one of the parameters is StructureOnly. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "...

When report is empty show a standard phrase (newbie)
I have a report with subreports in it. When one of the subreports is empty I would like to have a standard phrase show up such as "There are no additons today". I am using Access 2003. Is this possible? Try a text box with this expression: =IIf([Report].[HasData], Null, "There are no additions.") If you have any other calculated fields, that might fail, since calculating non-existent fields results in #Error, and Access quits calculating the calculated controls when one is uncalculatable. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Acces...

Excel 2003: macro to add data to next empty row
When I run this macro with new data it overwrites instead of pasting to the next empty row in the destination sheet. Any suggestions? Sub Copy9365() 'Copy cells of cols A,D,F,H,J,L,M,N from rows containing "9365" in 'col B of the active worksheet (source sheet) to cols ',A-H of Tester (destination sheet) Dim DestSheet As Worksheet Set DestSheet = Worksheets("tester") Dim sRow As Long 'row index on source worksheet Dim dRow As Long 'row index on destination worksheet Dim sCount As Long sCou...

Empty cell in vlookup
hi, is there a way to have a vlookup see an empty cell and rather than filling it in with a 0 it could leave it blank? here's my vlookup formula =IF(A21="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)) Thank you in advance for your help :) =IF(A21="","",if(VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE))) -- Please click "yes" if this post helped you! Greatly appreciated Eva "confused" wrote: ...

CRM licenses gone
I've read the various posts about authentication issues causing the deployment manager to be empty; no licenses, or users. Our CRM installation was running just fine until this morning it suddenly returned authentication errors for all users. I've checked everything over and NOTHING was changed on either the CRM box, or the off-box SQL Server. If I login to the CRM box as my domain user account (admin on the CRM box) I get an empty deployment manager. If I login as the local admin account (the account used to install CRM), I get an error saying SQL Server is unavailable. Th...

Eliminating empty rows in Pivot table
Problem is that you can't simply filter out some data field because in some row fields they have values and at some they don't. So what I essentialy need is for those rows to exist when they contain data and to be erased when not containing data.I hope that it+s possible in excel 2007 Hi, Click on the immediate cell to the right of the Grant Total (for columns) cell, and press Auto Filter. This will get the auto filter drop down on the Grand Total column. Now filter out the Blanks. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishma...

Null vs empty
Wow - how silly. As posed early today, I asked in a query why I cannot find "Is Null" in a field that definitely contains no entry. The table was imported from an external source therefore the problem is somehow linked with the export/import feature as Is Null works on any other table. Now...I just did an Edit|Replace in one of the fields that contain empty contents with the following: Find What: null Replace with: Null then clicked Replace All It only took 30 sec. and all the empty fields now contain the word "Null". I am really stumped, I cannot query for Is Null and ye...

Bug and workaround: Office 2004 (vX too, possibly) Insert Object empty, also "Object library not registered", maybe more...
We have run into this bug: http://groups.google.com/group/microsoft.public.mac.office.word/browse_threa d/thread/78feb803f7b93202/ea663014dff78f62?tvc=2&q=%22object+library+not+reg istered%22+%22office+2004%22 We have also found a workaround. First, some background information. We have been having this issue for quite some time at our site, even before I started. So far as we know, it affects Office 2004 on Panther and Tiger with NFS home directories, and probably also Office vX. What happens is that after the first run of Word or Excel (or probably any Office 2004 product), we don&#...

list of fonts in toolbar for publisher is empty
I am using MS Office 2003. Only Publisher 2003 doesn't list fonts in the toolbar. What do I do to get the fonts listed in the toolbar? Did you look try modifying your toolbars? -- JoAnn Paules MVP Microsoft [Publisher] "Joel T" <Joel T@discussions.microsoft.com> wrote in message news:F4D0D14A-6278-4A36-AAF7-CB85B3DD1AC2@microsoft.com... >I am using MS Office 2003. Only Publisher 2003 doesn't list fonts in the > toolbar. What do I do to get the fonts listed in the toolbar? Right-click the toolbar, click formatting. -- Mary Sauer MSFT MVP http://offic...

Find Empty Distribution Lists
Hi, How can export to a file a list of all Distribution Lists that have no members? Thanks. You can use CSVDE to dump these to a CSV file. csvde -f emptyDistributionLists.csv -d "dc=my-domain-name,dc=com" -r "(&(objectClass=group)(|(groupType=2)(groupType=4)(groupType=8))(!member=*))" -l name *replace dc=my-domain-name,dc=com with your domain name and extension. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "markj" <markj@discussions.microsoft.com> ...

Angled borders/text and filling in empty cells
Hi, Two quick questions: 1. If you've got a cell formatted with "angled" text (say the orientation is 45 degrees) and you put a side border on that cell, the border also angles. If you want the cell above it, which may or may not have text in it, to have/match the "angled" border like the one below it, how do you do that? 2. If you have a cell range, say A1:G10 with some empty cells and some with data in them, is there a way to fill just the empty cells of that range with a * (or anything for that matter) easily? Thanks! Christina, 1. You can use the ...

binding points in a line chart when having empty cells
Hi, I need to make a linechart, but I do not have values in every cell in columns I am using. I would like the values I have to be binded together. Now they are binded if there are no emty cells between 2 points. I can not put in values in the empty cells, they have to be empty.. Hi, You can select the chart and use menus Tool > Options > Chart > Plot empty cells: Interpolated. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Ksenija" <Ksenija@discussions.microsoft.com> wrote in message news:87B9C258-2684-432E-A875-B26461016DCB@mi...

Empty Cells as Zeros
How can I get formulas to treat empty cells as if they held the value of 'zero' ? Thanks. Ted If they're really empty, then excel should treat them as 0's in arithmetic operations. If they have space characters (or other whitespace characters), then this isn't the case. And if they once contained formulas that evaluated to "" (an empty string), then converted to values, you'll have trouble with those until you clear the contents of those cells. So... What formula/function doesn't treat the empty cells as 0? And are you sure thos...

Emptying MSN Junk Folder thru Outlook 2003
I noticed that when I manually empty the MSN Junk Folder all the deleted mail shows back up inside the folder between 1 to 2 hrs after I emptied the folder. I would have to open IE and go to my MSN mail account and empty the folder there. I can't do it thru Outlook 2003. ...

Copy Data to Cells, but Stop at Next Non-Empty Cell
I have a spreadsheet with the following data: Column A Column B 05:00 Item A Item B Item C 07:00 Item A Item B 09:00 Item B Item C Each item corresponds with the time in the same block. What I want to accomplish is have the time move down until it comes to the next time. Shown below: 05:00 Item A 05:00 Item B 05:00 Item C 07:00 Item A 07:00 Item B 09:00 Item B 09:00 Item C So it copies the time down until it comes to the next, non-empty cel...

VBA access to a series, after it was assigned to an empty range
Hello, I'd like to get access to a chart series via vba. This works fine with ActiveChart.SeriesCollection(1).Values = "='" & ActiveWorkbook.Name & "'!" & Diagramm1.Range("Chart1_Series1").Value this is a reference to a named range. So, if I assign the series to a range with values I can change the series to another range afterwards. But if I once have assigned a series to an empty range I do not have access to the chart series any more. The error message is:"unable to set the values property of the series object" O...

how do i delete empty rows in excel?
e.g.: B A O P Q L P E Q D L E if i press Ctrl+End, it will jump to the E at bottom right corner then i delete the last row(entire) and press Ctrl+End, it jumps to the empty space(previously the last "E") how can I solve that problem? thx a lot! not using VBA, thx "charlie" wrote: > e.g.: > > B A O P > Q L P E > Q D L E > > if i press Ctrl+End, it will jump to the E at bottom right corner > then > i delete the last row(entire) > and press Ctrl+End, it jumps to the empty space(previously the last "E") > how can I solve that pr...

Replace Empty Node
I'm trying to use XPath to get a handle on one specific node so that I can replace it with an xml fragment I've loaded from another file. So if I have this XML: <issue> <id>1</id> <date>7/28/2006</date> <states/> </issue> If "myDoc" is the preceding XML loaded into an XmlDocument object, then this code is what I want to return the <states/> node above, but it returns null instead: XmlNode statesNode = doc.SelectSingleNode("//states"); I can't seem to get this XPath to return a node. Can someone he...

Can you do IsEmpty(Range)?
As I progress in VBA, I'm trying to make my macros more elegant and less heavy-handed. One of those problems is how I end Do loops with embedded If loops. I was previously just inserting a stop point in the column. Something like 'Do Until ActiveCell = "End"'. That works ok, until the range I'm working on is much shorter than my stop range. Is it possible to use IsEmpty to test a 15 cell block, and if they're all empty to end, or insert my end point? I've tried using the following code, but it doesn't work. Range("C1").Select Do Until ActiveCe...

Can't receive emails with empty body!?!
Since Outlook Express always crashed when trying to "break apart messages that are too large", I decided to set up Outlook 2000. But now, I have an even worst problem: I just DON'T RECEIVE emails that have an empty body!! I'm using my gMail account with Outlook (not that it really matters since it worked fine in Outlook Express!). What happens is, when I send myself an email with an empty body, it is sent, but I NEVER receive it! I can tell the message has been properly sent since I can read it from the web-based gMail, but for some reason, Outlook ignores it! I have no filt...