Relative Addressing of Charts in Calc
#1
#2

Relative Addressing of Charts in Calc

#1
#2

#1

On Tue, 2004-09-14 at 14:21, Peter Kupfer wrote:
> I sent this over the weekend, but I know there were some server errors
> on some of the lists so I am resending it since I didn't get a
> response. Any help would be appreciated.
>
> --
>
> Well, there was a discussion about this not too long ago, but I didn't
> investigate what was said because it didn't affect me at the time.
>
> I know there is a difference between the two and understand what the
> difference it, the problem I have right now is how change what my
> spreadsheet does. I tried pressing [shift-f4] as per the help file,
> but I don't know what that does. What I have a is a spreadsheet with
> my bills and and the bottom I have a pie chart. I sum different bills
> into categories into three cells and then have a pie chart that shows
> me those three categories. I make a new sheet for each month (by
> copying the previous month's), and the pie chart tries to address the
> previous month's sheet.
>
> I would like to do one of two things, neither of which I can figure
> out.
>
> 1) How do I edit the data in a chart. I right click on it and I don't
> see and option I highlight the graph (which is just a ? right now) and
> go through the menus and I don't see anything that looks right. If I
> could edit the data, I could fix the problem,
>
> 2) How do I change the addressing? I think I want relative addressing
> and I getting absolute.
>
> Any help would be great. Thanks!

This was replied to by Ian Laurensen:

Shift + F4 is just a quick way of putting in the dollar signs for absolute cell addressing. Changing the data range of a chart:

First select any cell (I.e. make sure that the chart isn't selected)
Now click on the border of the chart. It should have green selection handles.
Now right click and you should get the Modify Data Range.. option.

Unfortunately the addressing for charts is always absolute regardless of what you type in for the address. Also, note that, unlike Excel, you can have absolute Sheet addresses (the dollar sign to the left of the sheet name). Thus when you copy a sheet with a chart, the chart still refers to the data that the original chart referred to. So you need to use the above method to change the data range. Cheers, Ian

#2

On Fri, 2004-09-17 at 12:01, CPH wrote:
[snip]
> I think that the content in "Help" -> Contents -> Search and input
> "absolute
> addresses" has quite a good explanation.

The help doesn't refer to the absolute addressing of sheets. The way that Calc works is different to Excel. In Excel if a sheet name is explicitly used in a formula, then when the cell that contains that formula is copied, the pasted cell will always refer to the specified sheet. There is one exception to that rule in Excel, if you have a formula that explicitly refers to the sheet that the cell containing the formula is in, and copy the entire sheet, then the formula will refer to the newly copied sheet. In Calc, you can specify absolute and relative *sheet* addresses by having a dollar sign to the left of the sheet name. E.g. =$Sheet2.A1 In this example the sheet is absolutely addressed but the cells are relative. The problem that the original poster had, as I understand it, was that they had a chart on a sheet, where the datarange for that chart was on the same sheet. In Excel if you copied such a sheet, then the "datarange" for the chart would refer to the equivalent range on the newly copied sheet. In Calc, the chart still refers to the data on the original sheet. Further, no matter what you type in for the datarange for a chart in Calc the datarange is always absolute including for the sheet name.
Cheers, Ian

Last edited 21 May 2005