Hi all

Is it possible to highlight a range of cells (all of which contain formulas), copy and paste as values but ignoring all formulas that are currently showing as #REF!

Hi,

I think this is one of those cases where it's better to cure the problem at the root than cover up the result.

What is causing the #REF! error?

Hi Spencer,

The #REF! errors are expected.

I have a workbook containing surveys that are linked to a results sheet through a series of indirect formulas. There are 409 condition surveys to be input but only a 100 in there now.

When the missing surveys are input into the main workbook, the indirect formulas grab all the necessary fields.

What I wanted to do was to paste all the current results as values whilst leaving the missing ones so that when I add the sheet to the workbook, it will populate the results sheet. (Does that make sense)?

This will then allow me to delete all the surveys reducing the size of workbook and allowing me to have all the results in one table.

Thanks

So would all the #REF! errors be at the end of the list or dotted throuought the data?

If at the end, you could possible wrap your formulas with =IFERROR() which would force the results to be blank cells rather than #REF!

Would that work for you?

Have emailed you the workbook I'm talking about if you want to take a look. Better than trying to explain it.

Yeah the #REF! errors are scattered about throughout the results sheets.

Not sure if an =IFERROR would work as it would then paste those cells as blanks when they would still need to have the active indirect formulas for when the matching survey is imported to the workbook.