Copying and pasting with SUMIF and COUNTIF

[mwdawg721]

I have a workbook with several sheets. One sheet serves as a 'dashboard' and
includes several SUMIF and COUNTIF formulas that reference cells on another
sheet. The other sheet contains information that is exported from a database
and pasted into the sheet on a regular basis. However, upon doing this, each
cell on the "dashboard" that contains a SUMIF or COUNTIF formula returns a
#REF! error message.

Is there a way to change this so that I can keep the SUMIF and COUNTIF
formulas and still be able to paste new info into the other sheet? Help
please!

 

[Paul C]

Have you tried using Copy, Paste Special, Values when bringing in your data?

 

[mwdawg721]

I tried pasting "values only" but received the same #REF! error upon
recalculating the formulas.

Is there a better way to setup my workbook so that I can continuously paste
in the new information without receiving these error messages?

 

[Gord Dibben]

Show us a few samples of your dashboard formulas that get messed up by
imported data.

Your formulas in dashboard sheet should not show #REF! if data on other
sheet simply gets overwritten with new data.

i.e. -COUNTIF(Sheet2!A1:A19,"mystring") on dashboard sheet.

Scenario..........If your import somehow deletes those cells in column A on
Sheet2 then #REF! will be returned.

Note: deletes not just clears contents.

See help on #REF! for possible causes and solutions.


Gord Dibben MS Excel MVP