Dynamic Range Charts Across Worksheets?

Hi,

I'm trying to create a graph of a dynamic range of cells. In order t
do this, I defined two named functions (X and Y) to graph. Everythin
works great, except that I need to do this for a lot of different set
of data on different worksheets. The parameters for each worksheet ar
the same, but I'm trying to find a way to get around defining a ne
named function for every single worksheet.

Is there some way to set the named function to refer to the workshee
that the graph is located in, instead of a specific worksheet?

Thanks

--
Message posted from http://www.ExcelForum.com

0
8/12/2004 10:54:48 PM
excel.charting 18370 articles. 0 followers. Follow

12 Replies
910 Views

Similar Articles

[PageSpeed] 10

A few choices.  None very good.

(1) Good news/bad news...
Good news: There is indeed a way to create a name that refers to data 
on the current sheet.  See the last paragraph in http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html

Bad news: Unfortunately, XL's charting module won't use such a name.  
Sorry.

(2) Use the INDIRECT function to create the a named formula that refers 
to the 'active sheet.'  However, use of the INDIRECT function in a 
named formula used in a chart is somewhat flaky in how one sets it up.  
Worse, the chart on each worksheet may not actually reflect the data on 
that sheet until one clicks in a cell or forces a recalculation (with 
F9 on a Windows machine).

(3) Create a single chart and use the INDIRECT function to let the user 
select which worksheet data are graphed.

(4) Write a VBA macro that sets up all the worksheet names as needed.

(5) If the different worksheets represent data for different values of 
the same 'field' (weeks, products, etc.), you could put everything in a 
single table with an additional field that is the value on which the 
worksheets were created.  Then, create a PivotChart (or a regular 
chart) based on this single table.

You may be best off with either (4) or (5).

-- 
Regards,

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

In article <GerbilGod7.1awe1a@excelforum-nospam.com>, GerbilGod7 
<<GerbilGod7.1awe1a@excelforum-nospam.com>> says...
> Hi,
> 
> I'm trying to create a graph of a dynamic range of cells. In order to
> do this, I defined two named functions (X and Y) to graph. Everything
> works great, except that I need to do this for a lot of different sets
> of data on different worksheets. The parameters for each worksheet are
> the same, but I'm trying to find a way to get around defining a new
> named function for every single worksheet.
> 
> Is there some way to set the named function to refer to the worksheet
> that the graph is located in, instead of a specific worksheet?
> 
> Thanks!
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
8/12/2004 11:39:52 PM
okay, so here is one of my Named functions:

=OFFSET('X'!$J$1,'X'!$S$2,0,'X'!$S$7-'X'!$S$2,1)

if I were to use (2), would I just replace the 'X'!$J$1 with
INDIRECT(!$J$1), and so forth?

As far as (4) goes, that sounds like a good option. Are there any
example macros that I could adapt to my purposes?

Thanks very much for the help!


---
Message posted from http://www.ExcelForum.com/

0
8/13/2004 12:11:47 AM
A simple macro might go like this:

Sub NameSheetRange()
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
     ' adjust range address and range name in formula below
     ws.Range("A1:A10").Name = "'" & ws.Name & "'!" & "Range_1"
     ' repeat for all needed ranges
   Next
End Sub

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

GerbilGod7 < wrote:
> okay, so here is one of my Named functions:
> 
> =OFFSET('X'!$J$1,'X'!$S$2,0,'X'!$S$7-'X'!$S$2,1)
> 
> if I were to use (2), would I just replace the 'X'!$J$1 with
> INDIRECT(!$J$1), and so forth?
> 
> As far as (4) goes, that sounds like a good option. Are there any
> example macros that I could adapt to my purposes?
> 
> Thanks very much for the help!
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
8/13/2004 1:59:26 AM
okay, I gave this a shot, and excel doesn't like it:

>>>>>>>>>>>>>>>>>

