+ Reply to Thread
Results 1 to 9 of 9

Populate Field From Single Data Validation Selection

  1. #1
    Registered User
    Join Date
    08-20-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    74

    Populate Field From Single Data Validation Selection

    Greetings,

    I created 4 different tabs (‘Work’, ‘School’, ‘Home’, Volunteer’) with the same type of information (Date, Hours of Homework, Length of Break, and Sector) on each tab. For further reference, see the attachment for example.

    Additionally, I created a data validation list from these tabs.

    My questions are:

    (1) How do I create formulas that list the respective information (date, hours of homework, time length of break, and sector) from the selected tab?
    For Example: If I select Volunteer, I want all data from the 'Volunteer' tab to populate in the blank field on the ‘Report’ tab.

    (2) Once the data is populated and I am able to toggle back and forth between my 4 choices (Work, School, Home, and Volunteer), is there a way to create a line chart (graph) that displays hours of homework AND time length of break from the listed dates?

    This is a pretty in-depth request. Any feedback is much appreciated. Thanks!!!

    PLEASE IGNORE ATTACHMENT IN THIS MESSAGE,SEE NEW ATTACHMENT LISTED BELOW
    Attached Files Attached Files
    Last edited by warrior2411; 04-05-2013 at 08:04 PM.

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Populate Field From Single Data Validation Selection

    this may sound weird....but if was me....I would setup the data differently....I would have one worksheet with another column that had the 'Work, School, Home, Volunteer' and then have either one pivot table where I could filter whatever I wanted....or now have 4 worksheets that are pivot tables for each of the 4 types....the advantage is that you enter data in one WS and then all you have to do is click refresh pivot table and the all update..you don't have to worry about formula this or formula that.....just my 2 cents....
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    08-20-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Populate Field From Single Data Validation Selection

    judgeh59,

    Thanks for the reply, but I'm trying to avoid using pivot tables/pivot charts. I once saw an example, based on two dependent data validation lists, that populated a similar spreadsheet. However, this only has one data validation list. So, I'm currently at a loss

  4. #4
    Registered User
    Join Date
    08-20-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Populate Field From Single Data Validation Selection

    Please Note Attachment In This Message For Example

    DO2Report.xlsx

  5. #5
    Registered User
    Join Date
    04-05-2013
    Location
    dublin, Ireland
    MS-Off Ver
    Excel 2007/10
    Posts
    26

    Re: Populate Field From Single Data Validation Selection

    This may help

    Indirect function - very useful

    =+INDIRECT("'"&$B$4&"'!A2")

    this make a formula using cell references and others.

    If you put this intot he upper left column of your report table, it will reference the location in B4 to point it to the relevant tab.

    if you need further info, let me know

  6. #6
    Registered User
    Join Date
    08-20-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Populate Field From Single Data Validation Selection

    shanermuls,

    Thanks for your reply. My apologies! I accidentally uploaded the wrong attachment in the first message. Is your formula based on the attachment in the first message or the attachment in the message directly above yours? Thanks!

  7. #7
    Registered User
    Join Date
    04-05-2013
    Location
    dublin, Ireland
    MS-Off Ver
    Excel 2007/10
    Posts
    26

    Re: Populate Field From Single Data Validation Selection

    =+INDIRECT("'"&$C$21&"'!AB2")

    Updated for most recent spreadsheet.

    This effectively makes a formula which Excel sees as " = 'Work'!AB2 " if you change the location from work to home it will make a formula which Excel sees as "= 'Home'!AB2

  8. #8
    Registered User
    Join Date
    08-20-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Populate Field From Single Data Validation Selection

    shanermuls,

    Brilliant! Works like a charm! Thanks so much for your help!

  9. #9
    Registered User
    Join Date
    04-05-2013
    Location
    dublin, Ireland
    MS-Off Ver
    Excel 2007/10
    Posts
    26

    Re: Populate Field From Single Data Validation Selection

    no problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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