Hi, This is probably easier than I make it out to be but here goes.

I'm making a list of references I've been using for a research paper in
excel and because the list is quite large (50 sources) I wanted to
visually depict how useful each reference is. So I created a bubble
chart with "relevance" on the x-axis and "quality of the source" on the
y-axis which references a 1-4 scale for relevance and quality. That
works fine, but when I want to sort references, let's say by date, the
references for the chart data are absolute, which means if a Row is
sorted up or down, the chart in that row will still reference the
previous cell data it was in. Of course I tried turning the absolute
references into relative, but excel changes them back to absolute
everytime!! Is there a way around this? I hope i didn't just confuse
everyone. Any help is appreciated.

In article om,
says...
Hi, This is probably easier than I make it out to be but here goes.

I'm making a list of references I've been using for a research paper in
excel and because the list is quite large (50 sources) I wanted to
visually depict how useful each reference is. So I created a bubble
chart with "relevance" on the x-axis and "quality of the source" on the
y-axis which references a 1-4 scale for relevance and quality. That
works fine, but when I want to sort references, let's say by date, the
references for the chart data are absolute, which means if a Row is
sorted up or down, the chart in that row will still reference the
previous cell data it was in. Of course I tried turning the absolute
references into relative, but excel changes them back to absolute
everytime!! Is there a way around this? I hope i didn't just confuse
everyone. Any help is appreciated.


Maybe I am missing something but how does it not work? If you plot a range,
say A1:A50, in a chart, it doesn't matter what's in each individual cell.
XL will plot all 50; just that after you sort the range the chart will show
the points in a different order.

--
Regards,

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

There is no problem the way the Microsoft excel behaves. You are confusing
with the Absolute reference and relative refernce. Relative reference is only
pointing to a reference and not to the absolute address or location. Sorting
would actually change the order only if your source content is Absolute.

It is similar to Pointer to Pointer and Pointer to address. If it sorts as
per your request then the whole chart will be blank. Thats why Excel
positions the value as Absolute reference.

Challa Prabhu

" wrote:

Hi, This is probably easier than I make it out to be but here goes.

I'm making a list of references I've been using for a research paper in
excel and because the list is quite large (50 sources) I wanted to
visually depict how useful each reference is. So I created a bubble
chart with "relevance" on the x-axis and "quality of the source" on the
y-axis which references a 1-4 scale for relevance and quality. That
works fine, but when I want to sort references, let's say by date, the
references for the chart data are absolute, which means if a Row is
sorted up or down, the chart in that row will still reference the
previous cell data it was in. Of course I tried turning the absolute
references into relative, but excel changes them back to absolute
everytime!! Is there a way around this? I hope i didn't just confuse
everyone. Any help is appreciated.

1. Charts use absolute referencing.

2. When you sort a range, links to cells within the sorted range stay
pointing at the same cell with respect to the cell containing the link. If
the cell is outside the sorted region, it stays pointed to the same absolute
cell, whether the link was relative or absolute. So if the chart points to
row 2 of a range before sorting, it points to row 2 of the range after
sorting.

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


wrote in message
ps.com...
Hi, This is probably easier than I make it out to be but here goes.

I'm making a list of references I've been using for a research paper in
excel and because the list is quite large (50 sources) I wanted to
visually depict how useful each reference is. So I created a bubble
chart with "relevance" on the x-axis and "quality of the source" on the
y-axis which references a 1-4 scale for relevance and quality. That
works fine, but when I want to sort references, let's say by date, the
references for the chart data are absolute, which means if a Row is
sorted up or down, the chart in that row will still reference the
previous cell data it was in. Of course I tried turning the absolute
references into relative, but excel changes them back to absolute
everytime!! Is there a way around this? I hope i didn't just confuse
everyone. Any help is appreciated.