Sub DynamicGraph()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Range(ws.Name & "-EjectaX").Name = "OFFSET('" & ws.Name & "'!$J$1,'
& ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"
ws.Range(ws.Name & "-RampartX").Name = "OFFSET('" & ws.Name 
"'!$J$1,'" & ws.Name & "'$S$7,0,'" & ws.Name & "'!$S$6-'" & ws.Name 
"'!$S$7,1)"

End Sub

>>>>>>>>>>>>>>>>>>

The idea was that it would create two new named dynamic ranges for eac
worksheet, with names like "(worksheet name)-EjectaX". I think I messe
up the naming conventions, but I can't find a definition for th
parameter.

Once I get this macro to work, how do I get it to create graphs in eac
worksheet that refer to the range in each worksheet?

Thanks

--
Message posted from http://www.ExcelForum.com

0
8/14/2004 4:08:10 AM
Take a look at Quick Charts at http://www.add-ins.com/quickcharts.htm - it
is designed to do dynamic charts across multiple worksheets.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"GerbilGod7 >" <<GerbilGod7.1awe1a@excelforum-nospam.com> wrote in message
news:GerbilGod7.1awe1a@excelforum-nospam.com...
> Hi,
>
> I'm trying to create a graph of a dynamic range of cells. In order to
> do this, I defined two named functions (X and Y) to graph. Everything
> works great, except that I need to do this for a lot of different sets
> of data on different worksheets. The parameters for each worksheet are
> the same, but I'm trying to find a way to get around defining a new
> named function for every single worksheet.
>
> Is there some way to set the named function to refer to the worksheet
> that the graph is located in, instead of a specific worksheet?
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
8/14/2004 1:32:58 PM
I've looked at that already, and it would be great... unfortunately, a
a poor college undergrad working on NASA grant money, I can't reall
afford it.

:( 

Thanks thoug

--
Message posted from http://www.ExcelForum.com

0
8/14/2004 4:25:28 PM
After some tweaking, I've gotten it to work, sorta... Here's what 
use:

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Nam
& "'!$J$1,'" & ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name 
"'!$S$2,1)"

In each worksheet, it creates a named range with this name:

EjectaX   ...[bunch of spaces]... (worksheet name)

I'm not sure why it's doing this; I've tried:

"'" & ws.Name & "'!EjectaX" etc, but it doesn't make a difference.

How do I get it to name these ranges properly, and ultimately grap
them

--
Message posted from http://www.ExcelForum.com

0
8/14/2004 8:39:32 PM
The best way, as far as I am concerned, to get the correct code is to 
turn on the macro recorder, create a named formula, turn off the macro 
recorder and replace the hard-coded sheet name with ws.name (with the 
associated adjustments of double quotes).  As a precaution, I would 
also ensure that the sheet name has a space in it.  That way the XL-
generated code will have the very important single quotes already in 
the appropriate places.


-- 
Regards,

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

In article <GerbilGod7.1azx3u@excelforum-nospam.com>, GerbilGod7 
<<GerbilGod7.1azx3u@excelforum-nospam.com>> says...
> After some tweaking, I've gotten it to work, sorta... Here's what I
> use:
> 
> ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Name
> & "'!$J$1,'" & ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name &
> "'!$S$2,1)"
> 
> In each worksheet, it creates a named range with this name:
> 
> EjectaX   ...[bunch of spaces]... (worksheet name)
> 
> I'm not sure why it's doing this; I've tried:
> 
> "'" & ws.Name & "'!EjectaX" etc, but it doesn't make a difference.
> 
> How do I get it to name these ranges properly, and ultimately graph
> them?
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
8/14/2004 9:40:52 PM
As the man says, turn on the macro recorder. I discovered the critical 
typo: Your formula leaves out the = in front of OFFSET. Either of these 
work:

     ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
         "=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
         & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

     ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
         "=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
         & ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"

I did both A1 and RC notation, because the recorder uses RC. I converted 
to A1 to see whether that was the problem, and both worked. Then I 
noticed the missing "=".

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

Tushar Mehta wrote:
> The best way, as far as I am concerned, to get the correct code is to 
> turn on the macro recorder, create a named formula, turn off the macro 
> recorder and replace the hard-coded sheet name with ws.name (with the 
> associated adjustments of double quotes).  As a precaution, I would 
> also ensure that the sheet name has a space in it.  That way the XL-
> generated code will have the very important single quotes already in 
> the appropriate places.
> 
> 

0
8/15/2004 10:14:45 PM
Jon,
Is it possible to refer to a set number of cells that shifts down each
day?  For example, I am plotting VHLC in four columns on a worksheet
and on a separate worksheet I am graphing the latest 20 weeks worth of
data.  Each day the graph advances one day (but drops the oldest day
off the chart) when the new data is entered.  So the chart remains the
same size, but the data moves across from right to left as the days go
by and new data is entered.
Then this is done for the next worksheet and another chart is added
below the first, etc.
I have all the code working for creating the charts and formatting
them correctly, but the dynamic ranges are new to me.  So far what
I've researched appears to define the starting cell in the code (...&
ws.Name & "'!$J$1...) and goes down from there.  I guess I'm wanting
my range to advance down the data so I can keep my old numbers (but
not chart them after 20 weeks).
Hope this makes sense, because it's sure tough to put into words for
me! :)
Thanks,
Jim


