I have a workbook that has two tabs. One tab contains the range to look up
from. the other tab is a report with calculations that draws numerical data
from the lookup table based on employee name.

The calculating sheet has multiple columns, four of which look up data, and
several others that calculate from the looked-up data. If I use this:
=IF(ISERROR(VLOOKUP($A10,Week3,3,FALSE)),0,VLOOKUP ($A10,Week3,3,FALSE))
everything is fine, as 0 is put in place of an error code.

However, this leaves me wondering if I have errors, so I changed the 0 to
"CHECK" so I could find problems.

BUT, if I am only on Week1 (of 4 weeks), week 2, 3, 4 have "CHECK" in the
fields as there is no data yet. This then throws all the calculations into
errors.

I'm trying to set this up to be used by multiple people who are not
experienced enough to play with the formulas. That's why the formulas are
already in week2-3-4.

Any suggestions for how to do error checking by week without throwing off
all the calculations?

You can trap for a particular error, such as ISNA, if the value of the blanks
returns NA as the error use the ISNA and check for all of the others
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karin" wrote:

I have a workbook that has two tabs. One tab contains the range to look up
from. the other tab is a report with calculations that draws numerical data
from the lookup table based on employee name.

The calculating sheet has multiple columns, four of which look up data, and
several others that calculate from the looked-up data. If I use this:
=IF(ISERROR(VLOOKUP($A10,Week3,3,FALSE)),0,VLOOKUP ($A10,Week3,3,FALSE))
everything is fine, as 0 is put in place of an error code.

However, this leaves me wondering if I have errors, so I changed the 0 to
"CHECK" so I could find problems.

BUT, if I am only on Week1 (of 4 weeks), week 2, 3, 4 have "CHECK" in the
fields as there is no data yet. This then throws all the calculations into
errors.

I'm trying to set this up to be used by multiple people who are not
experienced enough to play with the formulas. That's why the formulas are
already in week2-3-4.

Any suggestions for how to do error checking by week without throwing off
all the calculations?

Thank you, John, but I really don't understand your response. I use the ISNA
in the calculations or in the VLookup?

"John Bundy" wrote:

You can trap for a particular error, such as ISNA, if the value of the blanks
returns NA as the error use the ISNA and check for all of the others
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karin" wrote:

I have a workbook that has two tabs. One tab contains the range to look up
from. the other tab is a report with calculations that draws numerical data
from the lookup table based on employee name.

The calculating sheet has multiple columns, four of which look up data, and
several others that calculate from the looked-up data. If I use this:
=IF(ISERROR(VLOOKUP($A10,Week3,3,FALSE)),0,VLOOKUP ($A10,Week3,3,FALSE))
everything is fine, as 0 is put in place of an error code.

However, this leaves me wondering if I have errors, so I changed the 0 to
"CHECK" so I could find problems.

BUT, if I am only on Week1 (of 4 weeks), week 2, 3, 4 have "CHECK" in the
fields as there is no data yet. This then throws all the calculations into
errors.

I'm trying to set this up to be used by multiple people who are not
experienced enough to play with the formulas. That's why the formulas are
already in week2-3-4.

Any suggestions for how to do error checking by week without throwing off
all the calculations?

Further - there are potentially five columns that may return #N/A, in any or
all of the 5, with (for the sake of easy discussion) 1 column that totals
those five. How would I use ISNA in the totaling column? (It gets a litle
more complex...but this is a good start.)

"Karin" wrote:

Thank you, John, but I really don't understand your response. I use the ISNA
in the calculations or in the VLookup?

"John Bundy" wrote:

You can trap for a particular error, such as ISNA, if the value of the blanks
returns NA as the error use the ISNA and check for all of the others
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karin" wrote:

I have a workbook that has two tabs. One tab contains the range to look up
from. the other tab is a report with calculations that draws numerical data
from the lookup table based on employee name.

The calculating sheet has multiple columns, four of which look up data, and
several others that calculate from the looked-up data. If I use this:
=IF(ISERROR(VLOOKUP($A10,Week3,3,FALSE)),0,VLOOKUP ($A10,Week3,3,FALSE))
everything is fine, as 0 is put in place of an error code.

However, this leaves me wondering if I have errors, so I changed the 0 to
"CHECK" so I could find problems.

BUT, if I am only on Week1 (of 4 weeks), week 2, 3, 4 have "CHECK" in the
fields as there is no data yet. This then throws all the calculations into
errors.

I'm trying to set this up to be used by multiple people who are not
experienced enough to play with the formulas. That's why the formulas are
already in week2-3-4.

Any suggestions for how to do error checking by week without throwing off
all the calculations?

maybe...

=if($a10="","",if(iserror(.....

Then you won't get an error if A10 is empty.

Karin wrote:

I have a workbook that has two tabs. One tab contains the range to look up
from. the other tab is a report with calculations that draws numerical data
from the lookup table based on employee name.

The calculating sheet has multiple columns, four of which look up data, and
several others that calculate from the looked-up data. If I use this:
=IF(ISERROR(VLOOKUP($A10,Week3,3,FALSE)),0,VLOOKUP ($A10,Week3,3,FALSE))
everything is fine, as 0 is put in place of an error code.

However, this leaves me wondering if I have errors, so I changed the 0 to
"CHECK" so I could find problems.

BUT, if I am only on Week1 (of 4 weeks), week 2, 3, 4 have "CHECK" in the
fields as there is no data yet. This then throws all the calculations into
errors.

I'm trying to set this up to be used by multiple people who are not
experienced enough to play with the formulas. That's why the formulas are
already in week2-3-4.

Any suggestions for how to do error checking by week without throwing off
all the calculations?

--

Dave Peterson