Charting Problem #2

I have a problem with charting a Dynamic chart. 

I goes ok to set up the dynamic function as a range. It also shows the 
correct series. The range is as follows:
=OFFSET(Sheet3!$A$1;1;Sheet3!$P$2-1;COUNTA(INDIRECT("C"&Sheet3!$P$2;FALSE));1)

Where P2 is an input from a listbox, and the name of the range is: 
Sheet3!Dato. 

When I try to change the reference for the series i get the following message:

"Your formula contains an invalid external reference to a worksheet. Verify 
the path, workbook, and range name or cell reference are correct, and try 
again."

The series function is below.
         =SERIES(Sheet3!$D$1;Sheet3!Dato;Sheet3!$D$2:$D$28;1)
I have also tried to include a ' before and after "Sheet 3".
I have alsoe checked that the workbook does not have an external reference.

There is nothing wrong with the series and it works perfectly when I am not 
using the range, but uses A1:A100 etc.

I is also worth mentioning that the dynamic range for the Chart label works 
just fine.

Anyone who knows what I am doing wrong.


-- 
Peter B
Norway
0
PeterB (7)
8/5/2004 8:07:01 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
837 Views

Similar Articles

[PageSpeed] 42

It works for me in the sense that I don't get any error.  Are you sure 
P2 contains a legitimate value?

That said, I should clarify what "in the sense that I don't get any 
error" means.  The chart contains only a single point rather than all 
the values in the range 2:x.

That is because the XL charting module is a lot less flexible 
(forgiving?) than the part of XL that deals with formulas in cells.

While in some cases it handles the INDIRECT function just fine, in 
other instances it doesn't.  In this instance, for some reason, it only 
returns a single value from row 2.

What does work is a named formula:
=OFFSET(Sheet1!$A$1,1,Sheet1!$H$1-1,COUNTA(OFFSET(Sheet1!$A:
$A,0,Sheet1!$H$1-1)),1)

My tests involved sheet1 (rather than sheet3) and the column to plot 
was controlled by H1 (rather than P2).

-- 
Regards,

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

In article <9824402B-96F2-4AB7-A025-52AD656AB4DA@microsoft.com>, 
PeterB@discussions.microsoft.com says...
> I have a problem with charting a Dynamic chart. 
> 
> I goes ok to set up the dynamic function as a range. It also shows the 
> correct series. The range is as follows:
> =OFFSET(Sheet3!$A$1;1;Sheet3!$P$2-1;COUNTA(INDIRECT("C"&Sheet3!$P$2;FALSE));1)
> 
> Where P2 is an input from a listbox, and the name of the range is: 
> Sheet3!Dato. 
> 
> When I try to change the reference for the series i get the following message:
> 
> "Your formula contains an invalid external reference to a worksheet. Verify 
> the path, workbook, and range name or cell reference are correct, and try 
> again."
> 
> The series function is below.
>          =SERIES(Sheet3!$D$1;Sheet3!Dato;Sheet3!$D$2:$D$28;1)
> I have also tried to include a ' before and after "Sheet 3".
> I have alsoe checked that the workbook does not have an external reference.
> 
> There is nothing wrong with the series and it works perfectly when I am not 
> using the range, but uses A1:A100 etc.
> 
> I is also worth mentioning that the dynamic range for the Chart label works 
> just fine.
> 
> Anyone who knows what I am doing wrong.
> 
> 
> 
0
8/5/2004 12:23:10 PM
Reply:

Similar Artilces:

OUTLOOK NEWS #2
Once again my news does not work. When I click it, I get a folder full of outlook stuff. This has happened before and someone here helped me. I, unfortunately, do not remember what to do. Next time I will save the instructions as this seems to be happening again and again. The word news appears at the top but when clicked there is nothing but the folder. Help Please! Thanks! You mean you cannot start the Newsreader from within Outlook? Go to Internet Explorer and make sure Outlook Express is set as the default newreader. For other solutions see; http://www.howto-outlook.com/faq/news....