Jon Peltier <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message news:<#CB5HVxgEHA.3864@TK2MSFTNGP10.phx.gbl>...
> As the man says, turn on the macro recorder. I discovered the critical 
> typo: Your formula leaves out the = in front of OFFSET. Either of these 
> work:
> 
>      ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
>          "=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
>          & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"
> 
>      ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
>          "=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
>          & ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"
> 
> I did both A1 and RC notation, because the recorder uses RC. I converted 
> to A1 to see whether that was the problem, and both worked. Then I 
> noticed the missing "=".
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> Tushar Mehta wrote:
> > The best way, as far as I am concerned, to get the correct code is to 
> > turn on the macro recorder, create a named formula, turn off the macro 
> > recorder and replace the hard-coded sheet name with ws.name (with the 
> > associated adjustments of double quotes).  As a precaution, I would 
> > also ensure that the sheet name has a space in it.  That way the XL-
> > generated code will have the very important single quotes already in 
> > the appropriate places.
> > 
> >
0
8/26/2004 12:07:13 AM
Jim -

I have an example on my site that shows how to chart the last 12 months 
of data. The first and last plotted point in the chart both move through 
the data. You can adapt it to show the last 20 weeks.

   http://peltiertech.com/Excel/Charts/DynamicLast12.html

On Tushar Mehta's site (http://tushar-mehta.com) there are several 
examples of dynamic ranges that can be used for different ways to 
represent a changing data set.

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

Jim wrote:

> Jon,
> Is it possible to refer to a set number of cells that shifts down each
> day?  For example, I am plotting VHLC in four columns on a worksheet
> and on a separate worksheet I am graphing the latest 20 weeks worth of
> data.  Each day the graph advances one day (but drops the oldest day
> off the chart) when the new data is entered.  So the chart remains the
> same size, but the data moves across from right to left as the days go
> by and new data is entered.
> Then this is done for the next worksheet and another chart is added
> below the first, etc.
> I have all the code working for creating the charts and formatting
> them correctly, but the dynamic ranges are new to me.  So far what
> I've researched appears to define the starting cell in the code (...&
> ws.Name & "'!$J$1...) and goes down from there.  I guess I'm wanting
> my range to advance down the data so I can keep my old numbers (but
> not chart them after 20 weeks).
> Hope this makes sense, because it's sure tough to put into words for
> me! :)
> Thanks,
> Jim
> 
> 
> Jon Peltier <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message news:<#CB5HVxgEHA.3864@TK2MSFTNGP10.phx.gbl>...
> 
>>As the man says, turn on the macro recorder. I discovered the critical 
>>typo: Your formula leaves out the = in front of OFFSET. Either of these 
>>work:
>>
>>     ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
>>         "=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
>>         & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"
>>
>>     ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
>>         "=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
>>         & ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"
>>
>>I did both A1 and RC notation, because the recorder uses RC. I converted 
>>to A1 to see whether that was the problem, and both worked. Then I 
>>noticed the missing "=".
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Tushar Mehta wrote:
>>
>>>The best way, as far as I am concerned, to get the correct code is to 
>>>turn on the macro recorder, create a named formula, turn off the macro 
>>>recorder and replace the hard-coded sheet name with ws.name (with the 
>>>associated adjustments of double quotes).  As a precaution, I would 
>>>also ensure that the sheet name has a space in it.  That way the XL-
>>>generated code will have the very important single quotes already in 
>>>the appropriate places.
>>>
>>>

