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!

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(She etY!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


On Sep 8, 3:52*pm, Banshee wrote:
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!

Sorry, the first formula should be:

=IF(OR(A2="",A2SheetX!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

On Sep 8, 4:16*pm, Pete_UK wrote:
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(She etY!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

On Sep 8, 3:52*pm, Banshee wrote:



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!- Hide quoted text -

- Show quoted text -

Thank you to both NBVC and Pete_UK.

Both solutions solved my problem. :)

"Banshee" wrote:

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!

You're welcome - thanks for feeding back.

Pete

On Sep 8, 4:44*pm, Banshee wrote:
Thank you to both NBVC and Pete_UK.

Both solutions solved my problem. :)



"Banshee" wrote:
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!- Hide quoted text -

- Show quoted text -