+ Reply to Thread
Results 1 to 7 of 7

Formula to display data validation results on another sheet based on data chosen?

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    10

    Formula to display data validation results on another sheet based on data chosen?

    Hello,

    I am working furiously on a tracker for my job, but I am relatively new to the Excel formulas and am having some trouble. I am almost finished, but there is one final touch that I am trying to do, if possible. In the image below, I am on Sheet 2. Columns J-L have a data selection of --, Yes, or No. Column B will have an individual's name. I am trying to see if it is possible so that when No is chosen (data source cell C103) in any of the cells in Columns J-L, the individual's name from that row will show up on Sheet 1's B13, B14, B15, etc. Is this possible??

    Ex. Sheet 2, Row 4, Column B reads John Doe. J4 reads NO, but K4-M4 read YES.
    Because J4 reads NO, John Doe's name shows on Sheet 1 B13.

    Is it possible?


    Spreadsheet sample.bmp
    Last edited by myoung5149; 10-16-2013 at 02:14 PM. Reason: Correct the title

  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: What complex formula would I use to...

    Quote Originally Posted by myoung5149 View Post

    Is it possible?
    Yes!

    Attach a SMALL sample file and I'll show you how to do it!

    SMALL = no more than 20 rows worth of data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    10-10-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: What complex formula would I use to...

    Quote Originally Posted by Pepe Le Mokko View Post
    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
    Thank you for the notice. This has been corrected.
    Last edited by myoung5149; 10-12-2013 at 11:31 AM.

  4. #4
    Registered User
    Join Date
    10-10-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: What complex formula would I use to...

    Thanks, Tony!

    Will this suffice as a sample file??

    I should expand on my original request: the number of names on the data sheet will not match the number of names on the results sheet. For example, there may be 10 names on the data sheet, but I only want those names with a NO chosen to show up on the results page, which could be just one or two of them.

    Test Workbook.xls
    Last edited by myoung5149; 10-12-2013 at 11:49 AM.

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

    Re: What complex formula would I use to...

    Your profile says you use Excel 2007 so the formula is written to work in Excel 2007 and later.

    Try this array formula** entered in A2:

    =IFERROR(INDEX('Data Sheet'!B:B,SMALL(IF(MMULT(--('Data Sheet'!J$2:M$15="No"),{1;1;1;1})>0,ROW('Data Sheet'!J$2:M$15)),ROWS('Data Sheet'!A$2:A2))),"")

    ** 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.

  6. #6
    Registered User
    Join Date
    10-10-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: What complex formula would I use to...

    Tony, that worked like a charm!! Thanks so much for your help!

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

    Re: What complex formula would I use to...

    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. Complex IF / THEN formula
    By BaylorDad in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2013, 10:57 AM
  2. Complex IF formula
    By elanum in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2013, 08:12 PM
  3. Complex IF Formula
    By davo1224 in forum Excel General
    Replies: 8
    Last Post: 12-15-2010, 05:39 PM
  4. Complex formula
    By Marc in forum Excel General
    Replies: 8
    Last Post: 10-19-2005, 10:05 PM
  5. Complex formula?
    By Jean in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2005, 08:05 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