0
8/26/2004 12:38:46 PM
Thanks Jon, I just went to your site and I think I can adapt it quite easily.
Thank you for your help again, you guys do a wonderful job on this newsgroup!!
Jim
Jon Peltier <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message news:<uJHdom2iEHA.3008@TK2MSFTNGP10.phx.gbl>...
> Jim -
> 
> I have an example on my site that shows how to chart the last 12 months 
> of data. The first and last plotted point in the chart both move through 
> the data. You can adapt it to show the last 20 weeks.
> 
>    http://peltiertech.com/Excel/Charts/DynamicLast12.html
> 
> On Tushar Mehta's site (http://tushar-mehta.com) there are several 
> examples of dynamic ranges that can be used for different ways to 
> represent a changing data set.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> Jim wrote:
> 
> > Jon,
> > Is it possible to refer to a set number of cells that shifts down each
> > day?  For example, I am plotting VHLC in four columns on a worksheet
> > and on a separate worksheet I am graphing the latest 20 weeks worth of
> > data.  Each day the graph advances one day (but drops the oldest day
> > off the chart) when the new data is entered.  So the chart remains the
> > same size, but the data moves across from right to left as the days go
> > by and new data is entered.
> > Then this is done for the next worksheet and another chart is added
> > below the first, etc.
> > I have all the code working for creating the charts and formatting
> > them correctly, but the dynamic ranges are new to me.  So far what
> > I've researched appears to define the starting cell in the code (...&
> > ws.Name & "'!$J$1...) and goes down from there.  I guess I'm wanting
> > my range to advance down the data so I can keep my old numbers (but
> > not chart them after 20 weeks).
> > Hope this makes sense, because it's sure tough to put into words for
> > me! :)
> > Thanks,
> > Jim
> > 
> > 
> > Jon Peltier <DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote in message news:<#CB5HVxgEHA.3864@TK2MSFTNGP10.phx.gbl>...
> > 
> >>As the man says, turn on the macro recorder. I discovered the critical 
> >>typo: Your formula leaves out the = in front of OFFSET. Either of these 
> >>work:
> >>
> >>     ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
> >>         "=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
> >>         & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"
> >>
> >>     ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
> >>         "=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
> >>         & ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"
> >>
> >>I did both A1 and RC notation, because the recorder uses RC. I converted 
> >>to A1 to see whether that was the problem, and both worked. Then I 
> >>noticed the missing "=".
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>Tushar Mehta wrote:
> >>
> >>>The best way, as far as I am concerned, to get the correct code is to 
> >>>turn on the macro recorder, create a named formula, turn off the macro 
> >>>recorder and replace the hard-coded sheet name with ws.name (with the 
> >>>associated adjustments of double quotes).  As a precaution, I would 
> >>>also ensure that the sheet name has a space in it.  That way the XL-
> >>>generated code will have the very important single quotes already in 
> >>>the appropriate places.
> >>>
> >>>
0
8/27/2004 8:31:28 PM
Reply:

Similar Artilces:

Justify text across multiple columns
I have used Edit, Fill, Justify to fill rows with text within the boundaries of a set number of columns, but the text in each row is left justified. How do I justify the text (even spacing) in each row. If I highlight all of the text (multiple columns and rows) and then select Format, Cells, Alignment, Justify; Excel jams all the text into the first column. I know I could use a text box, but I'd prefer not to. Any help would be appreciated. Thanks. -Dave Hi Dave, Select the cells you want to center on. Under Format > Alignment > Horizontal drop down > Center across se...

