Stupid Excel Tricks

[Puppet_Sock]

So this is with Excel 2007.

Put the following in columns 1 and 2, rows 1 through 6. So you will
have a little two column table.

Y1 Y2
1 2
2 3
3 4
4 5
5 6

Now highlight this data, and Insert a line graph, the first one from
the selection. So you get a line graph with two straight lines.

Now on the graph, click on the Y2 line. And delete it. So the line
graph now has a single line showing the data from column 1.

Now click on column 2 and delete the column. And notice the error
message about an invalid reference.

What? What's that? And how do I avoid getting that message?
Socks

 

[Puppet_Sock]

So this is with Excel 2007.

Put the following in columns 1 and 2, rows 1 through 6. So you will
have a little two column table.

Y1  Y2
1    2
2    3
3    4
4    5
5    6

Now highlight this data, and Insert a line graph, the first one from
the selection. So you get a line graph with two straight lines.

Now on the graph, click on the Y2 line. And delete it. So the line
graph now has a single line showing the data from column 1.

Now click on column 2 and delete the column. And notice the error
message about an invalid reference.

What? What's that? And how do I avoid getting that message?
Socks

So I answered my own question. It would seem the graph is
keeping information about what it was pointing at, even though
it is no longer pointing at it. So deleting the column creates
an invalid reference in this historical information.

So, to get rid of the column (as I need to) it is necessary to
clear the data rather than delete the column. Instead of
deleting the column it is necessary to select the column then
click the Del key. (Or use the Clear command in VBA, which
is what I'm doing.) If I do that then no error message.

Since I actually have data to the right of the two columns in
my example, I then have to copy it over. Note, not cut-n-paste
since the cut operation will create invalid references in
graph data history also. But copy-n-paste, then clear the
data where it is now redundant. That is, manually do the
steps that are equivalent to deleting the column, but never
delete any column.
Socks