lookup problem #2
I have a sheet with J11 called Buyers # and K11 called Buyers name. On the next sheet called Buyers there is a list with B1 being the buyers number, and C2 is the buyers names. How can I enter a number on the first sheet and get the corresponding name to come up? Todd Have a look in HELP index for VLOOKUP -- Don Guillett SalesAid Software donaldb@281.com "Todd Fischer" <todd@fischerhome.net> wrote in message news:evMAjQDPEHA.3052@TK2MSFTNGP09.phx.gbl... > I have a sheet with J11 called Buyers # and K11 called Buyers name. On the > next sheet called Buyers there is a...

Similar multiple entries not downloading #2
Hi, I am facing issue in downloading similar multiple transactions - eg- I have 3 southwest air ticket purchase transactions on a particualr day , where each tickets is for $60. Now in my bank statement, these appear as - 1-aug Southwest $60 1-aug Southwest $60 1-aug Southwest $60 - which is correct as i purchased 3 tickets. now when i download in money, only one transaction comes as - 1-aug Southwest $60 -other 2 are not downloaded. It seems money is treating other two as duplicate. Can someone tell me how can i get such t...

A Combination Chart using a Column and Line
I have a large amount of data about protein values and I have grouped these using the Analysis-Pak Histogram wizard. The resulting frequencies have been charted using the 'Column' Chart Type. Using the NORMDIST function I then plotted the appropriate Normal Curve as a 'Line' Chart Type on the same chart but using a secondary y-axis. I was wondering if there is any sensible way of scaling the NORMDIST values so that both sets of data can be charted on the same axis. Any suggestions will be gratefully received. ...

Publisher Problem
Volunteer Nancy C. Benner Janyszeski Answers Subject MS Publisher 2000 Problem Question I'm using Pub 2000 in on a Pent 4 3.06GHz processor 512 mb ram. Publisher is installed on the local drive (c:). Problem is when trying to open a publisher file from the network share it won't open. The files are on a NT Server 4.0 shared directory with full access to all users. When trying to open the file all it does is close publisher completely. If I do manage to get a file open when I try to save it the message appears stating that the destination drive (S:\shared) is full. An examin...

Network printing problem
I administer a small network at a middle school. We use Publisher 2003 in our classrooms, until lately with no problem. We have three computer labs, each with a printer and print server running Win Server 2003. Just this week we have been finding that when girls work on a publication in one room and then open it in another room, sometimes Publisher maintains the old printer connection and sends the document to a printer in another room. The Print dialogue shows the new printer, but while the document is spooling, that dialogue box shows that the document is actually printing to the ...

Problem with register scrolling
I just started having this problem with Money 2004 Deluxe. When I try to scroll to the last entry of my register the scroll bar reaches the bottom, but I am not at the bottom of the register. I must click on the down arrow at the bottom of the scroll bar to get the register to scroll to the last entry. bdog Are you using a scroll mouse button? Sometimes the button gets stuck and what you describe happens in Bills & Deposits. If so then move your scroll button a slight amount. Mark Fields "bdog" <bd@op.net> wrote in message news:Xns9469EF048AE96bdopnet@24.50.78.38...

Header/Footer #2
When we open a workbook, we would like the header/footer to default to an 8 instead of 10. We always end up changing it from a 10 to an 8. Any way for this to default instead of creating a new template? Excel 2002. Thank you. You need to save a template file (called Book or Sheet) in the XLSTART folder. Look in Help under Standard Template for more details. Bernard "Kim" <hanke@wickpilcher.com> wrote in message news:1ca0001c38870$7cf791a0$a601280a@phx.gbl... > When we open a workbook, we would like the header/footer > to default to an 8 instead of 10. We always ...

