Broken link between worksheets
 

HELP! I have one workbook in which Sheet 1 imports data from Quickbooks. It always has the same number of rows and columns. I want Sheet 2 to pull data from Sheet 1 to create another report where G4 on Sheet1 is the same as G4 on Sheet 2.

Initially, I imported the data from Quickbooks, then typed +Sheet1!$G$4 into the Sheet 2 cell. However, when I import new data to replace old data on Sheet 1 (again, same number of rows and columns - the numbers just change within the cells) my Sheet 2 cells all say #REF! error.

Any idea how I can fix this? I'm assuming it gets confused when I replace old data with new in Sheet 1.

Trixie

Sounds like the original cells are being deleted - maybe by the import
or maybe by a Cut and Paste operation.

You can make the formula survive this by changing it to
=INDIRECT("Sheet1!G4")

This formula will not adjust if you copy or move it.
The following will:
=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN(),,1))
Placed in Sheet2!G4 it will reference Sheet1!G4.
copied to Sheet2!H5 it will reference Sheet1!H5

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup