Graph to only include rows with data in

Hello, I want to make a graph which is based on a table which gets its data 
from other parts of my spreadsheet - so all the data is made from formulas.

Some of the rows at the bottom will be empty, and I want the graph to ignore 
these empty rows, but to include them if data is added at a later date.

Is there a way to point the graph to an area, but to tell the graph to 
ignore any rows where the formulas return a blank (or Icould make it zeros 
if that helps). I don't want acres of graph with no columns in either, so it 
would have to adjust automatically.

I'm OKish with VBA, but I'd prefer a solution that didn't use it if that's 
possible.

Thanks

Michelle 

0
12/9/2008 1:46:38 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
358 Views

Similar Articles

[PageSpeed] 21

Have a look here

http://spreadsheetpage.com/index.php/tip/handle_missing_data_in_a_line_chart


"Michelle" wrote:

> Hello, I want to make a graph which is based on a table which gets its data 
> from other parts of my spreadsheet - so all the data is made from formulas.
> 
> Some of the rows at the bottom will be empty, and I want the graph to ignore 
> these empty rows, but to include them if data is added at a later date.
> 
> Is there a way to point the graph to an area, but to tell the graph to 
> ignore any rows where the formulas return a blank (or Icould make it zeros 
> if that helps). I don't want acres of graph with no columns in either, so it 
> would have to adjust automatically.
> 
> I'm OKish with VBA, but I'd prefer a solution that didn't use it if that's 
> possible.
> 
> Thanks
> 
> Michelle 
> 
0
12/9/2008 2:18:30 PM
You could use autofilter to display Non-blanks (or Custom | Not Equal
To | 0 (zero) if you return zero in your formulae), and the graph will
adjust automatically. This is not dynamic, though - you would have to
re-apply the filter if your data changed.

Hope this helps.

Pete

On Dec 9, 1:46=A0pm, "Michelle" <mh_londonNOJ...@hotmail.com> wrote:
> Hello, I want to make a graph which is based on a table which gets its da=
ta
> from other parts of my spreadsheet - so all the data is made from formula=
s.
>
> Some of the rows at the bottom will be empty, and I want the graph to ign=
ore
> these empty rows, but to include them if data is added at a later date.
>
> Is there a way to point the graph to an area, but to tell the graph to
> ignore any rows where the formulas return a blank (or Icould make it zero=
s
> if that helps). I don't want acres of graph with no columns in either, so=
 it
> would have to adjust automatically.
>
> I'm OKish with VBA, but I'd prefer a solution that didn't use it if that'=
s
> possible.
>
> Thanks
>
> Michelle

0
pashurst (2576)
12/9/2008 2:21:29 PM
Excel 2007
Variable X-axis length with available data.
Dynamic, adjusts automatically.
No VBA.
Method will vary with chart and data type.
http://www.mediafire.com/file/yjdgkjynhwt/12_09_08.xlsx
0
12/9/2008 4:21:14 PM
Reply:

Similar Artilces:

Can I move down 12 rows in excel for every new entry
I am entering records of numbers which have formulas for calculation. I want to place these records every 12 rows and be able to automate the worksheet for our office personnel. How? HELP!! There can be anywhere from 50 to 150 records depending on how many batches are produced in manufacturing plant. right click sheet tab>view code>copy\paste this. If not column A (1) then change. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub Target.Offset(12).Select End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mickey&...

Re: Can you include a recipient using activeDocument.SendMail VB
See the article "How to send an email from Word using VBA� at: http://www.word.mvps.org/FAQs/InterDev/SendMail.htm Note that the Routing Slip method does NOT work in Office 2007 -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "rick" <sam@nowhere.org> wrote in message news:... > Message-ID: <1038928a8b81482f85e04738c78b0ec2@newspe.com> > X-Mailer: http://www.umailcampaign.com, ip log:150.228.4...

row area total in pivot table
After each row group, i want to displat total row for that group. How can i do it in pivot table. As column group totals are available bt not row group totals Thanks ...

