Populate data based ona criteria

Hi,

Below is the table i am working with. i 'd like to populate data in cell c1, c2, c3, c4 based on a criteria. For example: If the criteria is 4 i want cells C1,C2,C3,C4 to display 11,23,304,40 respectively and not show anything in c5,c6.... and so on.

Is there a formula that can generate this result? Thanks

Column A Column B
Date             Sales
1/2/2016     11
1/9/2016     23
1/16/2016   304
1/23/2016   40
1/30/2016   50
2/7/2016    60
2/14/2016   70
2/21/2016   80
2/28/2016   90
3/7/2016    100
Who is Participating?

Commented:
This formula only populate if the row number is greater than G1+2. what if it is less than G1+2?

No, "<" = less than. For example, if G1 = 4, rows 2 to 5 will be shown. If G1 = 15, rows 2 to 16 will be shown.
0

Commented:
So you want only the first X number of records listed in column C?

Assuming the table headers are in Row 1 and the criteria is in cell G1, you can use this formula copied down as required....

=IF(ROW()<G\$1+2, B2, "")
0

Author Commented:
Hi Wayne,

Thanks for your response. This formula only populate if the row number is greater than G1+2. what if it is less than G1+2?

0

Author Commented:
0

Author Commented:
Your previous solution worked. however what if i want cells to display C1,C2,C3,C4 to display 70,80,90,100 respectively when G1=4 or C1,C2,C3,C4,C5 to display 60,70,80,90,100? basically last 4 or 4 rows of data rather than the first 4 or 5 rows?

Thank you so much for your help
0

Commented:
A bit more complicated, but this formula in cell C1 copied down will give what you request...

=IF(ISBLANK(INDEX(B:B, COUNTA(A:A)-G\$1+ROW())), "", INDEX(B:B, COUNTA(A:A)-G\$1+ROW()))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.