Join 3,494 readers in helping fund MetaFilter (Hide)


Excel keeps unreferring my references
April 18, 2012 2:24 PM   Subscribe

Excel 2010 query using data from MS Access: refreshing the query screws with my cell references. Help me make it stop.

Sheet one, data: pulls query from a database using parameters on sheet two.
Sheet two, form: has room to enter parameters, then has a bunch of cells that refer to the data sheet.

Eg: parameters are in form!b1 and form!b2. These work perfectly.
The query starts in data!a1.

In form!b10, b11, b12, etc, I want to get the content of data!c1, c2, c3, etc. This works. The first time.

But when I refresh the parameters and return fewer items than the last time, my =data!c3, etc, turn into data!#REF (eg, if I had 20 items the first time, then have 2, I get lines 1 and 20 filled with the info from items 1 and 2, and lines 2-18 with reference errors). This happens whether or not I use absolute references.

How can I fix this?
posted by jeather to Computers & Internet (2 answers total)
 
In your query table properties (click in the table Design tab, then Properties, is "Overwrite existing cells with new data, clear unused cells" selected? If not, Excel is probably inserting/deleting rows, which screws with references to those rows.
posted by hot soup at 2:38 PM on April 18, 2012


No, it's got Insert cells for new data, delete unused cells. I switched it, and now it works perfectly. Thanks!
posted by jeather at 2:41 PM on April 18, 2012


« Older How is the space shuttle actua...   |  Is there a way to cut white sp... Newer »
This thread is closed to new comments.