+ Reply to Thread
Results 1 to 4 of 4

Find & return all non-zero values within a range, on a different sheet

  1. #1
    Registered User
    Join Date
    04-09-2012
    Location
    NorCal
    MS-Off Ver
    Excel 2010
    Posts
    2

    Find & return all non-zero values within a range, on a different sheet

    Hi - I have searched and searched for the correct approach to solving my problem and have gotten only close. Any help will be much appreciated.

    I need to be able to search a range of cells (one column only), most of which do not have data in them, and have all the non zero values returned on a different worksheet, in a range that's a different size. For example:

    Sheet 1 has:

    1|
    2|
    3| .05
    4|
    5|
    6| .02
    7|
    8|
    9|
    10| .01

    And I need Sheet 2 to show:

    1| .05
    2| .02
    3| .01
    4|
    5|

    For one, the data entered will not always be entered in rows 3, 6 and 10, so it needs to be able to search all the rows. We do however know that there will be no more than 5 data entries (this is the size of the destination range).

    Second, the fact that the source range and the destination range are different sizes can't be changed.

    Third, Sheet 2 needs to auto-update when a value on Sheet 1 changes. This is why a filter wasn't enough, unless I was just missing something.

    If the blank cells are an issue, zeros can be inserted.

    Any insight here will be greatly appreciated. I have struggled to find the right formula/function for this and can't find one that meets each of the requirements.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Find & return all non-zero values within a range, on a different sheet

    Assuming that both sheets have their data in column A then in cell A1 on sheet2 try:

    =INDEX(Sheet1!A:A,LARGE(INDEX(ROW(Sheet1!A1:A1000) * (Sheet1!A1:A1000 <> ""),0),COUNTA(Sheet1!A:A)-(ROW(1:1)-1)))

    And drag down 5 cells.

    Edited to add: Just to be neat, and in case of their being less values than 5, you should encase this in an IFERROR statement ...

    =IFERROR(INDEX(Sheet1!A:A,LARGE(INDEX(ROW(Sheet1!$A$1:$A$1000)*(Sheet1!$A$1:$A$1000<>""),0),COUNTA(Sheet1!A:A)-(ROW(1:1)-1))),"")
    Last edited by Andrew-R; 04-10-2012 at 12:59 PM.

  3. #3
    Registered User
    Join Date
    04-09-2012
    Location
    NorCal
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Find & return all non-zero values within a range, on a different sheet

    Thank you Andrew for the quick reply. I knew I had left something out - the source and destination ranges don't match - they start in J10 on Sheet 1 and M126 on Sheet 2.

    However, if aligning the addresses of the first cell in both ranges is the only way to make it work, I can probably make that happen somehow. I will tinker with it.

    Thanks again for your the help!

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Find & return all non-zero values within a range, on a different sheet

    Hello lesaint,

    If it is valid numbers, you can use either SMALL/LARGE. This will also sort the numbers in ascending/descending orders.

    In Sheet2 M126

    Please Login or Register  to view this content.
    In M127

    Please Login or Register  to view this content.
    Then copy this cell & paste down as needed.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1