+ Reply to Thread
Results 1 to 3 of 3

Formula to ignore blank cells and copy data that meets criteria?

  1. #1
    Registered User
    Join Date
    10-26-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Formula to ignore blank cells and copy data that meets criteria?

    Hi, I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.

    In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.

    Is this possible? And could anyone please advise me on this.

    Regards
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to ignore blank cells and copy data that meets criteria?

    In A2

    =IF(ROWS(A$1:A1)>COUNTIF(Data!$E$2:$E$47,3),"",INDEX(Data!$A$2:$A$47,SMALL(INDEX((Data!$E$2:$E$47=3)*(ROW(Data!$E$2:$E$47)-ROW(Data!$E$2)+1),),COUNTIF(Data!$E$2:$E$47,"<>"&3)+ROWS(A$1:A1))))

    Copy down as far as needed

    Change Range Data!$E$2:$E$47 as per requirements of AM/PM
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    10-26-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Formula to ignore blank cells and copy data that meets criteria?

    Ace_XL that is spot on, thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Looking for a formula to reference a range of cells and return data if meets criteria
    By jeremyhawg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2014, 07:19 PM
  2. [SOLVED] SUMIFS Formula: Can it ignore a Blank Criteria field?
    By mlj61289 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2013, 12:43 PM
  3. Replies: 5
    Last Post: 05-18-2013, 07:49 PM
  4. [SOLVED] Copy and paste cells data once another cells value meets a certain criteria
    By gally in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2012, 05:03 AM
  5. Copy rows of data if date in cells meets certain date criteria
    By angrycartoon12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2010, 05:57 AM

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