+ Reply to Thread
Results 1 to 4 of 4

Report list of people attending

  1. #1
    Registered User
    Join Date
    04-29-2013
    Location
    London
    MS-Off Ver
    Excel MAC 2011
    Posts
    16

    Report list of people attending

    I have a guest list, with attendance marked yes, no or blank. On a separate sheet, i'd like a report of all those that said yes.
    At the moment I'm using the below, but that leaves lots of blank cells, which is not very useful. Can anyone help?


    =IF(lista!H4="yes",lista!A4,"")

    there's also a listb

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Report list of people attending

    A very common problem.

    Solution may look a bit daunting.

    http://www.cpearson.com/excel/NoBlanks.aspx
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    03-06-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Report list of people attending

    One solution I've used is to create a dynamic list, I used this as a guide to create it.

    https://www.youtube.com/watch?v=6bGKhbUYOas

  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: Report list of people attending

    Try one of these...

    Data Range
    H
    I
    J
    K
    1
    Name
    Status
    ------
    Name
    2
    Pam
    Yes
    Pam
    3
    Bob
    No
    Tom
    4
    Tom
    Yes
    Jill
    5
    Jill
    Yes
    Carl
    6
    Karen
    No
    Eric
    7
    Tim
    8
    Tony
    No
    9
    Lisa
    No
    10
    Biff
    No
    11
    Carl
    Yes
    12
    Tracy
    13
    Eric
    Yes
    14
    Paul
    No
    15
    Wolf
    No


    Don't know about Mac Excel, but if the AGGREGATE function is available use this formula entered in K2:

    =IFERROR(INDEX(H:H,AGGREGATE(15,6,1/(I$2:I$15="Yes")*ROW(I$2:I$15),ROWS(K$2:K2))),"")

    Otherwise, use this array formula** entered in K2:

    =IFERROR(INDEX(H:H,SMALL(IF(I$2:I$15="Yes",ROW(I$2:I$15)),ROWS(K$2:K2))),"")

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

    Let's Go Pens. We Want The Cup.

+ 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] Excel - looking for subject and copying attending student
    By Bryony309 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-27-2014, 08:03 AM
  2. Give an "X" the value of a 1. Show how many people are attending a meeting
    By CDSProgAsst in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2013, 07:00 PM
  3. [SOLVED] Formula for how many from each of the three departments are attending
    By gdub72 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-01-2013, 08:15 AM
  4. Access - Creating report requiring password/username for multiple people
    By shadestreet in forum Access Tables & Databases
    Replies: 2
    Last Post: 04-25-2009, 04:56 AM
  5. Calculate Number Attending
    By Michael Koerner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-05-2005, 02:06 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