Smartlist export to Excel #2
Quantities for Inventory Items are set-up in our database with zero decimals. When exporting 'Inventory Purchase Receipt' information to Excel, quantities receipt and quantities sold, greather than 1000, are shown only the first digit in Excel, for example 4,799 is shown for 4. Any idea how to fix it??? Thanks, -- Toni Sounds more likely that the file is being exported as a csv using the print function in smartlist. -- Mick "Toni" wrote: > Quantities for Inventory Items are set-up in our database with zero decimals. > When exporting 'Inventory Purchase ...

source sheet to auto fill 2 other sheets
sheet1 witch is what i fill out (source data)i want the data to be auto filld into the next too sheets..an to also sort the the data (by date- verry important) from the 1st sheet. all 3 sheets r totaly difernt..same names though.. thx for any help ...

I want a cell should 2 accept only >=0 or <=30 or Y or N
I want a cell should to accept >=0 or <=30 or Y or N Regards Brijesh =OR(AND(A1>=0,A1<=30),A1="Y",A1="N") In "Custom" for Data Validation. -- Regards Dave Hawley www.ozgrid.com "Brijesh Poojary" <Brijesh Poojary@discussions.microsoft.com> wrote in message news:1F46DC05-D143-407F-BB81-D5D9109914D6@microsoft.com... > > I want a cell should to accept >=0 or <=30 or Y or N > > Regards > Brijesh ...

Customizing -2^2
Shouldn't minus a squared number be a minus number? In Excel -2^2 = 4. To keep it negative I have to write -(2^2). It's very easy to make mistakes because I also work with Quattro Pro which works differently. Is there a way to customize this in Excel so that -2^2 = -4? Thanks. In Excel the negation takes precedence over the power operator. However, subtraction is a lower priority. 0-2^2 results in -4. Does that help? Regards, Fred. "Tim Walters" <timwalters@DROPTHISterra.es> wrote in message news:3bb8ue.of8.17.1@news.alt.net... > Sho...

line-column combination chart, with stacked columns
I am trying to make a combination chart with stacked colums and an overlayed line (or data point), just like it is possible to make a combination chart with "regular" bars and a line. Hi, Have you tried creating the combination chart yourself? Create a stacked columns chart, including the series you want to plot as a line. Then select this series and use Chart > Chart Type to change the series into a line chart of your choice. Cheers Andy annewcpss wrote: > I am trying to make a combination chart with stacked colums and an overlayed > line (or data point), just like ...

Too many cell formats #2
I have a set of spreadsheets that I am unable to change the format of any cells. When I try to change the background color, font or font size, an error message pops up saying there are too many cell formats. I really want to avoid having to re-create all of these spreadsheets. Please help. Thank you. Excel has a built in limit to the number of cell formats. You have reached that limit. As the message indicates, you cannot use any additional cell formats. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wsdanalyst" <...

Printing multiple charts #2
My question is - i have multiple charts on the same worksheet. Is it possible to print them on separate pages without selecting each one individually? Hi, Depending on you layout inserting page breaks may solve your problem. Cheers Andy Edwina wrote: > My question is - i have multiple charts on the same worksheet. Is it > possible to print them on separate pages without selecting each one > individually? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi Edwina, Here is a possible workaround. Go to View>Page Break Preview Select a range of cells and the...

