| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple corresponding Values (Biff.........)
Having trouble getting this formula to work. Im hoping its just something
small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
I think the problem is in the Row(98:98), which is looking for the 98th
smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
Im trying to pull all the rows that match a certain criteria. I already have
one of these formulas in place that pulls from another sheet but the range is 1:16200 and it works great. Here's a small quick layout: NY N Apt NY N Apt NY N Off NY N Off NY N Ret where NY is in A98, N in B98 and Apt in C98. I want all the rows that apply to Apt. "Bob Phillips" wrote: I think the problem is in the Row(98:98), which is looking for the 98th smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
I think that this is what you need
=INDEX($A$98:$AB$400,SMALL(IF($C$98:$C$400=$H$90,R OW($98:$400)),ROW(1:1))-MI N(ROW($A$98:$A$400))+1,1) still an array formula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Im trying to pull all the rows that match a certain criteria. I already have one of these formulas in place that pulls from another sheet but the range is 1:16200 and it works great. Here's a small quick layout: NY N Apt NY N Apt NY N Off NY N Off NY N Ret where NY is in A98, N in B98 and Apt in C98. I want all the rows that apply to Apt. "Bob Phillips" wrote: I think the problem is in the Row(98:98), which is looking for the 98th smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
Bob, I copied the exact formula over and used the array but I am still
getting an error. When I F2 the formula to see where everything is pulling from it looks to me that the ROW(1:1) is the problem. The first row of the spreadsheet row 1 is outlined. I would have thought that row 98 would have been out lined but if i change the (1:1) to (98:98) I still get an error. Any thoughts???????-Thanks "Bob Phillips" wrote: I think that this is what you need =INDEX($A$98:$AB$400,SMALL(IF($C$98:$C$400=$H$90,R OW($98:$400)),ROW(1:1))-MI N(ROW($A$98:$A$400))+1,1) still an array formula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Im trying to pull all the rows that match a certain criteria. I already have one of these formulas in place that pulls from another sheet but the range is 1:16200 and it works great. Here's a small quick layout: NY N Apt NY N Apt NY N Off NY N Off NY N Ret where NY is in A98, N in B98 and Apt in C98. I want all the rows that apply to Apt. "Bob Phillips" wrote: I think the problem is in the Row(98:98), which is looking for the 98th smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
Bob, I found the problem.......I had some #NUM's after my info so I think the
formula was looking to that. When I changed the range the formula worked. However the range of values will always be changing and I dont want to have to change the formula range every time. Is there a way I can get the formula to ignore the #NUM?? "Bob Phillips" wrote: I think that this is what you need =INDEX($A$98:$AB$400,SMALL(IF($C$98:$C$400=$H$90,R OW($98:$400)),ROW(1:1))-MI N(ROW($A$98:$A$400))+1,1) still an array formula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Im trying to pull all the rows that match a certain criteria. I already have one of these formulas in place that pulls from another sheet but the range is 1:16200 and it works great. Here's a small quick layout: NY N Apt NY N Apt NY N Off NY N Off NY N Ret where NY is in A98, N in B98 and Apt in C98. I want all the rows that apply to Apt. "Bob Phillips" wrote: I think the problem is in the Row(98:98), which is looking for the 98th smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
GOT IT!!! I just used a IF(ISERROR( and everything seems to be going fine
now. Thanks again for your help. "Ribeye" wrote: Bob, I found the problem.......I had some #NUM's after my info so I think the formula was looking to that. When I changed the range the formula worked. However the range of values will always be changing and I dont want to have to change the formula range every time. Is there a way I can get the formula to ignore the #NUM?? "Bob Phillips" wrote: I think that this is what you need =INDEX($A$98:$AB$400,SMALL(IF($C$98:$C$400=$H$90,R OW($98:$400)),ROW(1:1))-MI N(ROW($A$98:$A$400))+1,1) still an array formula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Im trying to pull all the rows that match a certain criteria. I already have one of these formulas in place that pulls from another sheet but the range is 1:16200 and it works great. Here's a small quick layout: NY N Apt NY N Apt NY N Off NY N Off NY N Ret where NY is in A98, N in B98 and Apt in C98. I want all the rows that apply to Apt. "Bob Phillips" wrote: I think the problem is in the Row(98:98), which is looking for the 98th smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple corresponding Values (Biff.........)
especially my help this afternoon <vbg
Bob "Ribeye" wrote in message ... GOT IT!!! I just used a IF(ISERROR( and everything seems to be going fine now. Thanks again for your help. "Ribeye" wrote: Bob, I found the problem.......I had some #NUM's after my info so I think the formula was looking to that. When I changed the range the formula worked. However the range of values will always be changing and I dont want to have to change the formula range every time. Is there a way I can get the formula to ignore the #NUM?? "Bob Phillips" wrote: I think that this is what you need =INDEX($A$98:$AB$400,SMALL(IF($C$98:$C$400=$H$90,R OW($98:$400)),ROW(1:1))-MI N(ROW($A$98:$A$400))+1,1) still an array formula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Im trying to pull all the rows that match a certain criteria. I already have one of these formulas in place that pulls from another sheet but the range is 1:16200 and it works great. Here's a small quick layout: NY N Apt NY N Apt NY N Off NY N Off NY N Ret where NY is in A98, N in B98 and Apt in C98. I want all the rows that apply to Apt. "Bob Phillips" wrote: I think the problem is in the Row(98:98), which is looking for the 98th smallest value, which seems unlikely. What exactly are you trying to do? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ribeye" wrote in message ... Having trouble getting this formula to work. Im hoping its just something small I'm missing.....My info is in A98:AB400 the values I want to evaluate are in column C. Here is my current formula: =INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($9 8:$400)),ROW(98:98)),1) I've tried to change the ranges, the rows and everthing else and I still get error formulas........Please help to end my suffering...... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Biff - SPSS Output Exported as Values and Not Image | Excel Discussion (Misc queries) | |||
| BIFF - Help!! lookup numbers in multiple columns and return one nu | Excel Worksheet Functions | |||
| Biff I need your help!! | Excel Worksheet Functions | |||
| Hello Biff are u here | Excel Discussion (Misc queries) | |||
| To Biff: Using Match with multiple criteria | Excel Worksheet Functions | |||
Linear Mode