Can I use named range in data range box when creating pie chart?
I have 75 pie charts to create today. Is there a way to use named ranges in the data range box to speed this up? Yes. Just type in the name. -- George Nicholson Remove 'Junk' from return address. "BJackson" <BJackson@discussions.microsoft.com> wrote in message news:0DE9A0AD-C3BA-4242-B3BA-3CC3F0D87894@microsoft.com... >I have 75 pie charts to create today. Is there a way to use named ranges >in > the data range box to speed this up? Thank you! I was making it too difficult, thinking I needed to include the Sheet name along with the name range. ...

Chart Total
I have a chart which displays 2 suppliers and their spend by month. I want a 3rd line which shows the total spend of these 2 vendors combined however I cannot figure out a method to do it. Any ideas? Typically, you would calculate this in the Row Source property of the chart. If you can't figure out how to do this, reply back with your current Row Source. -- Duane Hookom MS Access MVP "Ryan" <Ryan@discussions.microsoft.com> wrote in message news:12051B71-2291-425D-BC90-F621F134956E@microsoft.com... > I have a chart which displays 2 suppliers and th...

Line splits across 2 pages
In one of my access reports I have there is one line that splits across to pages where the top of the letters appear on page 1 and the bottom of the letters appear on page 2 Any idea why it happens and how to avoid it? Thank you, Samuel Samuel wrote: > In one of my access reports I have there is one line that splits > across to pages where the top of the letters appear on page 1 and the > bottom of the letters appear on page 2 > > Any idea why it happens and how to avoid it? > > Thank you, > Samuel Go into design mode and shorten that line. -- Joseph Mee...

Waterfall Charts
Does anybody have a step by step guide to creating waterfall charts? I have a training session to run on wednesday and I haven't got a clue where to start! Thanks in advance Diane Hi Diane, Here are a couple of resources, (http://peltiertech.com/Excel/Charts/Waterfall.html) (http://www.tushar-mehta.com/) Cheers Andy Diane wrote: > Does anybody have a step by step guide to creating > waterfall charts? I have a training session to run on > wednesday and I haven't got a clue where to start! > > Thanks in advance > > Diane -- Andy Pope, Microsoft MVP...

Create an identical Chart Worksheet in Excel 2003
Hi: I used the F11 key to create a chart, which inserts just a chart as a new worksheet and gives it a default name, such as chart 2. My question is, can I create an identical chart as a new worksheet. This wouldn't be a copy, rather, a duplicate that is pulling information from the same location in the entire workbook. The reason I want to do this is because I currently have data for 12 different series, and each series has 7 values. What I would like to do is keep the same chart, but reduce the number of values per series from 7 to just 4. The easiest way seems to me to be to create ...

Macro to sort variable data range fails
Hi, Can you please help me identify why this macro is failing with the following error? Runtime error 1004: Sort method of Range class failed Row 7 is the header row Data range has a variable number of rows (so using end(xldown) to select the range) Using column BA to select a contiguous range of cells as all cells are populated in the column Then using that selection to select entire rows in my list Sorting as per criteria using row 7 as header row Rows("7:7").Select Range("BA7").Activate Range(ActiveCell, ActiveCell.End(xlDown)).Select ...

More on "Data Range"
This is wonderful for using "OFFSET" for a Dynamic Range Name for a Chart "Data Range"...http://processtrends.com/pg_dynamic_charts.htm Thank you again Kelly O'Day. It includes, for the X-Axis Dynamic Range Name... "Refers to:" ---> =OFFSET(dyn_ranges!$A$2,0,0,COUNTA(dyn_ranges!$A$A)-1) Currently, I'm using Column A to receive several Ranges for Charts from Access Automation. The...COUNTA(dyn_ranges!$A$A) will Count Entries in Column A from Cell $A$2 down. Other than separating the Ranges...IE.. For Chart 1...Columns A:B ---> COUNTA(dyn_ranges!$...

