Relative reference Chart data

[Mike.Bernhard83]

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.

 

[Tushar Mehta]

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

 

[Jon Peltier]

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