Results 1 to 6 of 6

Thread: *HELP* Consistent Updating Fields

  1. #1
    Seeker mk_alii's Avatar
    Join Date
    Jun 2018
    Posts
    9
    Articles
    0
    Excel Version
    Excel 2013

    Question *HELP* Consistent Updating Fields



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys!

    I am looking for help with a formula (?) that would display the last ten of a table into another worksheet.

    I.E. I have 20 rows of data on worksheet 1, as I add a new row of data, I'd like my worksheet 2 to display just the last ten, always.

    Is this possible with a formula?

    Thanks in advance guys!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Try a forumula like this:

    =INDEX(Sheet1!A:A,COUNTIF(Sheet1!$A:$A,"<>")-10+ROWS($A$2:$A2))


    copied down 10 rows and across as far as necessary to capture all row info.

    Note: This assumes no blanks in column A of other sheet (Sheet1) and it assumes you are extracting from sheet1 starting at column A.


  3. #3
    Seeker mk_alii's Avatar
    Join Date
    Jun 2018
    Posts
    9
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by NBVC View Post
    Try a forumula like this:

    =INDEX(Sheet1!A:A,COUNTIF(Sheet1!$A:$A,"<>")-10+ROWS($A$2:$A2))


    copied down 10 rows and across as far as necessary to capture all row info.

    Note: This assumes no blanks in column A of other sheet (Sheet1) and it assumes you are extracting from sheet1 starting at column A.



    Hey there!!

    The formula did work!!! But I'm having trouble translating this into my Workbook.
    Can you re-write the formula if the worksheet I need to pull from is named "Scorecard Results" and the information needed to be pulled is: 'Scorecard Results'!$A$45:$K$5000,2,0)

    Let me know if this makes sense

    Thanks for your help!!!

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =INDEX('Scorecard Results'!A$45:A$5000,COUNTIF('Scorecard Results'!$A$45:$A$5000,"<>")-10+ROWS($A$2:$A2))

    copied down and across


  5. #5
    Seeker mk_alii's Avatar
    Join Date
    Jun 2018
    Posts
    9
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by NBVC View Post
    Try:

    =INDEX('Scorecard Results'!A$45:A$5000,COUNTIF('Scorecard Results'!$A$45:$A$5000,"<>")-10+ROWS($A$2:$A2))

    copied down and across

    Hi Friend!

    This worked you are amazing! Thank You!!!!

  6. #6
    Seeker mk_alii's Avatar
    Join Date
    Jun 2018
    Posts
    9
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by NBVC View Post
    Try:

    =INDEX('Scorecard Results'!A$45:A$5000,COUNTIF('Scorecard Results'!$A$45:$A$5000,"<>")-10+ROWS($A$2:$A2))

    copied down and across

    If anyone uses this in the future please note the "ROWS" Formula must reflect 10, 9, 8, 7 Etc. for every row below.
    If that makes sense I can clarify further if anyone has trouble with this!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •