#REF!

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

John Gregory

Two worksheets sheet are used to feed a chart on a third sheet. One accepts
the results of a query that's triggered by a macro from a button on an
outline (a forth sheet). The second sheet is a worksheet that simply takes
data from the query sheet and permits it to be manipulated. The data is
transfer by simply "=" in a cell then a link to the respective cell on the
query sheet.

When I run the macro, the cells on the worksheet report "#REF!". Anything
linked to that updated query sheet reports "#REF!".

What am I doing wrong and how do I prevent this? I could solve the problem
by editing the macro to rebuild the links each time but I don't understand
why the links are being broke. I am clearing the query sheet by selecting
the top left cell and clicking DELETE but I can't see how that could be
returning the error. The new data ends up in the same position that the
worksheet points to and the query sheet name hasn't changed.
 
John

I suspect you are deleting the entire rows and columns. This has the affect
of writing the #REF! to the worksheet formulas, so when you put the data in
the formula has no cell reference.

Trying 'Clear' instead of delete. (The equivalent of pressing the delete
key)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Hummmm.... Looks like there's no alternative but to record the strokes to
relink the cells after every update.

Thanks, Nick.
 
Apparently I misunderstood and thought Clear = Delete in effect. As it
were... I did try Clear and got the same results. So I just recorded a new
macro to include rebuilding the links as a last step. That worked.

But "Clear" should have worked as well? Or was that merely a suggestion to
try?
 
John

I probably confused you by saying clear was the same as the 'delete' key.
These are two distinctly different operations in Excel. They are both
available from the Edit menu. Delete actually 'moves' the surrounding cells,
although on empty sheets this sometimes cannot be seen (MS added animation,
in 95 I think), to assist in recognising this. Clear does not move any data
it simply clears the selected cells. In this way, nothing moves and links
remain intact.

You may end up with rows that are blank in the data, but a sort will send
those to the bottom

(The shortcut for 'Clear' is the delete key on your keyboard)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Thanks for the clarification, Nick. I'll redo what I did and try it with
"Clear" recorded in the macro. I thought I did that once but there seem to
be so many items in the fire here that it's difficult to keep track
sometimes. I'll let you know how I make out. Thanks again.
 
Back
Top