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)
 
Best answer: 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


Response by poster: 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.