Excel Magic Trick 913: Select From Drop Down and Pull Data From Different Sheet

February 19, 2015 by Admin  
Filed under Learning Magic Tricks

Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to: 1. Select From Drop Down and Pull Data From Different Sheets using Data Vali…
Video Rating: 4 / 5


25 Responses to “Excel Magic Trick 913: Select From Drop Down and Pull Data From Different Sheet”
  1. Awi Cobranded says:

    Good Stuff! Possible to have multiple drop down? Example: you have multiple
    stores with revenue and expenses on each sheet. “drop down” Jan 15. “drop
    down” Smith street.

  2. Almiro Joao says:

    exceptional that someone takes his time to help others. Simple and well

  3. Jacob Gies says:

    How can i do this in one sheet with different fields?

  4. Miguel Castillo says:

    I just want to thank you so much for your videos, this helped me alot! You
    make me look like an Excel genious to my boss. 

  5. joopz0r says:

    First off thank you for all your advice and effort, its greatly
    appreciated. I’ve read through this section but have not quite found /
    understood what I need for my “issue”.

    I am trying to create a pay sheet for my staff. We have various staff on
    different pay grades. We have a separate excel sheet with all our staff
    names, numbers, paygrade, hourly overtime rate and various other details in
    rows. e.g. A2=name, B2=number, B3=rate, etc.

    I would like to, using a drop down list, select the staff member by name
    and then in the subsequent columns have the required information (number,
    rate, overtime rate) inserted automatically in the subsequent columns in
    the pay sheet. e.g. in C11 we select the staff member by name. Once
    selected we require C11, C12, C13 and C14 in that sheet to be filled with
    the corresponding information of that staff member.

    The rest of the paysheet is fine, it calculates the remuneration with no

    Could you also include the formula for getting the data from a separately
    saved workbook (we have the staff list stored in the cloud).

    Thanks in advance!

  6. Fagner Furtado says:

    You can do it with different Workbooks?

  7. Alfredo F. Lavigat says:

    Awesome video….
    How do you manage to bring total from each sheet if the totals are
    referencing difference rows? Thanks

  8. Fareen Mohamed says:

    hi, im a frequent viewer of your excel tutorials, they are the best on
    youtube and have really helped me in my work. however, i need your expert
    advise for a specific type of excel im trying to set up. it should have a
    number of drop downs whereby the first drop down automatically prompts
    related items to be selected on the second drop down and the second should
    have the same effect on the third and so on. the last drop down should
    tally hours and give total number of hours worked.

    to be specific what i need is this:
    a drop down for courses taught
    that gives options of subjects taught in second drop down
    that gives option of tutors names who teach it in the third
    that gives option of room/ or number of students/ or semester/ in the

    that leads to a drop down with timing of eact subject, this follows by a
    final drop down that gives the hours that were spent teaching by.

    please please help me, im in desperate need of a solution to this in is a
    project that was due last month but i have stalled my boss by telling him
    im still gathering data.
    i have all the data, i just need a way of sorting it, that works
    particularly to the above method.

    your help will be forever appreciated !


  9. fonso v says:

    Hey! Great video! Is this possible to do with charts? Pull down list in
    order to feed my charts?

  10. Andrei Nemeth says:

    Hi! Can you please help me with something? If i want to add another
    dropdown, and simply add another day to the sum. For example i want to know
    both values from day1 and day2 (summed). How can i do this?

  11. rafi md says:

    Bro….you are doing a great favour to mankind….cheers… :) :) 

  12. david stefaniak says:

    I have payroll on excel and want a drop down menu for the employee, the
    week they were paid and to enter in there deductions, rates, ect. for a
    payroll stub.

  13. Abbas Mundol says:

    Is there a way to convert dates to text. For Eg: 28-5-2014 to Twenty Eight
    May, Two Thousand Fourteen..

  14. Deyn2011 says:

    Hi share, thanks for sharing. I wonder how will it be if I am going to pull
    out multiple cell references like a grading sheet. :) 

  15. Roman Simple says:

    thank u !!

  16. Andrew Hogg says:

    This is great; thanks! Is it possible to add another level of complexity
    to the drop-downs? I want to have two drops downs on an ‘overview sheet’,
    the first would select the specific worksheet (for a particular person) and
    the second would selects the ‘total’ cell in a particular row on that sheet
    (for a performance metric for instance). Then I could display the info from
    any row on any sheet on my overview sheet… I can’t work it out!!

  17. Michelle Gitgano says:

    I have learned a lot just by watching your videos so, just wanted to say
    thanks for all your great work.. thank u so muchhhh!!!! :) )))

  18. Action24 says:

    I have attendant sheet with different section.Everyday I do need to go each
    sheet to print them. So I want to combine them together by drop down in one
    sheet. I mean in Sheet1 I want to make a format where by drop down I will
    just select the section and then on the bottom all the staff on that
    section will come automatically. How can I do that? Please help me

  19. Ulvi Guliyev says:

    Thanks very useful

  20. Jeff Stack says:

    Dude, for real, Excellent work! You cover all the (mystery) topics, explain
    it perfectly, structure it so it flows flawless and before this gets to
    creepy, I’m calling ‘No Homo.” In addition, if you had a ‘donate’ button,
    this would be one of the few that I would donate too! Just sayin’ Oh Yeah,
    Thanks alot too!

  21. djgaryc says:

    This is excellent. It’s going to help me loads! Thanks very much.

    I’m trying to work out how I can use your video trick to pull up a how row
    of data on a different sheet.

    For example, if I make my “Sheet 5″ with a drop down list that has “room
    1″, “room 2″, “room 3″ and “room 4″ in Cell A1, how can I make it pull the
    data from “sheet 1″ Rows B1:J1 down to B15:J15 purely because the fact
    A1:15 says “room 1″

    What I would then do is after printing the data, change sheet 5 Cell A1 to
    say “room 2″ and the sheet pull data B16:J16 down to B20:20 again, because
    Cell A16:A20 says “room 2″… and so on.

    Big respect

  22. Tim Mosby says:

    Cool trick, I have a question…can you do that same reference with Name

  23. Boaz Epstein says:

    Hey Mike.
    Been looking for that solution for some time,
    I’ll admit, I didn’t go straight to excelisfun,
    just googled it, total waste of my time.
    Again, your solution was exactly what I’ve been searching for.
    Cleverly done mr. Girvin.
    One question though:
    I need the cell reference to change as I move the formula around:
    A5 is the reference to sheet name,
    e33 is the cell reference.
    I couldn’t bring excel to accept E33 as reference instead of text.
    (I tried the awesome “&&” trick, but no luck..)
    What do you say.
    Thanks rocking Mike.

  24. Jan Jones says:

    Can you show how to do this same thing but in lieu of sheet names, use
    range names? I have a series of tables that have been named. Each table
    is an option in a drop down list. I need a blank table populated based on
    the drop down selection. Thanks.

  25. Fariz Kamis says:

    I have learned a lot just by watching your videos so, just wanted to say
    thanks for all your great work.. thank u so muchhhh!!!! :) )))

Switch to our mobile site