+ Reply to Thread
Results 1 to 4 of 4

Pull each instance of a Set Criteria/Value in a coulmn

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    AZ
    MS-Off Ver
    2013
    Posts
    29

    Pull each instance of a Set Criteria/Value in a coulmn

    Hello all,
    I am currently working on a form that will automatically pull in only dates that fit a certain criteria. On the first page, as soon as it is opened, the current month and year fill in, this then sets the calendar on a separate sheet and assigns it the crew that will be working. I have set logic to place "True" to the right of ONLY the last day of a crews rotation and "False" to the right of all other days. On the first sheet I would like to recall every instance of "True" so that I have only the dates that are associated to the last week of that crews rotation.
    the cells I am pulling the data TO are B6:B17
    The date I want returned is =Sheet2!C3:C33
    the instance I am looking for "True" is =Sheet2!E3:E33
    what is the best way to do this? I can get the first instance using =INDEX(Sheet2!C3:C33,MATCH("True",Sheet2!E3:E33,0)) but am unsure as how to pull only the next instance of "True". etc. etc. ANY HELP?

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Pull each instance of a Set Criteria/Value in a coulmn

    Try this array formula**:

    =IFERROR(INDEX(Sheet2!C:C,SMALL(IF(Sheet2!E$3:E$33="True",ROW(Sheet2!E$3:E$33)),ROWS(B$6:B6))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-05-2016
    Location
    AZ
    MS-Off Ver
    2013
    Posts
    29

    Re: Pull each instance of a Set Criteria/Value in a coulmn

    Tony Valko! Thank you very much, this is going to help me tons! I'm given tons of documents to work on in just one day and this one I feel is actually important. Once I had endless time to slowly learn as I go but things seem more rushed these days, so you are a life saver. Thank you again! It works beautifully.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Pull each instance of a Set Criteria/Value in a coulmn

    You're welcome. Thanks for the feedback!

+ 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. enter data in coulmn the today date appear in other coulmn and doesn't change
    By sterio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2015, 04:13 PM
  2. Sum the first Instance of a duplicate across two criteria
    By nas783 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-30-2013, 10:35 PM
  3. Replies: 11
    Last Post: 01-30-2013, 03:11 PM
  4. Identify a range, pull first instance of a symbol, repeat
    By Jbm444 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2010, 07:01 PM
  5. Pull every instance INDEX/MATCH criteria into consecutive rows
    By PowerSchoolDude in forum Excel General
    Replies: 4
    Last Post: 01-13-2010, 06:42 PM
  6. Identifying First Instance of a Value Given a Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2006, 02:30 PM
  7. [SOLVED] Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2006, 10:35 PM

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