Using INDEX SMALL array function to pull rows of data from another worksheet

[imwhoim]

I'm trying to create a workbook that has a summary page as the first sheet, where you select a piece of equipment from a dropdown menu and have it auto-populate data about that equipment on the summary sheet. My roadblock is in trying to create a table on that "Summary" sheet that pulls rows of data from a "Records" sheet that contains multiple columns of data (in columns B-F, i.e. date, type of service, parts used, description) for each row of data that corresponds to a piece of equipment (Equipment name located in column A), of which there are multiple instances. I've found a useful INDEX/SMALL/IF function, but it only seems to work when being used on the "Records" sheet. If I copy the same function onto the "Summary" sheet and change the ranges to reference back to the "Records" sheet, it doesn't return any data (no error, just a blank cell). Example:

On the "Records" sheet I utilized this function (where I15 is the piece of equipment I want info on):

=IFERROR(INDEX($A$1:$F$20,SMALL(IF($A$1:$A$20=$I$15,ROW($A$1:$A$20)),ROW()-2),2),"")

Then I try to utilize the same function on the "Summary" sheet by using (where C4 if the piece of equipment I want info on):

=IFERROR(INDEX(Records!$A$1:$F$20,SMALL(IF(Records!$A$1:$A$20=$C$4,ROW(Records!$A$1:$A$20)),ROW()-2),2),"")

Am I not able to reference multiple sheets using this function? Does the range being referenced and the array have to be on the same sheet?

Thanks in advance for the help!

 

[AFPathfinder]

You're probably getting a blank because of the IFERROR function. I'd assume it's a reference issue between the sheet names.

With that said, in-cell functions/formulas can only affect the value of the cell they are in (UDFs might provide more, but I've never tried).

If you want to keep away from VBA, look into using the advanced filter. If you're trying to have an autotable adjust based on the selection of an equipment piece from a data validation list or combobox, VBA would be the solution.

 

[AhoyNC]

What happens if you change your formula to:

[TABLE="width: 917"]
<colgroup><col width="917"></colgroup><tbody>[TR]
[TD="width: 917"]

[TABLE="width: 917"]
<colgroup><col width="917"></colgroup><tbody>[TR]
   [TD="width: 917"]=INDEX(Records!$A$1:$F$20,SMALL(IF(Records!$A$1:$A$20=$C$4,ROW(Records!$A$1:$A$20)-ROW(Records!$A$1)+1),ROWS($A$1:A1)),2)[/TD]
 [/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[imwhoim]

With this formula I get a #NUM ! error....

What happens if you change your formula to:

[TABLE="width: 917"]
<tbody>[TR]
[TD="width: 917"]

[TABLE="width: 917"]
<tbody>[TR]
[TD="width: 917"]=INDEX(Records!$A$1:$F$20,SMALL(IF(Records!$A$1:$A$20=$C$4,ROW(Records!$A$1:$A$20)-ROW(Records!$A$1)+1),ROWS($A$1:A1)),2)[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

This is an array formula. Did you enter it with CTRL-SHIFT-ENTER? If not put cursor in cell with formula press F2 for edit and then CTRL-SHIFT-ENTER.
Also check to make sure there is a match for C4 in column Records!A1 to A20 (make sure one isn't text and the other is numeric).
Does your data have a header row (say in row 1)?
If this doesn't help post a small sample of your data. One way is to put a border around the data and then use copy paste.