Comparing 2 coulumns in different worksheets and printing the rows that match
hello, Sorry to bother everyone. I am trying to learn some Visual Basic t maybe make a function that woudl do what i need, but that is goin slow. What i am trying to do follows. Workbook: NeededPatches.xls sheet1: Srvneed sheet2: ITPatches sheet3: Final I have created a perl script that uses the microsft progra hfnetchk.exe to find what patches are needed on all of our systems her at work. it gives me a text print out that is tab delimited and import that into excel on the *Srvneed* sheet. *Column B* in this shee has the patch numbers for what is needed. Now our IT security also has a lis...

Excel chart data labels showing % change instead of value?
I'm needing to have my excel chart show me the percent change in my 2 series. I would like this in a data label over series 2 on the chart, but all I can figure out to do is have the value labeled. Does any one have any suggestions? I'm believe I'm using Excel 2002. You can use Rob Bovey's XY Chart Labeler. http://www.appspro.com/Utilities/ChartLabeler.htm "MFritz" <MFritz@discussions.microsoft.com> wrote in message news:29317BE2-4574-4623-8D5E-A3BA97B3FAE6@microsoft.com... > I'm needing to have my excel chart show me the percent change in my 2...

Animated bar Charts
Anyone know how to take a simple bar chart, with say a value of 10 and through the press of a button have it animate and shrink in size down to 5 for example. In other words, I have tons of bars in a "before" case and also an "after" case and I want a macro or code to show the transition between those two. Any Ideas? -- gsimmons2005 ------------------------------------------------------------------------ gsimmons2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26385 View this thread: http://www.excelforum.com/showthread.php?threadid=...

Textbox on chart doesn't print in Office 2003, but does in 2007
I have some charts that I create through C# code on a "template" worksheet and then copy it over to a new worksheet using the following code. When looking at the chart.Shapes, I can see the three Textbox objects that I want and these are the only shapes available. I can see them on the screen in both Excel 2003 and 2007 correctly. When I print in 2003 though, the textboxes don't appear like they do for 2007. Can anyone shed some light on why this might be happening? // Copy the images and charts (note index starts at 1) for (int z = 1; z <= wsTemplate.Shapes.Count; ...

How to add a second axis to a chart?
I am trying to do a chart with items in Millions and in Units so I need a second axis. Please help. You can't select Secondary Axis until a second series is on the chart. Once you've done that, select Format Data Series and Axis to select the Secondary axis. "RLThomps" wrote: > I am trying to do a chart with items in Millions and in Units so I need a > second axis. Please help. ...

How do I resize a worksheet after deleting rows?
After deleting rows in a spreadsheet Excel seems to remember the blank rows, and will try to print them unless I reset the print area. Also, when I subtotal the grand totals appear after the blank rows. Is there a way to re-set the size? Save the workbook after deleting the Entire rows and before printing. -- Regards, Tom Ogilvy "Jay Jay" <JayJay@discussions.microsoft.com> wrote in message news:7A4B3295-C46C-4100-94B5-9AB4500DCFB2@microsoft.com... > After deleting rows in a spreadsheet Excel seems to remember the blank rows, > and will try to print them unless I...

Protect Worksheet but allow to insert or delete rows
I have set up a worksheet, locked and unlocked cells as needed and I want to allow the user to insert and delete rows. But, when I set protection and tick "Insert Rows" and tick "Delete Rows"; the menu only will allow "Insert Rows", the "Delete Rows" field is not accessible. Solution? Do you get an error message saying that you can't delete the row since there are locked cells on that row? I do when I protect a worksheet using your options and then rightclick on a row number and choose delete. (And that row contains locked cells.) Bob L Hill...

Doing charts in Excel 2000
i am still using the Excel in my Office 200 package. Where can I get udates to do better charts? All of the techniques shown here http://peltiertech.com/Excel/Charts/index.html can be used to make more interesting chart best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "TVG" <TVG@discussions.microsoft.com> wrote in message news:CD14A74C-8018-40FC-AA4F-FA0925208A78@microsoft.com... >i am still using the Excel in my Office 200 package. Where can I get udates > to do better charts? ...

