Smart Dep. Drop-Down Lists in Excel: Expandable & Exclude Blank Cells

Share
Embed
  • Published on Jul 19, 2018
  • Create drop-down lists in Excel that are dynamic - #1 They expand as new data is added to the list, #2, they are dependent on another list and #3, they exclude blank/empty cells on the bottom of each list. Basically they are only restricted to the cells that have values in them.
    In this video you'll learn how to use the OFFSET function to get fully dynamic dependent drop down lists.
    You'll then see another version which uses INDEX & MATCH as well as Excel Tables. We plug in the formula we create inside Name Manager and then use the new name inside our custom data validation formula box.

    🡻 Download the workbook here 🡻
    www.xelplus.com/excel-dynamic-dependent-drop-down-lists-exclude-blanks/
    ★ Links to related videos: ★
    Data Validation drop down Playlist: thexvid.com/p/PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y
    Learn OFFSET: thexvid.com/video/RPTQjbk2qy4/video.html
    Learn INDEX & Match: thexvid.com/video/F264FpBDX28/video.html
    ★ My Online Excel Courses ★
    Courses ► courses.xelplus.com/
    ✉ Subscribe & get my TOP 10 Excel formulas e-book for free
    www.xelplus.com/free-ebook/
    EXCEL RESOURCES I Recommend: www.xelplus.com/resources/
    Get Office 365: microsoft.msafflnk.net/15OEg
    Microsoft Surface: microsoft.msafflnk.net/c/1327040/451518/7593
    GEAR
    Camera: amzn.to/2FLiFho
    Screen recorder: techsmith.pxf.io/c/1252781/347799/5161
    Microphone: amzn.to/2DVKstA
    Lights: amzn.to/2eJKg1U
    Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
    #MsExcel

