Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat formula on second sheet?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat formula on second sheet?
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. -- HTH, David McRitchie, Microsoft MVP -- Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat formula on second sheet?
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? On Aug 30, 10:06 am, "David McRitchie" wrote: 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. -- HTH, David McRitchie, Microsoft MVP -- Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm wrote in message oups.com... 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?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat formula on second sheet?
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 -- HTH, David McRitchie, Microsoft MVP -- Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm wrote in message ups.com... 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? On Aug 30, 10:06 am, "David McRitchie" wrote: 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. -- HTH, David McRitchie, Microsoft MVP -- Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm wrote in message oups.com... 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?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter rows to repeat on sheet tab? | Excel Discussion (Misc queries) | |||
repeat an entry on spread sheet | Excel Worksheet Functions | |||
repeat an entry on spread sheet | Excel Worksheet Functions | |||
how can I repeat row heights in the same sheet | Excel Discussion (Misc queries) | |||
In Excel Sheet any digit repeat | Excel Worksheet Functions |