Formula returning unexpected reference.

[Stephersue]

I am trying to pull a list of equipment from a tasking list that reference an equipment code.

On a seperate tab I have input a count in Cell F6 with:
=COUNTIF('CP Board'!B1:B1999,"*PS")

and starting in A2 I am trying to use :

=IF(ROWS(A$2:A2)>F$1," ",INDEX('CP Board'!B$1:B$2000, SMALL( IF('CP Board'!B$1:B$2000="*PS",ROW('CP Board'!B$1:B$2000)),ROWS(A$2:A2))))

This is a sample of the raw data from the 'CP Board'! tab:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD] [TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]5RCP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]5RCP 160 01 PS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]5RCP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]5RCP 160 03 CC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]5RCP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]5RCP 160 03 CC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]CT-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]CT-1 150 01 PS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]CT-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99, align: left"]
<tbody>[TR="class: grid"]
[TD="width: 99"]CT-1 150 03 OS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]CT-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]CT-1 150 05 FM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]CT-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]CT-1 150 06 LS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]BATM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]BATM 100 01 PS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 40"]
<tbody>[TR="class: grid"]
[TD="width: 40"]BATM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR="class: grid"]
[TD="width: 99"]BATM 100 03 EC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


However Currently it is returning this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 275"]
<tbody>[TR="class: grid"]
[TD="width: 275"]5RCP 160 03 CC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 275"]
<tbody>[TR="class: grid"]
[TD="width: 275, align: center"]#NUM![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 275"]
<tbody>[TR="class: grid"]
[TD="width: 275, align: center"]#NUM![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thoughts Ideas Suggestions... I am going to use the values returned to VLookup equipment descriptions to give me my full equipment list with tonnage, etc.

Thanks for reading!

 

[Aladin Akyurek]

Try rather...

=IF(ROWS(A$2:A2)>F$1,"",INDEX('CP Board'!B$1:B$2000, SMALL(IF(ISNUMBER(SEARCH("PS"&"|",'CP Board'!B$1:B$2000&"|",ROW('CP Board'!B$1:B$2000)-ROW('CP Board'!B$1)+1),ROWS(A$2:A2))))

 

[Stephersue]

Giving me an Error :

"You've entered too many arguments for this function."

 

[Aladin Akyurek]

Try rather...

=IF(ROWS(A$2:A2)>F$1,"",INDEX('CP Board'!B$1:B$2000, SMALL(IF(ISNUMBER(SEARCH("PS"&"|",'CP Board'!B$1:B$2000&"|",ROW('CP Board'!B$1:B$2000)-ROW('CP Board'!B$1)+1),ROWS(A$2:A2))))

Giving me an Error :

"You've entered too many arguments for this function."

=IF(ROWS(A$2:A2)>F$1,"",INDEX('CP Board'!B$1:B$2000,
  SMALL(IF(ISNUMBER(SEARCH("PS"&"|",'CP Board'!B$1:B$2000&"|")),
  ROW('CP Board'!B$1:B$2000)-ROW('CP Board'!B$1)+1),ROWS(A$2:A2))))

 

[Stephersue]

THANK YOU!

It is working perfectly now.

 

[Aladin Akyurek]

THANK YOU!

It is working perfectly now.

You are welcome.