+ Reply to Thread
Results 1 to 16 of 16

Generating a list based on data in the next cell

  1. #1
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Generating a list based on data in the next cell

    So basically what i am looking to do is be able to select one of the day from the drop down menu in the daily attendance sheet and have it auto mantically populate from the students list if the have a yes next to their name for that day. I have included my desired result to give you an idea of what I'm looking for. Any all help would be greatly appreciated.

    TIA

    LOS
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Generating a list based on data in the next cell

    Hi
    Use this formula in C4 and copy down
    =INDEX(OFFSET(Students!$A$4:$A$89,0,MATCH('Daily Attendance'!$B$1,Students!$B$1:$J$1,0)),MATCH('Daily Attendance'!A4,Students!$A$4:$A$89,0),1)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Generating a list based on data in the next cell

    it is giving me a circular reference error.....

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,944

    Re: Generating a list based on data in the next cell

    In C1 =IF(B1="Monday",2, IF(B1="Tuesday",4,IF(B1="Wednesday",6,IF(B1="Thursday",8,10))))

    In c4 =VLOOKUP(A4,Students!$A$4:$J$89,INDIRECT("C1"),FALSE) and copy down
    Alan


    Have you debugged your code?
    Database Normalization
    What is Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Generating a list based on data in the next cell

    Quote Originally Posted by Losguapos1 View Post
    it is giving me a circular reference error.....
    Not for me. See the file Daily Attendance.xlsx

  6. #6
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Generating a list based on data in the next cell

    Maybe i wasn't clear with what i was looking to get out of the data. i need a sheet that will have the students name (Student 1, Student 2 etc) listed if they have a yes next to thier name. I want to be able to select the dat form the drop down list and have it give me all of the students who are enrolled on the at day.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,944

    Re: Generating a list based on data in the next cell

    Thread #4 worked for me on your worksheet, and Jose's also worked for me.

    Rereading your post. Do you not want to see the NO's. Only the Yeses. If a student is a No on a particular day, his name should not appear on the list. Is this what you are looking for? If yes, then I believe we will need to employ a VBA solution.
    Last edited by alansidman; 11-02-2015 at 07:23 PM.

  8. #8
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Generating a list based on data in the next cell

    Quote Originally Posted by alansidman View Post
    Thread #4 worked for me on your worksheet, and Jose's also worked for me.

    Rereading your post. Do you not want to see the NO's. Only the Yeses. If a student is a No on a particular day, his name should not appear on the list. Is this what you are looking for? If yes, then I believe we will need to employ a VBA solution.
    yes i am looking for it to return only the students names who have a yes next to them.

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Generating a list based on data in the next cell

    CSE formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    using combination of offset/if/small/index

    see attached result

    columns E to L are there just to cross check
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Generating a list based on data in the next cell

    actually second offset not required
    this should be suffice

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,944

    Re: Generating a list based on data in the next cell

    Here is a VBA solution. this will require that you remove merged cells (unmerge them) in the first sheet.

    Please Login or Register  to view this content.
    ..


    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Generating a list based on data in the next cell

    alan
    i think your select case is shifted off
    shouldnt it be 2/4/6/8/10?

    also i think your first SLR should be
    Please Login or Register  to view this content.
    otherwise you delete the students header

    **(if the data has been wiped already)

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Generating a list based on data in the next cell

    combined alan's solution with worksheet_change event based on B1
    Attached Files Attached Files

  14. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,944

    Re: Generating a list based on data in the next cell

    Thanks Hum

    Senior moments prevail.

    Alan

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,402

    Re: Generating a list based on data in the next cell

    This uses a helper column in Daily Attendance. It can of course be relocated or hidden. The formula for that is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in column B
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  16. #16
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,176

    Re: Generating a list based on data in the next cell

    Copy this in A4:

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down.

+ 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] Generating: letters from cell contents, & numbers based on number of cells containing data
    By excellenct in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2014, 06:14 AM
  2. [SOLVED] Generating new data based on cell value
    By dan12345 in forum Excel General
    Replies: 2
    Last Post: 09-05-2012, 12:10 AM
  3. Generating List based on Condition
    By andyb16 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2012, 06:37 AM
  4. Generating dates rang based on list of ranges with weekdays
    By Draekus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2011, 08:35 PM
  5. Generating List based on matching Criteria
    By jj72uk in forum Excel General
    Replies: 5
    Last Post: 06-14-2010, 02:12 PM
  6. Generating a 2nd list based off a 1st list.
    By Dulanic in forum Excel General
    Replies: 8
    Last Post: 02-09-2010, 11:07 AM
  7. Generating a detailed list based on data selected and it's information
    By Shananaaah in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-03-2009, 06:52 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