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?
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?
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
posted by jeather at 2:41 PM on April 18, 2012
This thread is closed to new comments.
posted by hot soup at 2:38 PM on April 18, 2012