Blank data fields (2000)

Topic

New Reply

Hello,

I have multiple sheets in one workbook. The first sheet imports the data from Access.

The second sheet does calculations based upon the data from the first sheet.

We are using this workbook to analyze data. We are constantly importing data from Access, looking at it, clearing it and importing (different) data. It works fine unless the data has less columns than the previous data imported. The second sheet will then give the error message #REF! wherever there is blank data in the first sheet. The only way to correct this is to copy the formulas over the error and it corrects it.

What am I doing wrong?

Does this make sense?

Reply | Quote

Replies

When you clear the old data, do you delete the columns ?. If so this will cause probelms with any formulae that reference the columns (or rows).

If this is the case, just delete the existing data by using Edit, Clear Contents. Or just press the delete key.

Andrew C

Reply | Quote

Thanks for your reply. I am doing exactly that – edit/clear contents. It does not work.

Reply | Quote

Could you post a sample formula that evaluates as #REF so that we can see what is happening. Does the data that is cleared contain formulas ?

Andrew C

Reply | Quote

I wonder if this is the old problem of Access importing blank fields to NullStrings in Excel, and that is why the formulas error out, though as a possible solution it doesn’t exactly fit the problem described.

Reply | Quote

Attached is a copy of the workbook. The first sheet called “Data” is where the data is imported. To import, I go to Data/Get External Data/New Database Query…then I find my Access Database and then the table which holds the data. After this initial set up, I clear the cells in “Data” and import again.

The second worksheet is called “Scratch”. This is where the problem lies. The cells seem to lose their references/calculations and addsin #REF! in some cells always before the last cell. ???

Thanks for looking at this.

Thanks.

Reply | Quote

On the Scratch worksheet the row reference in the formula row immediately above the error rows refer to Data Row 24, and the formula in the row immediately below the error rows refers to the very next row in the Data, Row 25. I susxpect that the #REFs are occurring because rows are being deleted on the Data sheet.

Reply | Quote

Hi Julie,

Change your formula in A11 on the Scratch worksheet to:
=IF(OFFSET(Data!$A$1,ROW()-10,column())=””,””,OFFSET(Data!B$1,ROW()-10,))
then copy this across the range A11:B43 and the #REF! errors will dissapear. This works because the OFFSET formula will be immune to the deletion of any row except row 1 and any column except column A on your Data sheet. It could even be made immune to that too, but I doubt it’s necessary in this case.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thank you for your response.

It will work with column A – because there are no calculations. However, I can not copy it over into column B because it has calculations.

Thanks.

Reply | Quote

Hi Julie,

That solution should work equally well for both columns A & B from the test workbook that you posted. If you have more complex formulae in Column B on the Scratch worksheet that need to take account of the changing numbers of data rows, that too should be possible. If you could post a (sanitized) workbook showing the more complex formulae, I’m sure that I and others here would be happy to have a got at modifying them to suit.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Julie

In the properties of your query you have left it at the default setting of Insert/Delete for new data. It might be worthe trying the Overwrite/Clear setting instead

HTH

Peter

Reply | Quote

Thank you! Thank you!! Thank you!!! to everyone who has been helping.

By changing the properties, it works. I have imported new data with varying lengths about 6 times and so far no #REF!.

Reply | Quote

I addition to Andrew’s request for a sample, can you describe exactly the steps being used to import the data from Access?

Reply | Quote