Cell reference error in Excel

[COII]

Dear all,

I have a question about cell reference. I have two spreadsheets in Excel File. One sheet named "Month", and the other named "Summary". "Summary" have formula for counting data of "Month". I should get updated data for each month. Like this:

Step 1: Delete all data in "Month" sheet
Step 2: Copy updated data to "Month" sheet

Problem: All formulas in "Summary" cannot work. (Formula bar will show "#REF..&quot

Anyone can me solve it?

Thank You.

COII

 

[TropicalFred]

I think we need a little more information. What is the formula on the "Summary" sheet, and how are you deleting the data on the "Month" sheet? If you are just highlighting the range of data and using the delete key, any formulas referencing those cells will remain unchanged. If , however, you are using Edit > Delete > Entire Row (or Entire Column) or if you are using the Delete Rows or Delete Columns icons from the toolbar, any formulas referencing those cells will change and may become corrupted.

Hope this helps.

-Fred

 

[Nareik]

Instead of deleting the data in 'Month', use the option 'clear contents'. (From the edit menu)

Deleting the cells can oftentimes screw up formula cell references as the reference to the formual is being deleted, therefore the #ref error.

Kieran

 

[COII]

Actuallly, I copied data from Access file to Excel every month. I will paste them to "Month" sheet. Before it, I must delete old data in "Month". Select the data range and press delete. Formula in "Summary" cannot work immediately. Display as #REF!.

How can I do?

COII

 

[TropicalFred]

Sounds like your formulas have already lost their refernces and need to be re-entered. What do the formulas look like now? Do you know what the formulas were (=Count("A1:A100"), for example) before you started getting the #REF error?

-Fred

 

[COII]

The formula is:

B2 =COUNTIF(month!J:J,"STUDENT"), something like that.

COII