Help: Formula put into entire row creates large file size
Hi, I have a spreadsheet where I would like one column to be the concatenation of two other columns. For example, in column C I would have =A1&B1. This excel file will be given to people and they will fill in values into columns A and B. The trick is, I don't know how many rows there will be, so I don't know how many rows in column C should have this formula. If I paste this formula into the entire column C, the excel file blows up to 3 megabytes. (It's only 15KB without this column). Are there ways for me to accomplish this more efficiently? Thanks for any help Are y...

How to specify a range using Row and Col Index instead of 'A1' notation?
How can I specify a range using Row and Col Index instead of 'A1' notation in VBA? Thanks, Keith dim myCell as range dim myRow as long dim myCol as long myrow = 33 mycol = 15 set mycell = activesheet.cells(myrow,mycol) or... dim myrng as range dim myRowS as long dim myColS as long dim myRowF as long dim myColS as long myrows = 33 mycols = 15 myrowf = 45 mycolf = 27 with activesheet set myrng = .range(.cells(myrows,mycols),.cells(myrowf,mycolf)) end with myRowS/F S=Start, f=Finish keithb wrote: > > How can I specify a range using Row and Col Index instead of ...

Problem- add-in Access Outlook Add-in for Data Collection & Publis
I have a problem with Outlook 2007 and the add-in Access Outlook Add-in for Data Collection and Publishing. This add-in worked when I first installed Outlook 2007 when installing Office 2007 Enterprise. The add-in created a sub-folder in my Inbox named Data Collection Replies and worked well until about 6 weeks ago. Now I can’t get the add-in to work at all even though it appears in the list of COM.adds in Outlook 2007. More perplexing is the error message I now receive EVERY time I click on any email message to read it. The message is titled ‘Custom UI Runtime Error in...

error reposting data
Hi everybody. I am adding image buttons to my web form programmatically and adding onclientclick for each button to call java function to save some info. When form is reposted I get an error message: An error has occurred because a control with id 'ctl00$ContentPlaceHolder1$ctl01' could not be located or a different control is assigned to the same ID after postback. If the ID is not assigned, explicitly set the ID property of controls that raise postback events to avoid this error. Probably I get this message, because on repost I do not recreate those image butto...

Comparing data from different spreadsheets????
Hi I have some a small problem with comparing data. I have data on to different sheet: Sheet A names account no. fred 12 Marie 54 An so on.... Sheet B also includes names and account no. but not identical to the ones in sheet A. Since the list is endless....more or less is is most convinient to keep the two sheets. The problem: I wish to find out which names and accounts are repeated in both A and B. If possible as an output in a blanc sheet C. Hopefully someone can assist me. I have tried to use countif and if but have not succeded. Hi One way would be...

Charting data as 'boxes'
Hi All I have an x-y chart (y is a date axis) and a series of events happening along the x-axis. The x-axis is the position along a datum, and I'd like to show a 'box' starting at one x value through to a second x-value to define where the event is happening; then the top and bottom of the box define the start and finish dates. I can store the data in a table easily enough, [ID;Startx;Endx;Starty;Endy] but is it then possible to use it to define the four corners of a box and get it to draw a series of 'boxes' on a chart ?? Hope this makes sense ! Regards John P J...

Need inserted lines to print in correct position on Excel graphs
The aim lines and trend lines that I inserted into the Excel graph appear in the correct position on my monitor screen, but when I print the graph they appear higher than when seen on the screen. How can I get the printed lines to appear in the correct position? Hi, Did you use shapes to draw the lines? You could try changing the Move and cell property. But the best solution would be to create the lines using dummy series. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Lavonne" <Lavonne@discussions.microsoft.com> wrote in message news:D4CD...

Path changes affecting external data query
I have an access database and an excel file (for the publication of various outputs of the database) which I update via external data query. Both of these files live on a datastick for portability. The issue I have is that the drive letter is different from one PC to another and manual editing of the SQL in excel is required to simply change the drive letter from E to F or whatever. Is there a way to get around this? I have tried to remove the drive letter altogether thinking that perhaps excel will look in the files home directory first for the DB but this doesnt work. TIA Tony This ...

last row or record
there is no ID in this table. table1: col1 col2 col3 ----- ------ ----- aaa bbb ddd aab jjj fff ccc qqq ppp like to get this output: col1 col2 col3 ----- ------ ----- ccc qqq ppp -- Message posted via http://www.accessmonster.com sorry to buttoning in between... try this... create form using table, and in form on load properties. DoCmd.GoToRecord , , acLast regards "igg via AccessMonster.com" wrote: > there is no ID in this table. > table1: > col1 col2 col3 > ----- ------ ----- > aa...

Frx NP rows and Excel export
In versions of Frx6.7 SP9 and lower when a column is made NP in Frx, when the report genrated is exported to Excel the NP column is not transferred to Excel. In Frx6.7 SP10 I have now found that NP columns now export to Excel and are displayed as hidden columns in Frx. As far as I can tell is is supposed to be function enhancement rather than a bug. Can someone confirm this for me? Also - is there a way somehow to ensure it works in the way it did previosuly (I ask this I my client has multiple complex Excel sheets that link to the columns in this exported sheet - now due to this '...

Transfer form data
In access 2000, how do I transfer form data to MS Word?. I would like to use something like transferSpreadsheet which I use often to transfer data to Excel. I would prefer not to create an Access report and then use "Publish with MS Word". This method does NOT faithfully reproduce the report. Thanks -- Shell "Shell" wrote: > In access 2000, how do I transfer form data to MS Word?. I would like to use > something like transferSpreadsheet which I use often to transfer data to > Excel. > > I would prefer not to create an Access report and then us...

Printing only non-blank rows
Hi all, 1) Is there a command I can use or a macro code so that I only print non-blank rows? 2) Similar problem: is there a command or macro so that I only print rows that do not contain error values? Thanks for any ideas Matthew --- Message posted from http://www.ExcelForum.com/ An alternative to VBA is to apply an Advanced Filter and filter for those rows that only contain data. Try this: 1. An empty cell below your data (say E100), put: =COUNTA(2:2)>0 where row 2 is the 1st row of data and row 1 = headers. 2. Select your data and go to Data > Filter > Advanced F...