Annotating charts in Excel
I am creating a line chart in Excel, and want to annotate various movements in the line. It is a share price graph, with price against date (and volumes on a secondary axis), and I want to explain the significant peaks, troughs, movements etc. Ultimately I want arrows to point to the line which are sequentially numbered - I will have a separate table which shows the date and narrative for each number (space is limited on the chart itself, as I'll have about 10-15 arrows). Rather than drawing the arrows on manually, and adding text boxes with numbers, is there any quicker way of doing it...

Problem creating distribution group
I have over 700 address contacts that have been "tagged" into various categories. I know how to set up a new distribution group. However, when I choose "select names" the names from my list apear as first name, last name instead of last name , first name. This makes it almost impossible to scroll up and down looking for the name I want to select and add to my distribution group. Please advise of any suggestions. What version of Outlook? Randal Langley wrote: > I have over 700 address contacts that have been "tagged" > into various categories. I ...

cell reference problem #2
I am trouble with cell references in a spreadsheet. Some cells will display the cell reference rather than the value it points to. For example the cell will display =A1 as text rather than the content of that cell. What could cause this???????? thanks in advance david Hi David, First check that you have calculation turned on. Tools, Options, Calculation, automatic calculation If that is the problem and it recurs, see my slowresp to see how to spot the problem earlier. The other possibility is that you have Text formatting instead of General or a numeric format. Other things t...

Moving Emails to Folders
Though I encountered no problems over many months, in the last several I have run into a huge issue in moving certain emails from my "in" and "sent" boxes to my achive folders. These emails all either originate from China or have a Chinese originated reply in the email string. The individuals with whom I'm corresponding in China are from major corporations and, in one case, the person is an employee on my corporate email system. The symptoms are as follows. When I try to drag the email to the folder, the hourglass appears, as if it is trying to complete the transfer. A...

combining two line charts with different x axis intervals
Hi newsgroup, I'm not getting around this one and hope that I'm just not clever enough ;) I try to get two line charts in the same graph, but they have different x axis intervals. Is this possible? For better understanding, an example of the first being x y 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 and an example of the second being x y 1 2 3 2 5 2 7 2 9 2 Thanks a lot in advance! Chris Hi, Try using a xy-scatter chart rather than a Line chart. Cheers Andy On 12/05/2010 11:50, Chris J Denver wrote: > Hi n...

X axis on 0 on Line-Column on 2 Axes
Is there a way on a Line-Column on 2 Axes chart to have the 0 value on both the bar and line axes be straight across from each other even though the bar chart is in 1,000s and the line axes is in single digits and has negative numbers? Only way I can figure is to put negative numbers in the bar axis as well. Try this: http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Supe" <Supe@discussions.microsoft.com> wrote in message news:F1174954-513D-...

Euro Currency Symbol Problem
I have Money 2006 Deluxe and I've got the following problem: The program does not show the correct EURO symbol. I change it to the correct one from the "Update Currencies" and it seemed to be ok, but when I restart the problem it "forgot" what i set. I try also to set "EU" as a symbol, but the result was the same. Can anybody help me to solve this issue? ...

Change file name #2
Hi, I've been searching for a solution to the following problem for more than a week. I've posted it here a couple of times, and got some good ideas, but unfortunately they did not work. The variable "myfolder1" has the correct value, followed by ".xls" as it should. I'd be very thankful if someone could find my error. This is the original question: I am new to Exel, and i cannot find my error in the following script. The error is: Run time erroro '9' Subscript out of range and it always stops on the "windows(my folder1).activate" line...

Command button code problem
I have a form with a command button that opens a report in preview. The form has a combo box from which you select the record that should be shown on the report but when I click on the command button I get all the records not just the one I have clicked What's wrong with my code? Here it is Private Sub cmdprintcr_Click() On Error GoTo Err_cmdprintcr_Click Dim strAVCISCode As String 'set strAVCISCode equal to the selected value before closing the form, otherwise it will give us an error strAVCISCode = "AVCISCode" DoCmd.OpenReport "rptCrimereport",...

Lost all inbox and contacts etc when removed server #2
I found the outlook.ost file. Can I reimpost this to Outlook? Can I somehow set it or use it as the "personal data files" that the outlook dialog box requested? I dont get why I was able to access the contacts emails etc after I disconnected the exchange server BUT only after I removed the exchange server did it all disappear. Not sure how I was meant to continue this thread so I copied the previous question, comment and listed it as a new post -- hope this it ok protocol. Also someone else had recommended that I re-engage the server but it is neither listed nor frank...