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 does the shuttle hang on to its carrier plane?   |   epub poetry fix Newer »
This thread is closed to new comments.