Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

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
0
Dokat
Asked:
Dokat
  • 3
  • 3
1 Solution
 
Wayne Taylor (webtubbs)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
 
DokatAuthor 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?

Thanks for your response?
0
 
Wayne Taylor (webtubbs)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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DokatAuthor Commented:
Thank you for your help!
0
 
DokatAuthor 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
 
Wayne Taylor (webtubbs)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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now