Comments • 216

  • Feroz Khan
    Feroz Khan 3 days ago +1

    how to summarize the datas of my parent drop down and its dependent drop down?

  • Peter Ng'ang'a
    Peter Ng'ang'a 6 days ago

    Thanks Leila. Thanks.

  • Ren Sycloren
    Ren Sycloren 6 days ago

    Great explanation.....!!!

  • Karthikeyan Subramanyan

    Leila, in your original video “offset for dynamic arrays” you didn’t use a secondary offset within the counta function. Even here I think you can do the same .

  • Mohamed Selim
    Mohamed Selim 12 days ago

    thexvid.com/video/T3kWIw9sjL8/video.html
    But it's not as easy as the data validation - list
    Thanks

  • Ruisandro Tromp
    Ruisandro Tromp 13 days ago

    Thanks, very clear and simple explanation. Is there a way to create a dropdown list from items in another excel file and each you select 1 of these items it substract the quantaty you select in the template?

  • Gera Sanz
    Gera Sanz 15 days ago

    I just love your videos and tips nice work! And greetings from México

    • Leila Gharani
      Leila Gharani  15 days ago

      I'm glad to hear that! Greetings to México.

  • Annie Nielson
    Annie Nielson 25 days ago

    I learned so much, thanks!

  • John Rice
    John Rice Month ago

    It's a kind of magic!

    • John Rice
      John Rice Month ago

      @Leila Gharani Black excel magic is VBA, but formulas is the white magic. 😊
      Thank you!

    • Leila Gharani
      Leila Gharani  Month ago

      Black Excel magic :)

  • tarnaka504
    tarnaka504 Month ago

    excel is a rocket science,
    but you made it like a choclate!!!

    • Alex Granowsky
      Alex Granowsky Day ago

      tarnaka504 on god we gon’ get you some pussy bro

  • 1zemaitukas
    1zemaitukas Month ago

    Hello Leila,
    I have a more difficult task. I need that drop down list result to be a table. For example, if I choose a phone manufacturer from drop down list, the table shows all models of this phone. Is it possible to do this in excel?

  • sirri tanribilir
    sirri tanribilir Month ago

    Very indirect way.. there was a better solution creating these data as tables and using “indirect” formula in data validation..

  • khalid Qweder
    khalid Qweder Month ago

    Great Teacher.

  • Nana Ampem Darko
    Nana Ampem Darko Month ago

    This is beautiful..... Exactly what I needed. Thanks Leila

  • Sashank Salvi
    Sashank Salvi Month ago

    Hi Leila, I have used the "offset" formula in Excel for drop-down and its functions, but it sometimes works, I have data for the brand and product, when I have select product in the drop-down and when i go to brand dropdown and when I select any brand, the product does not automatically change in the drop-down, it has to be changed manually.

  • Tom Chandler
    Tom Chandler 2 months ago

    I am struggling to master this. My formula is as follows; =OFFSET($A$1,1,MATCH($E$9,$A$1:$C$1,0)-1,1,1) and it works this way. However, if I change the 1 to the right of the -1 to a different number like you have, I receive a VALUE! error. Could you help me with what I am doing wrong?

    • Leila Gharani
      Leila Gharani  2 months ago +1

      The value error doesn't necessarily means it's wrong. It just means Excel can't show so many results in one cell (whereas the data validation can). The best test is to click inside the formula bar and press F9. If you see the correct results there, then the data validation will work - make sure you press Escape or Control Z before leaving the formula box.

  • Michael Pole
    Michael Pole 2 months ago

    fantastic insight very useful stuff! you look hot too! (#saucy)

  • Majeed Ahmad Jafri
    Majeed Ahmad Jafri 2 months ago

    Great

  • Glenn Magcaling
    Glenn Magcaling 2 months ago

    Well done, Ms Gharani! Very informative. Excel users will surely love your tutorials! Keep it up.

  • mostafa4321
    mostafa4321 2 months ago

    Hi Laila I have a question to ask. I am trying to create a multiple selection list using data validation but for a whole column. I created a VBA for one cell but for some reason, it does not propagate to the whole column. I want each cell in a column of 30 rows to have the same selection ( multiple) from the same source. Can you help? Thank you :)

  • ripudaman singh narang
    ripudaman singh narang 3 months ago

    It's difficult to complement you, as I was struggling for this for last couple of months, used table but it wouldn't appear in datavalidation, and what a simple trick u just explained in this video.. kuddos to you and your team

    • Leila Gharani
      Leila Gharani  Month ago

      You're very welcome! I'm glad I could help with that.

  • Uma Maheshwar Askula
    Uma Maheshwar Askula 3 months ago

    Thank you! Leila

  • phil knott
    phil knott 3 months ago

    this has been a massive help in my analysis - thanks Leila :)

  • Marks Man
    Marks Man 3 months ago

    Leila, congratulations! I am a financial analyst and like your course as well as excel. Your town Vienna is a beautiful city. I wish you all the best with your new plan!

  • Gennifer Caesar
    Gennifer Caesar 3 months ago

    Great tutorial! Does this work having the table list on one sheet and the drop downs appear in a separate sheet or only all in one worksheet?

  • Boyka
    Boyka 3 months ago

    Quick questions,
    1. How can I create a searchable drop-down list for multiple cells?
    2. If I add more products or games into the list, do I have to recode the whole list again?
    Thank you

  • IOANNIS SIGALAS
    IOANNIS SIGALAS 4 months ago

    hi I tried to make the depented drop down lists by the table method in excel 2019 but the program found the formula wrong. i did exactly what you did. do you have any idea???

  • Gokul Prassadh C M
    Gokul Prassadh C M 4 months ago

    Wow! Your Infographics and dynamic arrays are eye opening! I was looking at TheXvid videos in this channel "Wawamustats". And you are the right person to ask this question.. Can I create such charts using Excel?

  • sukrat kaushik
    sukrat kaushik 4 months ago

    Hi, thanks a lot for amazing videos.

    I used your 1st method. 2 problems I am facing now:-
    1 --> I am getting #VALUE! in cell before copying formula to data validation.
    2 --> Once I put formula in data validation; when I change 1st drop-down values of 2nd drop-down does not change automatic.

    Can you please help on this.

    Best regards, Sukrat

    • Leila Gharani
      Leila Gharani  4 months ago

      Hi - regarding number 1 - that's ok. If you click in the formula bar, press F9 and see the right list, then it is ok.
      Regarding 2 - the answer is in this video thexvid.com/video/wWasYHG1lmM/video.html or this one thexvid.com/video/aSPtWo3IiOM/video.html

  • Steve Tawil
    Steve Tawil 4 months ago

    Leila, great video thanks. I am using activeX combo list boxes with the data validation, but using this index offset formula in the data validation is not allowing the dependent combo box to work - it opens to a blank line. The parent combo works fine. Your help is appreciated.

  • Siang Lim
    Siang Lim 4 months ago

    Thanks Leila! two thumbs up for both methods, I find the table method far more intuitive and easier to recall and reuse.

  • Eva Von Nebenan
    Eva Von Nebenan 4 months ago

    Amazing Video and the solution to my problem! Thanks for sharing! It works quite fine but I noticed that whenever I change the '"height"parameter to something else than 1 I will ge a Value Error. do you know why that is?

    • Eva Von Nebenan
      Eva Von Nebenan 4 months ago

      @Leila Gharani Thanks a lot!

    • Leila Gharani
      Leila Gharani  4 months ago

      Hi Eva - please watch this video thexvid.com/video/wWasYHG1lmM/video.html and this one for an alternate solution thexvid.com/video/aSPtWo3IiOM/video.html

    • Eva Von Nebenan
      Eva Von Nebenan 4 months ago

      I could find the issue. I simply forgot to crtl+shift+enter . But I have another question, I tried this system with 3 dependable condition. Whenever I chose a 2nd option (lets say in your example Games--> Perino) and then go back to change my first option to eg "Utility" the 2nd dropdown stays at "Perino" instead of automatically switching to "commuta". I have added "please select" in the first row of every column, since I like 3rd parties to use my sheet. Do you know a good hack to achieve the selection to jump back to the first row value? Thank a lot in advance!

  • Ben Sharratt
    Ben Sharratt 4 months ago

    you do the best excel tutorials online, fact. Just wish you did a few more about macros though :(

  • Moshe Belfer
    Moshe Belfer 4 months ago

    Leila,
    I tried to use your explanation for my own case and I can't copy the formula into the data validation since the foumula I used is too long, is there anyway I can't extend the capacity of source field in the data validation?

    • Leila Gharani
      Leila Gharani  4 months ago

      You can put your formula in Name Manager - so give the formula a name and use that name in data validation.

  • jariemilov
    jariemilov 4 months ago

    Incredible value! Thank you so much!!

  • yomajo
    yomajo 4 months ago

    Is there an answer why offset can't grab values from another sheet? ;

  • Trinh Vuong
    Trinh Vuong 5 months ago

    damn it had to log in to like and comment for this brilliant video!!

  • Harshita Gupta
    Harshita Gupta 5 months ago

    =OFFSET(Sheet2!A1,1,MATCH(Sheet1!A2How ,Sheet2!A:A,0)-1,COUNTA(OFFSET(Sheet2!A1,1,MATCH(Sheet1!A2,Sheet2!A:A,0)-1,Sheet2!B2:B40,1)),1)
    For the parent list I am getting all values in the dropdown but for the dependent list of value I am getting on one value in the dropdown. Why I am not getting g all my values in the dependent dropdown list and only getting one.
    Please reply

    • Harshita Gupta
      Harshita Gupta 5 months ago

      Thanks for the update

    • Leila Gharani
      Leila Gharani  5 months ago +1

      With this formula it looks like the parent headers your are searching for are in rows and not in columns, but then the second offset is only moving than 1 row. The second part of the formula doesn't seem consistent with the first part.... If your data is organized in rows, check out the end of this video thexvid.com/video/gu4xJWAIal8-/video.html which shows how make a dependent drop down on this setup - if it's in columns, check out the end of this one thexvid.com/video/aSPtWo3IiOM/video.html, which shows how to exclude blank cells.

  • paresh narola
    paresh narola 5 months ago

    Thanks a lot,
    I have one problem, how i open this drop down after type some words with keyboard, i was try to open with alt + down arrow but it doesn't work, please give Suggestion...
    Thanks

  • Jeferson Santos
    Jeferson Santos 6 months ago

    Hi Leila. Your videos are awesome ! Concerning to OFFSET tutorial there is a way to formula COUNTA ignore "fake blank cells" that contains "" ? It's becasuse of my lists is formed by formulas, so I have a lot of cells with "" and the formula COUNTA count as a value....so the blanks cells is not ignored on drop-down list. Thanks in advance !

    • Leila Gharani
      Leila Gharani  5 months ago

      Hi Jeferson. Thank you. You can use the method I show here thexvid.com/video/aSPtWo3IiOM/video.html starting from minute 9:00.

  • SHREYASH SHETTY
    SHREYASH SHETTY 6 months ago

    PUT VIDEO OF TALLY ERP 9

  • Ziaur Rahman
    Ziaur Rahman 6 months ago

    Great video! Very useful and easy to follow. Thanks a ton!

  • Terry Desfiges
    Terry Desfiges 6 months ago +1

    Hi there the Tutorial is very good and when I try and do that here it works, I do have a little problem, the Fonts are too small in drop box, is there a way to make the fonts bigger, thank you

    • Terry Desfiges
      Terry Desfiges 4 months ago

      Any news for the above question please, thank you.

  • Kim Douglas
    Kim Douglas 6 months ago

    This is exactly what I have been struggling with!!!! Now i get it LOL

  • selva kumar
    selva kumar 6 months ago

    Please guide me how to create a word doc from Excel

  • Pune Pet Park
    Pune Pet Park 6 months ago

    Superb! what if I need to select the values of all first and then find out whether they belong to productivity, games or utility

    • Leila Gharani
      Leila Gharani  6 months ago

      I show an example here for reverse lookup: thexvid.com/video/OJLfPc9YlqE/video.html

  • Suleiman Abali
    Suleiman Abali 6 months ago

    This is really a useful function

  • Doorbs
    Doorbs 7 months ago

    OMG your amazing!! This has just solved a problem I’ve been working on for hours!! I was using dynamic validation using tables but needed a shared document!! Obviously you can’t share a document with tables so this has cured my headache, thank you so much 😊

    • Leila Gharani
      Leila Gharani  7 months ago

      I'm glad this helped! Thanks for the feedback.

  • Francis D'Costa
    Francis D'Costa 7 months ago

    Very informative explanation as usual. Thanks

  • Usama Khan
    Usama Khan 7 months ago

    great

  • Alfio
    Alfio 7 months ago

    How can this be applied to a full sheet? I have a sheet with hundreds of entries and the table for reference on a second sheet. I want column one to be the first selection and column two to be the second. I guess the tricky part is having the reference move with each row. Your help would be much appreciated. Thank you.

  • Khaled Enbaya
    Khaled Enbaya 7 months ago

    Thank you so much

  • ISMAEL RETURETA GARCIA
    ISMAEL RETURETA GARCIA 8 months ago

    The quality of your videos is amazing!!

  • Kashif Zia
    Kashif Zia 8 months ago

    Thanks a lot madam.
    I want help regarding two lookup values for single result in excel

  • Syed Rummaan Ahmed
    Syed Rummaan Ahmed 8 months ago

    Great Video. Leila Just a question.
    I have a Main worksheet with names linked with another worksheet with drop down list of same names.
    Can I make this drop down list to read the same name clicked on the Main worksheet.? rather than showing names in order.
    Thanks.

  • Petter Tesla
    Petter Tesla 8 months ago

    Great Job...is very usefull...

  • AceMacoe
    AceMacoe 8 months ago

    Thanks for the great video.... I have an issue where my lists are numbersl. Is it possible to do this?

    • Leila Gharani
      Leila Gharani  8 months ago

      You probably need a very simple approach like this one: thexvid.com/video/8hAT18U8qWs/video.html or this one: thexvid.com/video/CIJbcBBH88M/video.html - I have a whole playlist dedicated to dependent drop-downs.

  • Alper Em
    Alper Em 8 months ago

    Hello thank you for the tutorial. I want to ask you that how can I get the list item from another workbook. But the master workbook which has list items in it should be closed not open. There is a lot of tutorial which explains to do that when the both workbooks are open but i need that master file to be closed. So i would be really appreciated if you can help me with this. Thank you.

    • Leila Gharani
      Leila Gharani  8 months ago

      You can use the Index formula for that. But what you need to do is set up a data preparation table inside your open workbook. So don't directly use the formula to the closed workbook in your data validation or in name manager, but instead write a simple index formula like the one shown in this video: thexvid.com/video/w7cHgsFirLk/video.html (the formula shown in the second half in column I). You can put this data prep. table somewhere in a hidden sheet in your open workbook. These lists will pull values from the closed workbook. Then you reference the data validation to this list. Hope this helps.

  • Peter Lelkes
    Peter Lelkes 8 months ago

    Thanks Leila for a great tutorial :-) One question: how can I make DD searchable?
    Thanks in advance
    Regards
    Peter

    • Leila Gharani
      Leila Gharani  8 months ago

      I have this video on my to-do list. It takes a few steps to set it up with formulas. An alternative is to use VBA...

  • Romin Singh
    Romin Singh 8 months ago

    Hi Leila...I have a problem, I want the selection of my dependent list automatically refresh based on selection of original, and should not hold the old selection

    • Romin Singh
      Romin Singh 8 months ago

      @Leila Gharani thank you!

    • Leila Gharani
      Leila Gharani  8 months ago

      You can do that with a very simple VBA code. I'll add that video in the future...

  • Rahul Jauhari
    Rahul Jauhari 8 months ago

    Great

  • Kethan Chordia
    Kethan Chordia 9 months ago +2

    Amazing tip..!!! People don't care to like your video even though they use it.. 600 is quite low for this video :-) I love all your tips..

  • TurningFinal
    TurningFinal 9 months ago

    I use table names and =indirect to achieve the same thing. So many ways to skin a cat with excel.
    Well presented video, thanks

  • Brigh N Quisitive
    Brigh N Quisitive 9 months ago

    fantastic! I'll be using this indispensable information immediately. ..you're the best Leila

  • Muhammad Imran Bukhari
    Muhammad Imran Bukhari 9 months ago

    I have few columns; (dates, sticker Number, full name, birth date, address, customer type and many more). I have to count numbers of customer in a specific date or month or year. Please guide

  • Rahul Bakshi
    Rahul Bakshi 9 months ago

    Hello Leila, very nice explanation. First of all wish you Happy Teacher's Day. Your teaching helped everyone a lot. I need help on VBA code for drop down. I need a VBA code which when run will select values from drop down list one by one. Is it possible. If yes, pls help...

    • Rahul Bakshi
      Rahul Bakshi 9 months ago

      @Leila Gharani Thanks Leila for the reply. I will check your videos and get back if I have some doubts...

    • Leila Gharani
      Leila Gharani  9 months ago

      Thank you Rahul. Usually the drop-down values are inside cells instead of directly typed in the data validation box. You can then easily loop through the cells. Check out my VBA playlist. I have a few videos where I show looping in cells.

  • Grant W
    Grant W 9 months ago

    Leila, I've completed most of your Udemy courses and after these Drop Down tutorials must admit that you simply ROCK! Having you in my back pocket makes me feel confident that I can tackle any problem while knowing that you have my back, so to speak! Quite succinct and eye-opening is how I interpret your lecturing capabilities... In closing, don't, please don't... STOP!!!!!

    • Leila Gharani
      Leila Gharani  9 months ago

      Thank you Grant - that's really kind! I'm really glad you find the tutorials helpful for your work.

  • dennis mwaba
    dennis mwaba 9 months ago

    Great Video indeed. Now I have a challenge and I am asking for help. I want a name list dropdown in a cell, But after I select a name in that cell, it should display a birthday in that same cell. is that Possible? Please help?

    • dennis mwaba
      dennis mwaba 9 months ago

      @Leila Gharani true, I think there is no way as at now except using the macros vba. Anyway thanks again. You are good

    • Leila Gharani
      Leila Gharani  9 months ago

      Thanks. In the same cell - no - The cell can either be for selection or for input. You'd have to write it in a separate cell...

  • Ronald Agee
    Ronald Agee 9 months ago

    Thank you.

  • Omer Sirkeci
    Omer Sirkeci 9 months ago

    super...

  • Ajay Shirwadkar
    Ajay Shirwadkar 9 months ago

    super excel.....

  • KONSTANTINOS FOTOS
    KONSTANTINOS FOTOS 10 months ago

    Very interesting and useful videos

  • Jaipal Rana
    Jaipal Rana 10 months ago

    Superb!!!!! very very useful. good explanation in cool way!

  • Admiral Thrawn
    Admiral Thrawn 10 months ago

    While you were explaining the offset function, I kept asking myself why weren't you just using tables and names for ranges in Name manager, and then you did. This is a quite informative and well-structured tutorial. *Thumbs up*

  • Sayed Awesh Rahman
    Sayed Awesh Rahman 10 months ago

    THANK GOD YOU FORGOT THE NAME IN-BETWEEN I CAME TO KNOW ABOUT ANOTHER SHORTCUT AND TRICK :D THANKS A LOT FOR YOUR EFFORT

  • neeraj beeknoo
    neeraj beeknoo 10 months ago

    good one

  • Uttam Jain
    Uttam Jain 10 months ago

    Thanking you Leila.

  • Anil L
    Anil L 10 months ago

    Excellent and elaborated explanation. Thank you Leila

  • Auris Z
    Auris Z 10 months ago

    Hi Leila, I've scenario instead of header in column can we use rows?
    Rows:
    2 A ABC1
    3 A ABC2
    4 A DEF
    5 B Test
    6 B Test2

    • Auris Z
      Auris Z 10 months ago

      Hope my question is clear...

    • Auris Z
      Auris Z 10 months ago

      If cells value is A then I should 3 values in drop-down

  • Rafael Hernández
    Rafael Hernández 10 months ago

    Awesome, the second option I believe is the right one when more than one people is using the file. Really cool!

    • Leila Gharani
      Leila Gharani  10 months ago

      Agree - It's easier to understand and update :)

  • ckokse
    ckokse 10 months ago +1

    Very useful tip, great explanation!

  • Vijay Kumar
    Vijay Kumar 11 months ago

    Dear mam please do this same working in active x control drop down 😁

    • Vijay Kumar
      Vijay Kumar 9 months ago

      Hello mam, please make the video of same working in active x control.

    • Vijay Kumar
      Vijay Kumar 11 months ago

      Leila Gharani thanks a dear, kindly make this video asap.i need a lot of it.

    • Leila Gharani
      Leila Gharani  11 months ago

      Hi Vijay. Ok - I'm added it to my list. Thanks for your suggestion.

  • mowburnt
    mowburnt 11 months ago

    I have to write visit reports each week. Each week is a new sheet. In each sheet I add company name, person seen, job title, zip code. Is there a way to have a master sheet which stores all the customers and people so that I simply pick or start typing a company and then the rest of the fields have drop downs pre populated with the possible people or zip codes? And how can that be added to with new customers or companies so when I visit again in several weeks I can re copy. Or is this something best done in access?

    • Leila Gharani
      Leila Gharani  11 months ago

      Hi mowburnt - maybe this video helps? thexvid.com/video/7uIZg9p-IZQ/video.html

  • Hamza Ijaz
    Hamza Ijaz 11 months ago

    Another great tutorial. Can you please make a duty roster shift vise?

    • Leila Gharani
      Leila Gharani  11 months ago

      Thank you for your suggestion. I added it to my list.

  • Mark Miller
    Mark Miller 11 months ago

    Is there a way to limit the choices in a drop down list? I want to hide or remove items that have been previously selected. For example, I want to assign staff to a particular work shift/timeslot, or a task/project and don't want to assign the same employee twice. Is that possible?

    • Leila Gharani
      Leila Gharani  11 months ago

      Oh that's a good one. That makes a good video. I've added it to my list. I'd probably use a data preparation table to get the list that excludes the selected one and based on that make a data validation. Thank you for your question.

  • مقداد حديد
    مقداد حديد 11 months ago

    thanx queen you always creatior

  • Chaminda Basnayake
    Chaminda Basnayake 11 months ago

    This is GREAT !!!, Thank you.

  • erin davitt
    erin davitt 11 months ago

    I'm in a hurry so I can't watch the whole thing but wouldn't just filtering do the same thing? Forgive me but I'm not smart with Excel. Thanks for your time.

    • Leila Gharani
      Leila Gharani  11 months ago +1

      Hi Erin - this for cases where you have a list of master data and you have an input sheet somewhere else and you want the user to input but you don't want them to make spelling mistakes, so you provide them a drop-down to select from....

  • SU
    SU 11 months ago

    How to make the same thing but with Combo Box (form control)?

    • Leila Gharani
      Leila Gharani  11 months ago

      That will make a good video - added to my list. Thanks for the suggestion.

  • krn14242
    krn14242 11 months ago

    Very cool. Have not seen the area method before. Great solution.

    • Leila Gharani
      Leila Gharani  11 months ago

      Thank you. Hadn't used this in a while so I thought it was a good opportunity to put it to use.

  • TrendingKe hub
    TrendingKe hub 11 months ago

    Hello Leila, I'm practicing using excel and I'm designing excel sheet for beam analysis.... Could you please give procedure how to code VBA to pick integers alone in concrete grades M10, M20, M25..., I want to type say, M20 but use integer 20 alone in the formula( = k/fcu d²b) where fcu is concrete grade... Thanks in advance

    • Leila Gharani
      Leila Gharani  11 months ago

      Hi - in VBA you can extract the right hand side with the Right function. For example Right(Range("F12").Value, 2)

  • TrendingKe hub
    TrendingKe hub 11 months ago

    Hello Leila, I'm practicing using excel and I'm designing excel sheet for beam analysis.... Could you please give procedure how to code VBA to pick integers alone in concrete grades M10, M20, M25..., I want to type say, M20 but use integer 20 alone in the formula( = k/fcu d²b) where fcu is concrete grade... Thanks in advance

  • Juanma Picossi
    Juanma Picossi 11 months ago +1

    Leila, thanks a lot for this amazing video! Let me ask you if it could be possible to expand the table's technique to a third level depending on the value in the second level? For example, country, state and city. Thank you very much!

  • Mohammad Aijaz
    Mohammad Aijaz 11 months ago

    Waaaooo! Amazing, you are a Genius mam

  • DANNY ZHU
    DANNY ZHU 11 months ago

    I didn't know that table can be created next to each other until i watched this. How about this formula in the Data Validation. : =INDIRECT(F4) after tables are created. It is simple and the only thing is that the indirect is a versatile function.

    • Leila Gharani
      Leila Gharani  11 months ago

      Hi Danny - Indirect is a great solution. Much faster to write. I actually show that in the data validation video thexvid.com/video/FRiFfKb_B_A/video.html - I guess one downside to it is that the table name has to be identical to the data validation - and if we have spaces in there, we need to work with substitute function. Just to keep in mind depending on the data set. Many thanks for your feedback.

  • Malina C.
    Malina C. 11 months ago

    Absolutely brilliant! I like the second method very much! Thank you for sharing, Leila :)

    • Leila Gharani
      Leila Gharani  11 months ago +1

      You're very welcome Malina. Thank you for dropping by :)

  • John Borg
    John Borg 11 months ago +1

    Thanks Leila. Very interesting and helpful. :)

  • Ismail  Ismaili
    Ismail Ismaili 11 months ago

    Wow absolutely a great video thank u so much Ms. Liela

  • Luciano
    Luciano 11 months ago

    Grandes métodos Leila, sensacional, muito obrigado.

    • Luciano
      Luciano 9 months ago

      Sim!...Aprendo em Inglês, e aplico em português. Por que?

    • GJLNZ1
      GJLNZ1 9 months ago

      Por a caso você achou algo similar em Portugues ?

    • Leila Gharani
      Leila Gharani  11 months ago

      You're very welcome Luciano.

  • Tim Sheets
    Tim Sheets 11 months ago

    The named range trick to get around the data validation limitation is awesome.

    • Leila Gharani
      Leila Gharani  11 months ago

      Thanks. I hope Microsoft will improve on the data validation box.