Returning a Value from Another Worksheet
I have two worksheets in one Excel 2007 file that I'm working with, in which I want the second worksheet to return values of the first worksheet. Hope I can explain it clearly here: The first sheet contains a list of data and numbers to caculate a points system. For example: Worksheet1 Name Column B Column C Points Data1 100 5 4 Data2 75 2 1 ... 2) In the second worksheet, I want to be able to input a specific value from Column A (i.e., I inputted "Data2" from Workshee...

how to create a clustered, 100% stacked combination chart
I want to create a chart with 2 100% stacked columns to compare % of 2 different years. Hi, Maybe you can use the information here but using 100% stacked columns. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Keri in Vermont wrote: > I want to create a chart with 2 100% stacked columns to compare % of 2 > different years. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

adjusable columns in original dynamics windows
adjusable columns in original dynamics windows, that you can sort on and size properly. Is there a way to post a screenshot to show you what i'm on about ?? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

Combined bar and line chart (two Y axis) how to make excel know which data goes where
Hello all, after the super fast problem fixing last time i had a problem, i'd like to ask the excel collective for some more help... :) now trying to plot a combined bar chart and line graph. I basically want all the sets of data except one to appear as columns plotted against the axis on the left and the last set of data (which is a cumulative value of the data appearing for the columns) plotted against the right hand axis as a line. I can plot the graph fine, but out of the 7 sets of data which i want to appear as 6 sets of columns and one line, i get four sets of columns and three ...

Range name Problem
I have created a range name XYZ23 refering to a column of data in sheet1. On sheet 2 I typed XYZ23 in cell A3. Why does =COUNT(A3) typed in B3 return a 0 when =COUNT(XYZ23) return the correct count? Hi use =COUNT(INDIRECT(A3)) -- Regards Frank Kabel Frankfurt, Germany "Landmine" <Landmine@discussions.microsoft.com> schrieb im Newsbeitrag news:FF9F16A2-ACC7-44DA-8DE9-BCD60D18B791@microsoft.com... > I have created a range name XYZ23 refering to a column of data in sheet1. On > sheet 2 I typed XYZ23 in cell A3. Why does =COUNT(A3) typed in B3 return a 0 > when =C...

Multiple Worksheets
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) How can I copy same info between worksheets? You need to be more specific about what you're trying to accomplish... For what little you've disclosed regular copy/paste will work, but storing duplicate copies of the same data on multiple sheets [or in multiple workbooks] is redundant & inefficient. I'm sure you're clear in your own mind what you're trying to do, but these one-liners do not effectively convey what you're thinking :-) Also, it's fine to post multiple questions & use sepa...

Add 2nd Y axis in XY scatter chart?
Is there any way to add a second Y axis and then plot data associated with this axis in an XY scatter chart? If so, what are the steps? Thanks, Don Culp Hi Don, Add your data to the chart then double click the new data series. On the Axes tab of the format dialog chose Secondary axis. Cheers Andy Don Culp wrote: > Is there any way to add a second Y axis and then plot data associated with > this axis in an XY scatter chart? If so, what are the steps? > > Thanks, > Don Culp > > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

How to link data and it's formats bewteen worksheets
I am using Office v.X for mac. I have a workbook that that has master worksheet with A1:AK4 column headings and A5:AK265 has data with different formats (date,text,number etc.) I want to create new worksheet as a diiferent catagories with linking data from the master worksheet. So when i edit the master worksheet it edits the other worksheet simultaneous. For example rows 5,15,70,100 belongs to catagory1 being worksheet2 and rows 10,50,200 being another catagory as another worksheet and so on. Different rows will make up 6 catagories in turns 6 worksheets. I have tried using paste link but i l...

i need to create a chart from data from one workbook in another wo
hi new to excell so please be kind. i need to create a series of charts from different sets of data in one workbook, and have the charts show in another seperate workbook, and update automaticly when the data changes in the first workbook. i know how to create charts, its just that i can't get it linking from one workbook to another please help Mark Hi, An easy approach would be: Make the charts in the workbook containing the data. Cut and paste those charts into a new workbook. The charts are still linked to the original workbook. If you make changes in the data, the charts...