Repeat formula on second sheet?

[smduello]

All -

I am using the following formula:

=OFFSET('Sheet 1'!A2,MATCH(C8,'Sheet 1'!A3:A229,0),MATCH(E4,'Sheet 1'!
B1:IV1,0))

to lookup data in a table. The problem is that my table is on to
worksheets due to the number of vertical columns. I can't do a range
across 2 sheets due to the nature of the Match formula. If it doesn't
find the item on the sheet, it returns #n/a. I was trying to use
=IF(ISERROR formula to tell it that if an #n/a comes back to go to the
next sheet (by repeating the above match code and just changing the
sheet reference), however, I haven't been succesful. Anyone have any
ideas on how I could do this?

 

[David McRitchie]

Something where Excel 2007 would help.

What you want to do is search your Sheet1 as you are
doing and if there is an error use the same formula with
Sheet2 that contains the additional columns.

See your HELP (IS Functions)
ISERR Value refers to any error value except #N/A.

ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!,
#NUM!, #NAME?, or #NULL!).


=IF(ISERR(old formula), sheet2 formula, oldformula)

For the ISERR(old formula) portion you only need that
part of the formula that cause a failure, but you can include
the entire old formula.

 

[smduello]

Thanks -

I read up on it under the HELP menu.

I tried a few things to see if this would work over three worksheets,
and I kept getting "TRUE". Is it possible to incorporate more than
just the 2 formulas?

 

[David McRitchie]

Simply add another level of nesting.

starting from the formula with additions to handle a
second sheet. This formula then becomes the original
as far as sheet3 is concerned.

=IF(ISERR(old formula), sheet2 formula, oldformula)

substitute your modified sheet formula for sheet 3 where
yousee sheet2 formula above

=IF(ISERR(old formula), sheet2 formula, oldformula)

new part would look like
=IF(ISERR(old formula), sheet2 formula, oldformula)
modified to
=IF(ISERR(sheet2 formula), sheet3 formula, sheet2 formula)
and inserted to replace sheet2 formula is what was originally suggested

=IF(ISERR(old formula), IF(ISERR(sheet2 formula), sheet3 formula, sheet2
formula), oldformula)
================= -----------------sheet3 insertions----------
===================

hope that lines up for you