| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Check for errors
{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC! $Q$30:$T$30,1)),0)}
The above formula does not check for errors which results in #N/A being returned on some cells where the formula is used. I have changed it to: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC! $Q$30:$T$30,1)),0)INDEX(CC!$Q$31:$T$990,MATCH(OrdA ck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q $30:$T$30,1)),0)} this is obviously not correct, can someone help! Thanks Pat |
|
#2
|
|||
|
|||
|
I think that the portion of the formula that could break is when you look for
the matches for the column/row hed so maybe you could just test to make sure that the matches came back with numbers: =if(and(isnumber(j667), isnumber(MATCH(OrdAck!M667,CC!$C$31:$C$990,0) *MATCH(OrdAck!J667,CC!$Q$30:$T$30,1))), index(.....))) Since you're using isnumber() in the first test, you could include that in a single test: =if(isnumber(j667 * MATCH(OrdAck!M667,CC!$C$31:$C$990,0) * MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)), index(.....))) (Watch out for missing/extra parentheses!!) Pat wrote: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC! $Q$30:$T$30,1)),0)} The above formula does not check for errors which results in #N/A being returned on some cells where the formula is used. I have changed it to: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC! $Q$30:$T$30,1)),0)INDEX(CC!$Q$31:$T$990,MATCH(OrdA ck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q $30:$T$30,1)),0)} this is obviously not correct, can someone help! Thanks Pat -- Dave Peterson |
|
#3
|
|||
|
|||
|
Dave,
This is a belated response, I did not come across your message until I reset this newsgroup. Both your suggestions returned a result if J667 contained a value. However both formulas returned FALSE if J667 contained no value. It also made no difference if J667 is formatted as a number. I will repost a new message in the unlikely event you or others in the newsgroup do not see this message. Cheers Pat "Dave Peterson" wrote in message ... I think that the portion of the formula that could break is when you look for the matches for the column/row hed so maybe you could just test to make sure that the matches came back with numbers: =if(and(isnumber(j667), isnumber(MATCH(OrdAck!M667,CC!$C$31:$C$990,0) *MATCH(OrdAck!J667,CC!$Q$30:$T$30,1))), index(.....))) Since you're using isnumber() in the first test, you could include that in a single test: =if(isnumber(j667 * MATCH(OrdAck!M667,CC!$C$31:$C$990,0) * MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)), index(.....))) (Watch out for missing/extra parentheses!!) Pat wrote: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)} The above formula does not check for errors which results in #N/A being returned on some cells where the formula is used. I have changed it to: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)INDEX(CC !$Q$31:$T$990,MATCH(OrdAck !M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$3 0:$T$30,1)),0)} this is obviously not correct, can someone help! Thanks Pat -- Dave Peterson |
|
#4
|
|||
|
|||
|
Did you include the 0 at the end of your formula?
(the False portion of the if/then/else structure). (I'll look for the other post, too.) Pat wrote: Dave, This is a belated response, I did not come across your message until I reset this newsgroup. Both your suggestions returned a result if J667 contained a value. However both formulas returned FALSE if J667 contained no value. It also made no difference if J667 is formatted as a number. I will repost a new message in the unlikely event you or others in the newsgroup do not see this message. Cheers Pat "Dave Peterson" wrote in message ... I think that the portion of the formula that could break is when you look for the matches for the column/row hed so maybe you could just test to make sure that the matches came back with numbers: =if(and(isnumber(j667), isnumber(MATCH(OrdAck!M667,CC!$C$31:$C$990,0) *MATCH(OrdAck!J667,CC!$Q$30:$T$30,1))), index(.....))) Since you're using isnumber() in the first test, you could include that in a single test: =if(isnumber(j667 * MATCH(OrdAck!M667,CC!$C$31:$C$990,0) * MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)), index(.....))) (Watch out for missing/extra parentheses!!) Pat wrote: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)} The above formula does not check for errors which results in #N/A being returned on some cells where the formula is used. I have changed it to: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)INDEX(CC !$Q$31:$T$990,MATCH(OrdAck !M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$3 0:$T$30,1)),0)} this is obviously not correct, can someone help! Thanks Pat -- Dave Peterson -- Dave Peterson |
|
#5
|
|||
|
|||
|
Did you include the 0 at the end of your formula?
Here is the two formulas as you suggested: {=IF(AND(ISNUMBER(J664),ISNUMBER(MATCH(OrdAck!M664 ,CC!$C$31:$C$990,0)*MATCH( OrdAck!J664,CC!$Q$30:$T$30,1))),INDEX(CC!$Q$31:$T$ 990,MATCH(OrdAck!M664,CC!$ C$31:$C$990,0),MATCH(OrdAck!J664,CC!$Q$30:$T$30,0) ))} {=IF(ISNUMBER(J663*MATCH(OrdAck!M663,CC!$C$31:$C$9 90,0)*MATCH(OrdAck!J663,CC !$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!M663,CC!$C$31:$C$990,0), MATCH(OrdAck!J663,CC!$Q$30:$T$30,0)))} I hope I have interpeted them correctly. (I'll look for the other post, too.) I had been called away before I could send another post :) now you have seen this post there is now no need to repost ;) Cheers Pat "Dave Peterson" wrote in message ... Did you include the 0 at the end of your formula? (the False portion of the if/then/else structure). (I'll look for the other post, too.) Pat wrote: Dave, This is a belated response, I did not come across your message until I reset this newsgroup. Both your suggestions returned a result if J667 contained a value. However both formulas returned FALSE if J667 contained no value. It also made no difference if J667 is formatted as a number. I will repost a new message in the unlikely event you or others in the newsgroup do not see this message. Cheers Pat "Dave Peterson" wrote in message ... I think that the portion of the formula that could break is when you look for the matches for the column/row hed so maybe you could just test to make sure that the matches came back with numbers: =if(and(isnumber(j667), isnumber(MATCH(OrdAck!M667,CC!$C$31:$C$990,0) *MATCH(OrdAck!J667,CC!$Q$30:$T$30,1))), index(.....))) Since you're using isnumber() in the first test, you could include that in a single test: =if(isnumber(j667 * MATCH(OrdAck!M667,CC!$C$31:$C$990,0) * MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)), index(.....))) (Watch out for missing/extra parentheses!!) Pat wrote: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)} The above formula does not check for errors which results in #N/A being returned on some cells where the formula is used. I have changed it to: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)INDEX(CC !$Q$31:$T$990,MATCH(OrdAck !M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$3 0:$T$30,1)),0)} this is obviously not correct, can someone help! Thanks Pat -- Dave Peterson -- Dave Peterson |
|
#6
|
|||
|
|||
|
Your original formula had that ,0) at the end.
=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$99 0,0) *MATCH(OrdAck!J663,cc!$Q$30:$T$30,1)), INDEX(cc!$Q$31:$T$990,MATCH(OrdAck!M663,cc!$C$31:$ C$990,0), MATCH(OrdAck!J663,cc!$Q$30:$T$30,0)), 0) (still array entered) Pat wrote: Did you include the 0 at the end of your formula? Here is the two formulas as you suggested: {=IF(AND(ISNUMBER(J664),ISNUMBER(MATCH(OrdAck!M664 ,CC!$C$31:$C$990,0)*MATCH( OrdAck!J664,CC!$Q$30:$T$30,1))),INDEX(CC!$Q$31:$T$ 990,MATCH(OrdAck!M664,CC!$ C$31:$C$990,0),MATCH(OrdAck!J664,CC!$Q$30:$T$30,0) ))} {=IF(ISNUMBER(J663*MATCH(OrdAck!M663,CC!$C$31:$C$9 90,0)*MATCH(OrdAck!J663,CC !$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!M663,CC!$C$31:$C$990,0), MATCH(OrdAck!J663,CC!$Q$30:$T$30,0)))} I hope I have interpeted them correctly. (I'll look for the other post, too.) I had been called away before I could send another post :) now you have seen this post there is now no need to repost ;) Cheers Pat "Dave Peterson" wrote in message ... Did you include the 0 at the end of your formula? (the False portion of the if/then/else structure). (I'll look for the other post, too.) Pat wrote: Dave, This is a belated response, I did not come across your message until I reset this newsgroup. Both your suggestions returned a result if J667 contained a value. However both formulas returned FALSE if J667 contained no value. It also made no difference if J667 is formatted as a number. I will repost a new message in the unlikely event you or others in the newsgroup do not see this message. Cheers Pat "Dave Peterson" wrote in message ... I think that the portion of the formula that could break is when you look for the matches for the column/row hed so maybe you could just test to make sure that the matches came back with numbers: =if(and(isnumber(j667), isnumber(MATCH(OrdAck!M667,CC!$C$31:$C$990,0) *MATCH(OrdAck!J667,CC!$Q$30:$T$30,1))), index(.....))) Since you're using isnumber() in the first test, you could include that in a single test: =if(isnumber(j667 * MATCH(OrdAck!M667,CC!$C$31:$C$990,0) * MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)), index(.....))) (Watch out for missing/extra parentheses!!) Pat wrote: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)} The above formula does not check for errors which results in #N/A being returned on some cells where the formula is used. I have changed it to: {=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!M667,CC!$C$31:$C$990, 0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)INDEX(CC !$Q$31:$T$990,MATCH(OrdAck !M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$3 0:$T$30,1)),0)} this is obviously not correct, can someone help! Thanks Pat -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
| Auto spell check as in word | Excel Discussion (Misc queries) | |||
| check box formatting | Excel Discussion (Misc queries) | |||
| Using a Check Box as a control item | Excel Discussion (Misc queries) | |||
| Creating a check box that does not require security clearance. | Excel Worksheet Functions | |||
Hybrid Mode
