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!

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

--
If this helps, please remember to click yes.


"mwdawg721" wrote:

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!

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?

"Paul C" wrote:

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

--
If this helps, please remember to click yes.


"mwdawg721" wrote:

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!

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



On Mon, 26 Oct 2009 12:29:02 -0700, mwdawg721
wrote:

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?

"Paul C" wrote:

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

--
If this helps, please remember to click yes.


"mwdawg721" wrote:

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!