Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
skipping blank cells in dynamic chart
I am creating a dynamic chart that captures 24 months of data.
I'm using a vlookup to put data into the cells and it goes out 5 years. It looks like the dynamic chart code picks up the vlookup code 5 years out and tries to chart it. It also picks up months where no production accured. Is there away to ignore the vlookup if no data is in the cell? Here is a sample of my data: Dec 05 700 Jan 06 725 Feb 06 720 Mar 06 695 Apr 06 710 "blank" "blank" Jun 06 720 Jul 06 700 "blank" "blank" Aug 06 710 vlookup code goes out a total of 5 years. So, I have blank cells as of Jan 08 out to 2010. I only want to pickup the last 24 months, where there is data. I know this is complicated, so, thanks in advance for any help. -- Baltimore Ravens |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
skipping blank cells in dynamic chart
Chip Pearson has pages on his web site (http://cpearson.com) that show how
to compress a range with blank rows. Set this up in the next columns in the sheet and plot from this new range. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Ravens Fan" wrote in message ... I am creating a dynamic chart that captures 24 months of data. I'm using a vlookup to put data into the cells and it goes out 5 years. It looks like the dynamic chart code picks up the vlookup code 5 years out and tries to chart it. It also picks up months where no production accured. Is there away to ignore the vlookup if no data is in the cell? Here is a sample of my data: Dec 05 700 Jan 06 725 Feb 06 720 Mar 06 695 Apr 06 710 "blank" "blank" Jun 06 720 Jul 06 700 "blank" "blank" Aug 06 710 vlookup code goes out a total of 5 years. So, I have blank cells as of Jan 08 out to 2010. I only want to pickup the last 24 months, where there is data. I know this is complicated, so, thanks in advance for any help. -- Baltimore Ravens |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
skipping blank cells in dynamic chart
Hi Ravens,
If I understand your question you can use Data, Filter, AutoFilter. If the "blanks" are really blank then you choose Not Blank from the auto filter. If the "blanks" contain that word then you use custom filter from the AutoFilter drop down and choose does not equal and on the right enter blanks or "blanks" or whatever. The chart is set by default to plot only visible cells so your chart will not display the months for which data is missing. -- Cheers, Shane Devenshire "Ravens Fan" wrote: I am creating a dynamic chart that captures 24 months of data. I'm using a vlookup to put data into the cells and it goes out 5 years. It looks like the dynamic chart code picks up the vlookup code 5 years out and tries to chart it. It also picks up months where no production accured. Is there away to ignore the vlookup if no data is in the cell? Here is a sample of my data: Dec 05 700 Jan 06 725 Feb 06 720 Mar 06 695 Apr 06 710 "blank" "blank" Jun 06 720 Jul 06 700 "blank" "blank" Aug 06 710 vlookup code goes out a total of 5 years. So, I have blank cells as of Jan 08 out to 2010. I only want to pickup the last 24 months, where there is data. I know this is complicated, so, thanks in advance for any help. -- Baltimore Ravens |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
skipping blank cells in dynamic chart
Shane,
Thanks for your advice. It is the easier of the two suggestions. But, I still have a issue with the dynamic chart. Using the previous data sample I posted: Say I'm trying to capture 6 months of data and chart it. Using your idea, it only charts Apr 06 thru Aug 06 (only 4 months). I need it to go back to Feb 06 thru Aug 06 skipping the 2 blank (empty) cells. Any suggestions? I'm trying to make this as easy as possibly. I need to train someone, incase I get hit by a bus. Also, I'm trying to automate as much as possible. I have 100 charts that track 6 points of data. So, I'm trying to set it up that the charts update themselves each month. Thanks in advance for any help. -- Baltimore Ravens "ShaneDevenshire" wrote: Hi Ravens, If I understand your question you can use Data, Filter, AutoFilter. If the "blanks" are really blank then you choose Not Blank from the auto filter. If the "blanks" contain that word then you use custom filter from the AutoFilter drop down and choose does not equal and on the right enter blanks or "blanks" or whatever. The chart is set by default to plot only visible cells so your chart will not display the months for which data is missing. -- Cheers, Shane Devenshire "Ravens Fan" wrote: I am creating a dynamic chart that captures 24 months of data. I'm using a vlookup to put data into the cells and it goes out 5 years. It looks like the dynamic chart code picks up the vlookup code 5 years out and tries to chart it. It also picks up months where no production accured. Is there away to ignore the vlookup if no data is in the cell? Here is a sample of my data: Dec 05 700 Jan 06 725 Feb 06 720 Mar 06 695 Apr 06 710 "blank" "blank" Jun 06 720 Jul 06 700 "blank" "blank" Aug 06 710 vlookup code goes out a total of 5 years. So, I have blank cells as of Jan 08 out to 2010. I only want to pickup the last 24 months, where there is data. I know this is complicated, so, thanks in advance for any help. -- Baltimore Ravens |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
skipping blank cells in dynamic chart
Hi,
Sorry to take so long but I worked a very long day yeasterday. Let's suppose you are using Excel 2003. Suppose the data starts in A2 and goes to B60 with titles on row 1, with dates in column A and values in column B, also assume blank mean nothing in the cell. Select the entire range A1:B60, and press Ctrl L, then OK. Your data is a list. Highlight all the data and plot a chart. In cell B1 open the auto filter and choose Not Blanks, the last choice. Open the auto filter in cell A1 and choose Top 10, change the number 10 to 6 and click OK. Your chart should be plotting the last 6 months of data. -- Cheers, Shane Devenshire "Ravens Fan" wrote: Shane, Thanks for your advice. It is the easier of the two suggestions. But, I still have a issue with the dynamic chart. Using the previous data sample I posted: Say I'm trying to capture 6 months of data and chart it. Using your idea, it only charts Apr 06 thru Aug 06 (only 4 months). I need it to go back to Feb 06 thru Aug 06 skipping the 2 blank (empty) cells. Any suggestions? I'm trying to make this as easy as possibly. I need to train someone, incase I get hit by a bus. Also, I'm trying to automate as much as possible. I have 100 charts that track 6 points of data. So, I'm trying to set it up that the charts update themselves each month. Thanks in advance for any help. -- Baltimore Ravens "ShaneDevenshire" wrote: Hi Ravens, If I understand your question you can use Data, Filter, AutoFilter. If the "blanks" are really blank then you choose Not Blank from the auto filter. If the "blanks" contain that word then you use custom filter from the AutoFilter drop down and choose does not equal and on the right enter blanks or "blanks" or whatever. The chart is set by default to plot only visible cells so your chart will not display the months for which data is missing. -- Cheers, Shane Devenshire "Ravens Fan" wrote: I am creating a dynamic chart that captures 24 months of data. I'm using a vlookup to put data into the cells and it goes out 5 years. It looks like the dynamic chart code picks up the vlookup code 5 years out and tries to chart it. It also picks up months where no production accured. Is there away to ignore the vlookup if no data is in the cell? Here is a sample of my data: Dec 05 700 Jan 06 725 Feb 06 720 Mar 06 695 Apr 06 710 "blank" "blank" Jun 06 720 Jul 06 700 "blank" "blank" Aug 06 710 vlookup code goes out a total of 5 years. So, I have blank cells as of Jan 08 out to 2010. I only want to pickup the last 24 months, where there is data. I know this is complicated, so, thanks in advance for any help. -- Baltimore Ravens |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skipping blank cells when calculating time difference | Excel Discussion (Misc queries) | |||
Concatenation and skipping blank cells | Excel Worksheet Functions | |||
Skipping Blank Cells | Excel Discussion (Misc queries) | |||
Dynamic Named Range with blank cells | Excel Discussion (Misc queries) | |||
Skipping Blank Or Null Cells In a Lookup Function | Excel Worksheet Functions |