Problems with #Value erroe

[john.fleming]

I am a newbie to this forum so please excuse me if this thread is in the wrong area.
I am reasonably proficient at Excel but whilst I know the #value errors usually mean that I am trying to do something with two cells that are incompatible I would like a little help.

I am trying to capture information from multiple worksheets into a master one. I can easily do this when I am just pasting (using a + sign) but I have a problem.

In worksheet 'A' (Continental Europe budget spreadsheet) I use two tabs one holds details of budgets the other tab adds together cells and displays them if in the first tab if it meets certain criteria from the second tab.
=+IF('Events PO''s'!$B$2='Field Marketing'!$E$3,'Events PO''s'!$E$26,"")

This all works fine and when I copy the target field into Worksheet 'B' it does exactly what it is supposed to and shows me the calculated field I am expecting.

The problem is when I use a calculation on the target cell in worksheet 'B' (EMEA Budget combined) then I get an #VALUE error

=+'[Continental Europe budget spreadsheet Q1 2011.xls]Field Marketing'!G29/$R$1

The cell reference is the exchange rate between euros used in the continental europe worksheet and UK pounds used in the EMEA budget combined spreadsheet.

This is challenging as when I add data into worksheet 'A' it works well - I only get the #VALUE error when the cells are blank.

Can someone help- I would really like it to be simple (some hope)

 

[GlennUK]

How about:

=IF($R$1="","",'[Continental Europe budget spreadsheet Q1 2011.xls]Field Marketing'!G29/$R$1)

by the way, you don't need the plus sign, just an "=" will do.

 

[john.fleming]

GlennUK

Thanks for the help but it is only when there is no data in worksheet 'A' and I divide that cell by $R1$ that I get the #VALUE error

Any other suggestions? all are welcome

 

[Mike Blackman]

Hi John,

In Excel 2007 and later;

=IFERROR('[Continental Europe budget spreadsheet Q1 2011.xls]Field Marketing'!G29/$R$1,"")

In 2003 and previous;

=IF(ISERROR('[Continental Europe budget spreadsheet Q1 2011.xls]Field Marketing'!G29/$R$1),"",'[Continental Europe budget spreadsheet Q1 2011.xls]Field Marketing'!G29/$R$1)