Help with transferring data in one column to multiple columns.
I have a list of data that ranges from one cell in column A to over 400 cells (this can change as well) in column A. At the moment I have to manually cut and paste the first 50 cell from column A to Column B, then manually cut and paste the next 50 from column A to column C, then the next 50 into column D and so on. The above should be flexible where I can vary the number of cells to be cut and pasted. Is there a way that the above can be automated? Any help offered would be appreciated. -- Thank U and Regards Ann This code should do it for you - goes into a regular code module. ...

Excel 2000
In Excel 2000, can you define rows and columns as fixed heights and widths in centimetres for printing purposes? i.e. I have formatted stationery that will be printed to from Excel and this expects cell widths/heights to remain unchanged. So far, when amending cell properties, I am not being given cm as an option. Cheers Mark Try to format your excel file to match the formatted stationery by trials until you get a perfect print. This should be ok considering the fact that it will be a one time exercise. Thanks KB Excel friggin sucks for printing; I reccomend looking at Access Reports...

Row Limit in Excel
I work a lot with excel and I know that the row limit is 65,536, but I need more than that. Is there any way to make the number of rows infinite or at least to give me a certain amount of more rows. If there is please let me know. Thank you. -- Please help Molly, that is all there is, per sheet. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Molly" <molly_carols@yahoo.com> wrote in message news:FD2C7921-E249-4...

Data Markers
Can I change data markers so that it reflects the conditional formatting on that cell? For example if the data in column c is being plotted and the conditional formatting I have on the cells are if column D = "Y" can I have it so that those markers are plotted with a circle marker and the rest with a square marker? Thanks! Hi, Have a look at Jon's page for conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy ivy_gayle wrote: > Can I change data markers so that it reflects the conditional formatting on > that cell? For exampl...

WBS in Data Analysis or Reports
Is there a way to get the WBS codes into a Data Analysis report? If not, what table/view is the 'WBS' ifield n the Database? This is so that I can create a SQL query to combine the WBS column into a report. Hi Bubba, Please look for TaskOutlineLevel and TaskOutlineNumber fields in MSP_EpmTask_UserView table. Thanks Chak pVector Technologies http://www.pvectortech.com http://www.epmcentral.com On Feb 2, 10:35=A0am, Bubba <Bu...@discussions.microsoft.com> wrote: > Is there a way to get the WBS codes into a Data Analysis report? > > If not, what tabl...

dumb data type question
i have a field [SortOrder] which is data type - number field size - decimal format - standard precision - 8 decimal places - 2 however, whn in datasheet view, if if enter a numer (let's say; 150.10, Access immediately changes it to 150.00 Sorry to say, i give up, could someone tell me what i am doing wrong? -mark (when i first created this atble, the field [SortOrder] had an incorrect data type of text; which was subsequently changed to number after a small amount of data had already been entered...) Mark A side curiosity ... if the field ...

Getting Data From MS Access Database
Is this possible? I have MS Access DB on the file server and wanting to query directly from ms excel. Hoping for your fast response. Me absolutely,you can make use of ADO to get data from Access DB via VBA code OR you can click "data" ->"import externl data"->"database query" so you can choose the data source and set up some query conditions. "M P" <mark@textguru.ph> д����Ϣ����:uSifuSgLGHA.3424@TK2MSFTNGP12.phx.gbl... > Is this possible? I have MS Access DB on the file server and wanting to > query directly from ms excel. Hopi...

How can i stop same data being repeated in a column
I have a list of contract numbers relating to application numbers or payments. they are in the format nnnnnnan or nnnnnnpn. The columns are fixed to this format only. If they are entered with the a or p in the wrong place or if they have been left out completely an error message will appear to alert the user. I want to know how to alert the user if they enter an application or a payment number that has already been entered. ie if they enter 022079a4 but that same application has been entered else where in the column. Hope you can help Ru Hello See Chip Pearson's http://www.cpearson....

In a report, how do I unlink a data field with its label?
I am trying to separately adjust the width of a data field and its associated label. I could do this in Access 2003 by clicking a number of times on the data field and it would allow me to widen the data field without changing the width of its label. How can I do this in Access 2007? When I open a form in design view, click on a textbox (with an attached label), then drag the handle in the middle of the box, the textbox control changes size, while the label control remains the same. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received p...

Problems XSLT Transformation of XML Data to EXCEL 2000
Does Office Excel 2000 support XSLT transformation of data from XML cause am having problem when the attachment is opened in the client side if the Excels version is in Office 2000 and also its works fine if the Office Excel Version 2002 and up when attachment file is open via browser the data is in a single line of string in the first row of the excel worksheet Glenn Gomez wrote: > Does Office Excel 2000 support XSLT transformation of data from XML cause am > having problem when the attachment is opened in the client side if the Excels > version is in Office 2000 > >...