Populate cells based on key cell

[Banshee]

I have two sheets, X and Y set up as follows:

Y:
A1:B1 contain column headers
A2:A10 contain levels, 1 through 5 in random order
B2:B10 contain strings

Example:
Level String
1 Test1
4 Test2
3 Test3
2 Test4
1 Test5
2 Test6
5 Test7
3 Test8
1 Test9

X:
B2 contains a number, 1 through 5.

Example: 2

I need to populate X A4:A* with all entries Y B2:B10 where Y A2:A10 <= B2

Desired output would be:
Test1
Test4
Test5
Test6
Test9

I'm sorry if this is confusing, and I appreciate any help you can offer.

Thanks!

 

[NBVC]

Try


Code:
--------------------
=IF(ROWS($A$1:$A1)>COUNTIF(Y!$A$2:$A$10,"<="&$B$2),"",INDEX(Y!$B$2:$B$10,SMALL(IF(Y!$A$2:$A$10<=$B$2,ROW(Y!$A$2:$A$10)-ROW(Y!$A$2)+1),ROWS($A$1:$A1))))
--------------------


confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[Pete_UK]

In SheetY cell C1 enter the header "seq", and put this formula in C2:

=IF(OR(A2="",A2<SheetX!B$2),"",COUNTIF(A$2:A2,">="&SheetX!B$2))

Copy this down as far as you need to (even beyond the data in columns
A and B if they are likely to grow).

Then put this formula in A4 of SheetX:

=IF(ISNA(MATCH(ROW(A1),SheetY!C:C,0)),"",INDEX(SheetY!B:B,MATCH(ROW
(A1),SheetY!C:C,0)))

and copy this down as far as you think you will need it.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, the first formula should be:

=IF(OR(A2="",A2>SheetX!B$2),"",COUNTIF(A$2:A2,"<="&SheetX!B$2))

as you want the values that are less than or equal to B2 in SheetX (my
earlier formula gives you greater than or equal to).

Hope this helps.

Pete

 

[Banshee]

Thank you to both NBVC and Pete_UK.

Both solutions solved my problem.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thank you to both NBVC and Pete_UK.

Both solutions solved my problem.













- Show quoted text -