Reference Problem

  • Thread starter Thread starter ksbigfoot
  • Start date Start date
K

ksbigfoot

I am using Excel 2000 and I am pulling data from a Microsoft Acces
table and populating sheet1 with all the data in that table.
I then use sheet2 and pull data from the first sheet by pointing to i
in the following way: = Sheet1!$B233
On sheet1, the first 10 rows belong together and the next ten row
belong together, etc, ....
My first column of sheet 2 contains rows 1 - 10 of sheet1, My secon
column of sheet2 contains rows 11 - 20 of sheet1, etc, until the end.

I actually go much further than I need and set formulas to pull bac
enough cells from sheet1 to populate 100 columns of sheet2.
I have my data refreshing everytime excel file is opened.

Whew! So finally my problem, First time I open excel file, I pull bac
enough records to populate 50 columns of sheet2. I close down th
excel file. The table data is changed so that only 30 records will b
available to the Excel file.
I open up the Excel file and all the data is pulled into Sheet1. Whe
I look at sheet2, 30 columns of data are available and they look ok
but columns 31 - 50 have a Sheet1!#REF! and my formulas that point t
Sheet1 are continued on column 51.

I hope I am making sense as this has me really stumped.

Thank
 
I was stuck for a few days on this, but I found the solution.
I went to a cell in Sheet1, Clicked up top on the menu: 'Data'
Selected 'Get External Data'.
Selected 'Data Range Properties'.
There is an option that says 'If the number of rows in the data rang
changes upon refresh:
Overwrite existing cells with new data, clear unused cells.'
I picked this option and I am not getting the #REF! problem anymore.
Thank
 
Bigfoot.. in the future, you might want to make your first message shorter,
and/or make the subject line more descriptive (like "#REF! errors when
refreshing query"). That way more people will read it and can help you.
Your problem is one I had recently also, but the length of your message was
a turn-off from wanting to read it. I'm not trying to be a grump or
anything, but the shorter, easier the questions the more people who will
help. Glad you fixed it.

You can also right click on the queried range and select "data range
properties" in the future.
 
Back
Top