I have been trying to work this out for a couple of weeks. I feel quite embarrassed now that, I just didn’t initially see how this would work out and how Leila subsequently worked it out. But, I’m now quite edified and thankful for her lecture, thank you! Also the F2 to edit mode and the Ctrl + Shift was a great shortcut. I’m now able to help out a colleague in the office by adding the different list to generate different charts. I’ve learned a lot with this short lecture.
Thank you! I lost 5 hours on this. Writing this in case someone else has this issue. I first used OFFSET in my dynamic dropdown (so I use dynamic offset based on one value in the same row and it should give me different dropdown for each row). The problem with OFFSET is that it's too complicated (I had to combine it with INDIRECT and ADDRESS in my case) and a bigger issue is that whenever I closed the file, it would "forget" the list the next time I opened it. Basically, upon opening the file, the OFFSET would probably give a wrong value so my list would be non-existent. The hack to this is to open your Data Validation (Alt + D + L) and it would probably refresh the OFFSET formula so it would work, but it's a hack. Then I tried with INDEX:INDEX instead of OFFSET (very very useful thing I learned, Google it), but I would receive the error Leila got. I didn't know the solution to this was as simple as Name Manager. Thanks again, Leila!
Very useful and informative. Please upload at least one videos daily for all the Excel lovers/addicts/users. I am one of your fan. You are experimenting with Excel formulas and explaining beautifully, in all the ways. Hats off!!!!!!!! . Please help to the Excel users by starting the series VBA Basics.
Hi Gopala. Thank you! Uploading videos daily will be a major challenge. It takes a lot of time of put together the content, shoot, edit etc. I'll do my best to put up as many as possible within a week (I think for now it will be one a week, but will aim to increase that soon). I'm aiming to put out some VBA basics during the second half of the year, when I start to work on my VBA online course.
Hello Leila, it's simply awesome. I had no clue that Index formula can be used like this. Your lacture is also very clear and lucid. May god bless you. Very best regards.
Yes - I understand. These are a bit on the advanced side. I'd suggest to start with the Excel basics playlist. Mike from ExcelIsFun has a comprehensive playlist for those getting started.
I have a dataset that is 4x4. I want to see what formula will bring out the desired output. I have tried index& match, data validation with offsets. I still think it can get better please let me know where I can share my table with you. I will really appreciate your response on this.
Glad to hear that. Not much difference except indirect is a volatile formula and constantly calculates. Not a problem with small data sets. I personally prefer to use alternatives when I can.
How do I create a dynamic drop down based on a numeric input Example If input value less than 10 then Drop Down Option must be A,B and C If input value greater than 10 then Drop Down Option must be A
Thank you very much for your fabulous videos! I have applied your Index (reference) formula in exactly the way you demostrated for a drop down list. Everything works perfectly up until the point where I select an element from the list and then swap out the locked area number but the previously selected item from the drop down list doesn't disappear. So end up with an element selected that is actually not possible to select. Do you have any suggestions about how to solve this please?
I was looking for something like this for days and It's almost exactly what I needed, but I want to put text (names) in the drop down list instead of 1,2,3 etc. How can I do this?
Hi Leila, I just started with some of your videos and I really like them because are very well explained. I have a question though, what if in my select list cell I have a word and not a number? I get the value error and I realize that it happens because I am using a word and not a number. What could I do to make it work with a word? Thank you very much!!
Hi Hildemar - If the word is identical to one of your table headers you can use the match function to get the number reference. So in this example, instead of directly referencing G41, you would write: match(G41,A41:C41,0) - that would return the number to the index function. I describe in more detail what the match function does in this video: th-cam.com/video/F264FpBDX28/w-d-xo.html. Hope that helps...
Very nicely explained. I find your videos quite useful. I use drop down list, my dependent list contains code nos ie nos only, when I try to make drop down it shows error. I know, you have video on this, can you pl send me video link. Thanx.
im assuming its possible to do something as simple as checking the dropdown to see if its value = x and then adding something inside the field if correct or false.
Great Video Leila. Can you use your index formula instead the indirect function to make the data validation list dynamic? The index match combination is not as volatile as the indirect function, correct?
Thank you Michael. Yes, the Index with references can be used for dynamic drop-down lists. Indirect is a good formula but like you say, it can slow things down. Another option would be the choose function in this case. Will make a video on that too.....
How would you create a Index from a Data Base to then be able to work with. For example in Column A, I have a list of Sales people (Say 20 people max but not all people sell all products and some people sell multiple products eg. Anne Smith appears in the Column A 36 times Joe Sales appears 10 times and Margaret Nil doesn't appear at all this month. On worksheet #2, I want to create an Index of ONLY the people who appear in Column A from this particular Database and to be Indexed only the once in worksheet #2, so it will only generate an Index with each person name just once regardless of how many times they appear. Thanks Leila, it's just every version I've seen of Index appears far more complex than I need.
Hi Leila, I have been following your tutorials for some time now. I am not an advanced user of Excel and your stuff is just brilliant to start with. With regards to this particular video I have a question which may be silly:), however, because the lists are of different lengths whenever I choose NOT the longest one, the bottom cells are filled with 0s since they are empty in the table from which we generate lists. Is this stgh to do with cell formating, some Excel settings? Thanks.
Yes - you can hide that with formatting - if you right-mouse click - go to cell formatting, custom number formatting and put in #;-#; this will hide the zeros. You can also restrict each list to the number of items on the list. Which reminds me I still need to make a video on this one :)
Hi Leila, I love your videos and am so glad you make them. I'm interested in a solution here that's very similar to what you're doing. I have a table of three lists as you show and what I'm trying to do is make the horizontal sequence produce a word. So in your case WenCal, Fighterr, Commuta, would output "Business Apps" into a cell. I thought maybe index and match could do this but in looking at this and your other videos it feels like I'm missing something. The exact use case is - I would put those words into a list, left to right and then once that sequence is in, it will match a word in another list and put that word into the final cell. Seems logical, but I don't know what I'm missing. Can you point me to the function that would do that?
Hi, i'm really having problems to replicate your formula without returning the error #VALUE!. I have gone so far as to duplicate your spreadsheet and copy the formula exactly. Any ideas.....
I have 2 lists of numbers 1-40. The first list I will make as a drop down in my worksheet. The second list is a direct correspondent to the first list of numbers. For instance number 1 (1st list) = 34 (2nd list). So if I select 1 from drop down list in worksheet I want to have 34 populate in the next column over. Would like help on how to do this with 40 numbers.
Leila Gharani Thanks! I really needed to create drop down lists based on different columns and indexes, but I can't figure this out in Google Sheets (I love Excel, but I have to use Sheets at work, so I'm trying to figure this out)
The first version (column G) works on Google sheets but the other two versions seem not to work - I get formula parse error on there..... unfortunately....
Hi Leila, Thanks for your video regarding Index & Match functions. I have now started getting a knack of these two and it's working fine. Thanks very much indeed for your amazing teaching. The index formula for the list does not work for me "=INDEX(A20:C20,$H$19)". It gives an error #VALUE! Can you please help me on this. Bahir
@@LeilaGharani Does it always have to be a value 1 to 3(in this case) Why can't it be "List 1" or "List 2" My mind tells me I should maybe use indirect for this.... Not sure...
hi Leila, love your videos thanks for posting them! I have a quick question. Is there a way to modify this formula to show results from 2 columns? For example, say I want to show column 1 and 2 or column 2 and 3. Thanks!
I think I figured it out, Leila! I just made the header for the additional columns dependent on the 1st column. Then I set up the formula in each column, with each formula being dependent on the column header for that particular column. I hope I'm explaining it well, and that this information is useful. Thanks for the inspiration and knowledge, your TH-cam channel is really valuable for my job.
Hi Leila, Great video but I noticed something that is disturbing me. When you change the list number, the value in the cell of the second dropdown violates the validation rule. This is because it belongs to the previously selected list. Is there a way to solve that without vba? In vba I would write a ValueChange on the first dropdown to empty the second one but ... Thank you for your work anyway!
And this would have helped me a lot, rather than woking with 50 columns X 30000 raw Vlookup functions this would have made everything faster... anyway (dummy question of course) is there nothing in excell that can be used to update the reference dynamically so that rather than calling for the index function and specify the index, can directly point to the cell? hard to expain but rather than =index(B1:B10,A1) where A1 is the raw number, something like =B:B(+A1)?
Is there a way to do this if the information is in the same table? Example: NAME DEPT. john. Sales Jamie. Warehouse Shawn. Sales Chris. Warehouse How do i make it to where it will only show the workers when i select “sales” I thought you made a video doing it that way but cant find it.
Hello Leila , Highly impressed! I am redoing some of your work in Urdu Language for my crowd to understand. Outstanding work. One quick question: When I am using the F2 CTRL+Enter and if the list does not carry any value then it is returning 0. Whereas in your video it was blank. How did u do it ? Thanks again
Hi - Glad you like them! - to have the zero's invisible, you can mark the results area (so where you might have zeros) - go to format cells and for custom number formatting, type this format in: #;-#;; - this will hide the zeros from view....
You can also surround the formula with "IFERROR". Example: IFERROR (INDEX (A1:A5,MATCH (B1,C1:C5,O)),"") If the index/match returns an error, then leave cell blank. or IF (A1="","",INDEX (A1:A5,MATCH(B1,C1:C5,0))) Enjoy, DonW drwilcox.57@gmail.com
Hi, could you tell me how you produced the drop-down in G41, please. I know how to produce drop-downs, but how does =index(A42:C42,,$G$41) know to choose list items from the 3 lists? The drop-down options in G41 are not the headings of the 3 lists. Have you posted a video that shows your actions prior to those on this post? Thank you.
Hi Barry - it's G41 that tells it. I have numbers in that cell and since the second argument for index is how many columns to move over, G41 gives it that info - if I have List1, List2, List3 written in G41 instead, I would have to use match to convert that to numbers. I have a video on index and match basics here in case you'd like to look into it: th-cam.com/video/F264FpBDX28/w-d-xo.html
thanks Leila. I wish there was a way of adding multiple data validation in one cell. For example, I select deposit in cell a2 and in cell b2 (for amount deposited) I set data validation to restrict amount of the deposit. Again I select loan in cell a2, and in cell b2 I make it impossible for a loan figure to be entered if the subject has an outstanding loan. just wondering aloud
You can do different data validations in one cell - like the one you mention in the first paragraph. For the example you have in the 2nd paragraph VBA could be an option, although if you can determine the logic with a formula we could incorporate it in the data validation, either directly or through name manager.
Shau 78, select the cell you wish to limit the dollar value, go to data validation and change the "allow" field to decimal. Then, the "data" field to 'less than or equal to'. Leave "minimum" field blank and then finally, in the "maximum" field, enter the max dollar value allowed, such as, 10.99. If you don't want the cryptic error message to be displayed if the user tries to enter a value greater than your max, in the data validation "Error Alert" tab, style (stop, warning or info), give it a title (like, "Dollar Value Exceeded"), then a short message (like, "You cannot enter a dollar amount greater than 10.99"). Give it a try. DonW drwilcox.57@gmail.com
Not yet - I'm currently working on putting together a VBA course and soon I'll get around to posting some videos on this channel too. Anything special you're looking for?
Leila Gharani I only want to learn Basic VBA coding, after that I might be able to develop expertise in VBA coding myself through practice. Ma'am if you don't mind, can you spare sometime? I wanted to obtain some career related advice from you. I am already connected with you on linkedin
Hi Mustafa - ok great. VBA is actually quite easy to learn - once the basics make sense, you can build anything on top of that. Regarding career advice, sure - send me a message on LinkedIn.
Do you mean if you have many dependent lists after one another and if the user selects an app from the first list, then they disappear from the list below it?
Exactly. I did this myself but thought it would be pretty neat if you taught it. I have a dynamic list, displayed in DVdropdowns across several columns. As I choose from the list and move to the next column ( DVDropdown), that particular choice disappears from the list.
The last procedure "Data validation " I could not do it .I followed all your steps however only the 1st column first 3 details are reflection .When I am choosing 1,2,3 the details are reflecting for the first column .The App list is changing into the row number for column 1. Another point when I am using ,,, ( coma ) in order to keep blank for Row & Column argument -there is an error message .What should I use.
Try downloading the workbook using the link in the description. It takes you to the blog post. Scroll down and download. Your separator might be ; instead of , - it depends on the regional settings. You'll see which one, once you download the workbook. It will automatically adjust to your version.
@@LeilaGharani THANK YOU ....soo much. I have been able to do the scenario successfully and had downloaded the work book and the issue has indeed got resolved.
Can you use this formatting for larger table numbers? For example, I'm trying to set up the data validation lists but there are 21 lists. Evidentally I can only have 3 arguments?
Hi Preston, You can have as many lists - as long as you are only returning one of them. If your lists have different headers other than 1,2,3 etc... you can use the match function inside the index to return the correct list, based on the drop-down selection - to see how to use match, inside index, check out this video: th-cam.com/video/F264FpBDX28/w-d-xo.html
Hi, I have a question relating to the "Array". Its the area where my answer is .So here when we key in the App Column number then the answer against it is present in the whole spread sheet .So why did we not select the whole MAP nstead we selected only the first row.
Maybe a combination of the techniques used in these two videos is what you need: Index match and different sheets: th-cam.com/video/2erErC7LvPY/w-d-xo.html and dependent drop-down with multiple words: th-cam.com/video/w7cHgsFirLk/w-d-xo.html
When I do the same and choose which list I want, the return pads the last records with zeros. How do I make it like yours so that it shows blanks when the return list is shorter than the return area?
Hey Leila can you help me for creating a Sheet where there is a 100 of Items in the Drop Down List and the other column is link with it showing the Suppliers as per the Items selected which then goes on with Sizes, Specification, Rate Per Sq.Ft, Cost Per Piece, Guarantee/Warranty Period in the different different column with the drop down list. especially like once I select the Items & Vendor then the other data should appears automatically column wise.
how to make this working if cell G41 is not used as a fix cell. i mean what if there is a table and index function needs to work one row down with each no entry.
Hi, I wanted to show a drop down arrow with this so I used a combo box instead of data validation, but it didn't work. Could you please explain how to do this with a combo box?
Thanks for that. Yes, I shifted things around on the site - forgot to update this. It's corrected now. It'll link you to the blog post. Scroll all the way down and you can download the complete workbook.
Hello! I am struggling with something in excel. I am trying to create a dropdown list that can show a different information than the one finally displayed. Is there a way to do that? I've been trying with data validation, but i havent been able to find a way
We'll need the selection in a separate cell and then can use VLOOKUP or index match to get the other value in another cell. We can't really replace a cell value with another value (a cell can either be input or have formulas behind), so you'll have to do that in a separate column - unless you use some type of VBA code.
BUT SO MANY SHORTCUT KEYS , WHICH I DON'T KNOW, WANNA LEARN MORE ABOUT THEM, WITH THIS I WANT ANY KIND OF LARGE DATA IN WHICH WE CAN USE VLOOKUP, INDEX, PIVOT AND OTHER FOMULAS IN ONE SHEET, AND HOW WE CAN RECOGNIZE THAT WHICH ONE IS GOING TO APPLY ON THAT DATA..
In a lot of cases you can use different formulas and features to get the same result. Some are faster, some slower. The more techniques we learn, the more options we have to tackle any problem at hand. That's what keeps working with Excel interesting :)
How would you eliminate duplicates to come up with a unique drop-down list? I tried =IFERROR(INDEX(data!$A$2:$A$1315,MATCH(0,COUNTIF($D$4:D4,A2:$A$1315),0)),"") + CSE but Name Manager will not accept an array.
Hi Kanoock. My approach is to create a unique list in Excel cells first - using Index & Aggregate and then use OFFSET or INDEX to get the dynamic list depending on length in the drop-down...
Please ,, I need to create a drop list with (a default chosen item) For example : If you working in a company and about 90% of the individuals are working as a (worker) To save the time I shouldn't select the worker every time I enter the date ..... Regards
Click in the cell that is going to have "1,2,3" drop-down Select data validation, allow = list In the source field simply type "1,2,3" (without double quotes) Done!
Leila, i have one challenge if i add new app's name in any list no, didn't show, basically i m taking about data validation drop down, in this formula we have fixed area/ reference cells, however, i want as if i add new app in any list , automatically show in drop down list, could you assist me? Thx
Good question Sanjay. You can make it add apps automatically to the drop down if you turn it into an Excel table. Click on any cell inside the table and press "Control T" - then go to the bottom any type a name. The drop-down list will automatically update...
Mam, pl told me, whether this formula is used in next page i.e. in 1st page the statement and 2nd page this formula ! I've try it but it cannot be used !
Not sure about your request Shirish. you can download the workbook at the bottom of the blog post and check. Alternatively you might need this version for your dropdown: th-cam.com/video/w7cHgsFirLk/w-d-xo.html
Hi Leila. I am beginning to think your videos do not in fact work with MS Excel for Mac at all. Every example I try even recreating your data and following along does not work. And Microsoft is apparently unable to determine if its software is capable. Do you have any suggestions?
As far as I'm aware the drop-down functionalities and form controls are all compatible with Mac. They might just be called something else or be under a different menu. Have you tried downloading the workbook (link is in the descriptions) to see if it works on the mac?
Hi Leila, I have another question. For starters, your column headers (List 1, List 2, List 3) do not match the drop down options in cell G41 (1, 2, 3). So how does it index anything at all? Also, it's unclear why you chose the first cell/row from each list as in every other example, you have used the full range. I wonder if there is rationale that is not explained. I copied your formula exactly using my correct cell values. It does not work for Excel for Mac. #REF for the entire data set. Also, just curious - as the methods you teach are far more advanced than any of the examples provided in Excel, how did you learn all of these advanced techniques and tricks to manipulating data in Excel? I'd love to learn, although it appears I must abandon using on Mac to do so, or spend the additional time to learn and document all the ad-hoc methods to make formulas work in Excel for Mac. Thanks.
Hi Stacey, I explain the Index formula basics in this video: th-cam.com/video/F264FpBDX28/w-d-xo.html - Here I explain in detail how Index works. In this example though, I don't need to match for the List name because I am using the list address based on the number - for example the 3 tells the index function to move three columns and that's list 3. The reason I only index the first row, is because you only need to index the area where your answer is - as I drag down the formula, my index area moves down as well. Yes - Mac is not optimal if you use Excel a lot. At least not until now....
Hi Leila. I have a question. Can i use DV + INDEX in the following situation...... La Liga Name Goals Ronaldo 22 Messi 21 Villa 14 Rossi 12 Llorente 12 That's one team. I could have 4 or 6 teams. I tried, but only get the results for the first team. For the other teams i get #REF! error. Many thanks.
How is the data for the other teams organized? Is it right below or on the next column. If you have them right beside each other with the team name as header, you can use a match for the columns - You can send me a sample data set if you'd like - my contact info is on my site, but I'd probably only be able to look at these in June....a lot of things to get done in the next two weeks....
Hi Leila and thank u for ur reply. Each team has two columns of data + 6 or 5 or 9 rows/lines of active data. But thank u very much for introducing us to INDEX(Reference.....) that solved my problem totally. There's only one minor thing..... In my example stated earlier u can see 5 lines or rows of active data. But the next team has only 4 lines or rows of active data. So instead of text i get zeroes in the blank cells. How can this be resolved. Million thanks for ur valuable time.
Hi James - maybe custom formatting would work in this case. Highlight your cells, right mouse click - go to custom format - for custom number format, type something like this: #;-#;;@ - this should hide the zeros.
Yes - if you use text instead of numbers, you need to include the match function inside of the index - see th-cam.com/video/F264FpBDX28/w-d-xo.html for more details.
Hi Ashish - I'm currently busy with a few projects - best is to post your specific question either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
Grab the file I used in the video from here 👉 pages.xelplus.com/index-match-advanced-file
I watched the first 2mins 30secs and the way u use the INDEX + DV function is so magical. I also like the Highlight + F2 + Ctrl + Enter. Thanks Leila.
:) thanks and you're welcome.
I have been trying to work this out for a couple of weeks. I feel quite embarrassed now that, I just didn’t initially see how this would work out and how Leila subsequently worked it out. But, I’m now quite edified and thankful for her lecture, thank you! Also the F2 to edit mode and the Ctrl + Shift was a great shortcut. I’m now able to help out a colleague in the office by adding the different list to generate different charts. I’ve learned a lot with this short lecture.
Thanks for the kind feedback David. Glad you can apply it at work too.
Thank you! I lost 5 hours on this. Writing this in case someone else has this issue. I first used OFFSET in my dynamic dropdown (so I use dynamic offset based on one value in the same row and it should give me different dropdown for each row). The problem with OFFSET is that it's too complicated (I had to combine it with INDIRECT and ADDRESS in my case) and a bigger issue is that whenever I closed the file, it would "forget" the list the next time I opened it. Basically, upon opening the file, the OFFSET would probably give a wrong value so my list would be non-existent. The hack to this is to open your Data Validation (Alt + D + L) and it would probably refresh the OFFSET formula so it would work, but it's a hack. Then I tried with INDEX:INDEX instead of OFFSET (very very useful thing I learned, Google it), but I would receive the error Leila got. I didn't know the solution to this was as simple as Name Manager. Thanks again, Leila!
Very useful and informative. Please upload at least one videos daily for all the Excel lovers/addicts/users. I am one of your fan. You are experimenting with Excel formulas and explaining beautifully, in all the ways. Hats off!!!!!!!! . Please help to the Excel users by starting the series VBA Basics.
Hi Gopala. Thank you! Uploading videos daily will be a major challenge. It takes a lot of time of put together the content, shoot, edit etc. I'll do my best to put up as many as possible within a week (I think for now it will be one a week, but will aim to increase that soon). I'm aiming to put out some VBA basics during the second half of the year, when I start to work on my VBA online course.
U R SUCH AN AWSOME.....GOT SO MUCH KNOWLEDGE AND EASY TO LEARN.KEEP IT UP
Very happy to hear that Rekha. Glad you like the tutorials...
7:15 is exactly what I needed! You are the best!
Sweet and great words will never ends with your vedios
Thank you very much
So nice of you
Hello Leila, it's simply awesome. I had no clue that Index formula can be used like this. Your lacture is also very clear and lucid. May god bless you. Very best regards.
You're very welcome Akm.
Excellent examples of Index formula. Many-2 thanks Leila. I want to at least one example of combination of index & offset function.
You're welcome Sanjay. Yes - actually using them in own files and own data is the tricky part but it's the best way to learn....
Thank you so much , yes i did.(dynamically).
Thank you Leila. I did not know INDEX((A43,B43,C43).... Thanks a lot
You're welcome Shau
Leila You are the great again you are the queen of Excel, Thanks for this video.
You're very welcome! I'm glad you find it useful :)
Hi Leila I want to became king of Excel same like you with your help, please suggest me what can I do for this goal.
that's sooo great and useful!!! i never give feedback or comments in youtube, but i gotta give you a thumbs up on this!!!!
U provide grt info...
How do you get 1,2,3 drop-down selection ?
As a total newbie I was immediately lost with this as you were using terminology that I’m not yet familiar with. So I think I need to start elsewhere.
Yes - I understand. These are a bit on the advanced side. I'd suggest to start with the Excel basics playlist. Mike from ExcelIsFun has a comprehensive playlist for those getting started.
Leila Gharani Thanks Leila. I appreciate that!
I have a dataset that is 4x4. I want to see what formula will bring out the desired output. I have tried index& match, data validation with offsets. I still think it can get better please let me know where I can share my table with you. I will really appreciate your response on this.
I love your video, what's the difference between the indirect formula and the index formula for dynamic dropdown list?
Glad to hear that. Not much difference except indirect is a volatile formula and constantly calculates. Not a problem with small data sets. I personally prefer to use alternatives when I can.
How do I create a dynamic drop down based on a numeric input
Example
If input value less than 10 then Drop Down Option must be A,B and C
If input value greater than 10 then Drop Down Option must be A
Thank you very much for your fabulous videos! I have applied your Index (reference) formula in exactly the way you demostrated for a drop down list. Everything works perfectly up until the point where I select an element from the list and then swap out the locked area number but the previously selected item from the drop down list doesn't disappear. So end up with an element selected that is actually not possible to select. Do you have any suggestions about how to solve this please?
I was looking for something like this for days and It's almost exactly what I needed, but I want to put text (names) in the drop down list instead of 1,2,3 etc. How can I do this?
Thank you very much, I love your solution. The content in your channel it´s fantastic. Thank´s.
Hi Leila,
I just started with some of your videos and I really like them because are very well explained. I have a question though, what if in my select list cell I have a word and not a number? I get the value error and I realize that it happens because I am using a word and not a number. What could I do to make it work with a word?
Thank you very much!!
Hi Hildemar - If the word is identical to one of your table headers you can use the match function to get the number reference. So in this example, instead of directly referencing G41, you would write: match(G41,A41:C41,0) - that would return the number to the index function. I describe in more detail what the match function does in this video: th-cam.com/video/F264FpBDX28/w-d-xo.html. Hope that helps...
Hi Leila,
Thank you very much for your answer. It was very helpfull, it worked out as you told me :)
That's great! You're welcome.
I can not get this to work for the simple formula im doing.
Very nicely explained. I find your videos quite useful. I use drop down list, my dependent list contains code nos ie nos only, when I try to make drop down it shows error. I know, you have video on this, can you pl send me video link. Thanx.
Hi @Leila, Great to learn from your videos. But is there way that a cell also represents/ indicate corresponding values for the apps selected?
im assuming its possible to do something as simple as checking the dropdown to see if its value = x and then adding something inside the field if correct or false.
Great Video Leila. Can you use your index formula instead the indirect function to make the data validation list dynamic? The index match combination is not as volatile as the indirect function, correct?
Thank you Michael. Yes, the Index with references can be used for dynamic drop-down lists. Indirect is a good formula but like you say, it can slow things down. Another option would be the choose function in this case. Will make a video on that too.....
Thank you Leila, I love watching you weave your magic!
Michael Diamond hhloopPogo
How would you create a Index from a Data Base to then be able to work with. For example in Column A, I have a list of Sales people (Say 20 people max but not all people sell all products and some people sell multiple products eg. Anne Smith appears in the Column A 36 times Joe Sales appears 10 times and Margaret Nil doesn't appear at all this month. On worksheet #2, I want to create an Index of ONLY the people who appear in Column A from this particular Database and to be Indexed only the once in worksheet #2, so it will only generate an Index with each person name just once regardless of how many times they appear. Thanks Leila, it's just every version I've seen of Index appears far more complex than I need.
Hi Leila,
I have been following your tutorials for some time now. I am not an advanced user of Excel and your stuff is just brilliant to start with. With regards to this particular video I have a question which may be silly:), however, because the lists are of different lengths whenever I choose NOT the longest one, the bottom cells are filled with 0s since they are empty in the table from which we generate lists. Is this stgh to do with cell formating, some Excel settings? Thanks.
Yes - you can hide that with formatting - if you right-mouse click - go to cell formatting, custom number formatting and put in #;-#;
this will hide the zeros. You can also restrict each list to the number of items on the list. Which reminds me I still need to make a video on this one :)
Wow, it worked! How do you remember all this stuff?????:)
Hi Leila, I love your videos and am so glad you make them. I'm interested in a solution here that's very similar to what you're doing. I have a table of three lists as you show and what I'm trying to do is make the horizontal sequence produce a word. So in your case WenCal, Fighterr, Commuta, would output "Business Apps" into a cell. I thought maybe index and match could do this but in looking at this and your other videos it feels like I'm missing something.
The exact use case is - I would put those words into a list, left to right and then once that sequence is in, it will match a word in another list and put that word into the final cell. Seems logical, but I don't know what I'm missing. Can you point me to the function that would do that?
Did you find a solution?
@@KirstenHiggins858 There's no easy way to do it.
@@joshuaATbarnes I’ve got an easy solution. I’ll mock it up.
Hi, i'm really having problems to replicate your formula without returning the error #VALUE!. I have gone so far as to duplicate your spreadsheet and copy the formula exactly. Any ideas.....
Use match
I have 2 lists of numbers 1-40. The first list I will make as a drop down in my worksheet. The second list is a direct correspondent to the first list of numbers. For instance number 1 (1st list) = 34 (2nd list). So if I select 1 from drop down list in worksheet I want to have 34 populate in the next column over. Would like help on how to do this with 40 numbers.
OMG Leila! I really love your videos! You explain them in such an easy to understand way!
I got a question for you: would this work on Google Sheets?
Very happy to hear that Bruno - hmmmm never tried it with Google sheets. I'll let you know....
Leila Gharani Thanks! I really needed to create drop down lists based on different columns and indexes, but I can't figure this out in Google Sheets (I love Excel, but I have to use Sheets at work, so I'm trying to figure this out)
The first version (column G) works on Google sheets but the other two versions seem not to work - I get formula parse error on there..... unfortunately....
Awesome videos
Very helpful!
Hi Leila, Thanks for your video regarding Index & Match functions. I have now started getting a knack of these two and it's working fine. Thanks very much indeed for your amazing teaching. The index formula for the list does not work for me "=INDEX(A20:C20,$H$19)". It gives an error #VALUE!
Can you please help me on this.
Bahir
What do you have in cell H19? If it's a value between 1 to 3 then it should work....
You're very welcome for the videos Bahir :)
Hi, Thanks for the reply. I got it now. I took the cell reference of list1,2 & 3, instead of selecting from the list 1,2,3.
That's great Bahir!
@@LeilaGharani Does it always have to be a value 1 to 3(in this case) Why can't it be "List 1" or "List 2" My mind tells me I should maybe use indirect for this.... Not sure...
Update: No need, I've seen your match video, thanks :)
hi Leila, love your videos thanks for posting them! I have a quick question. Is there a way to modify this formula to show results from 2 columns? For example, say I want to show column 1 and 2 or column 2 and 3. Thanks!
Hi Bill, do you want to show the results of the different columns in 2 different cells or in the same cell?
Leila Gharani in 2 different cells
I think I figured it out, Leila! I just made the header for the additional columns dependent on the 1st column. Then I set up the formula in each column, with each formula being dependent on the column header for that particular column. I hope I'm explaining it well, and that this information is useful. Thanks for the inspiration and knowledge, your TH-cam channel is really valuable for my job.
That's great! yes that sounds right. Glad to have helped in some way :)
Hi Leila,
Great video but I noticed something that is disturbing me.
When you change the list number, the value in the cell of the second dropdown violates the validation rule.
This is because it belongs to the previously selected list.
Is there a way to solve that without vba?
In vba I would write a ValueChange on the first dropdown to empty the second one but ...
Thank you for your work anyway!
And this would have helped me a lot, rather than woking with 50 columns X 30000 raw Vlookup functions this would have made everything faster... anyway (dummy question of course) is there nothing in excell that can be used to update the reference dynamically so that rather than calling for the index function and specify the index, can directly point to the cell? hard to expain but rather than =index(B1:B10,A1) where A1 is the raw number, something like =B:B(+A1)?
Is there a way to do this if the information is in the same table?
Example:
NAME DEPT.
john. Sales
Jamie. Warehouse
Shawn. Sales
Chris. Warehouse
How do i make it to where it will only show the workers when i select “sales”
I thought you made a video doing it that way but cant find it.
Hello Leila , Highly impressed! I am redoing some of your work in Urdu Language for my crowd to understand. Outstanding work.
One quick question: When I am using the F2 CTRL+Enter and if the list does not carry any value then it is returning 0. Whereas in your video it was blank. How did u do it ?
Thanks again
Hi - Glad you like them! - to have the zero's invisible, you can mark the results area (so where you might have zeros) - go to format cells and for custom number formatting, type this format in: #;-#;; - this will hide the zeros from view....
Thanks Leila . Finally I get to see the Excel guru in my life :)
Cheers
You can also surround the formula with "IFERROR". Example: IFERROR (INDEX (A1:A5,MATCH (B1,C1:C5,O)),"")
If the index/match returns an error, then leave cell blank.
or
IF (A1="","",INDEX (A1:A5,MATCH(B1,C1:C5,0)))
Enjoy,
DonW
drwilcox.57@gmail.com
Thanks for the suggestion Don :)
Fantastic i love ❤️ it .many thanks for u
Hi, could you tell me how you produced the drop-down in G41, please. I know how to produce drop-downs, but how does =index(A42:C42,,$G$41) know to choose list items from the 3 lists? The drop-down options in G41 are not the headings of the 3 lists. Have you posted a video that shows your actions prior to those on this post? Thank you.
Hi Barry - it's G41 that tells it. I have numbers in that cell and since the second argument for index is how many columns to move over, G41 gives it that info - if I have List1, List2, List3 written in G41 instead, I would have to use match to convert that to numbers. I have a video on index and match basics here in case you'd like to look into it: th-cam.com/video/F264FpBDX28/w-d-xo.html
Thanks Leila.
thanks Leila. I wish there was a way of adding multiple data validation in one cell. For example, I select deposit in cell a2 and in cell b2 (for amount deposited) I set data validation to restrict amount of the deposit.
Again I select loan in cell a2, and in cell b2 I make it impossible for a loan figure to be entered if the subject has an outstanding loan. just wondering aloud
You can do different data validations in one cell - like the one you mention in the first paragraph. For the example you have in the 2nd paragraph VBA could be an option, although if you can determine the logic with a formula we could incorporate it in the data validation, either directly or through name manager.
Shau 78, select the cell you wish to limit the dollar value, go to data validation and change the "allow" field to decimal. Then, the "data" field to 'less than or equal to'. Leave "minimum" field blank and then finally, in the "maximum" field, enter the max dollar value allowed, such as, 10.99. If you don't want the cryptic error message to be displayed if the user tries to enter a value greater than your max, in the data validation "Error Alert" tab, style (stop, warning or info), give it a title (like, "Dollar Value Exceeded"), then a short message (like, "You cannot enter a dollar amount greater than 10.99").
Give it a try.
DonW
drwilcox.57@gmail.com
can this be used for a staff roster, list the dates, which then determines the staff working based on COUNTA in the dates
Ma'am, have you uploaded lectures for VBA coding in Excel?
Not yet - I'm currently working on putting together a VBA course and soon I'll get around to posting some videos on this channel too. Anything special you're looking for?
Leila Gharani I only want to learn Basic VBA coding, after that I might be able to develop expertise in VBA coding myself through practice. Ma'am if you don't mind, can you spare sometime? I wanted to obtain some career related advice from you. I am already connected with you on linkedin
Hi Mustafa - ok great. VBA is actually quite easy to learn - once the basics make sense, you can build anything on top of that. Regarding career advice, sure - send me a message on LinkedIn.
Great video, keep it up
Thank you!
Leila, could you show a video doing this same technique BUT as you choose a selection, it is removed from the list?
Do you mean if you have many dependent lists after one another and if the user selects an app from the first list, then they disappear from the list below it?
Exactly. I did this myself but thought it would be pretty neat if you taught it. I have a dynamic list, displayed in DVdropdowns across several columns. As I choose from the list and move to the next column ( DVDropdown), that particular choice disappears from the list.
Wow very nice video thank you
You're very welcome.
I never thought that there's a difference between F2+Ctr+Enter and copy & paste!
Yes - F2+Ctr+Enter can come in handy when you don't want the formatting to be copied - just the formulas :)
The last procedure "Data validation " I could not do it .I followed all your steps however only the 1st column first 3 details are reflection .When I am choosing 1,2,3 the details are reflecting for the first column .The App list is changing into the row number for column 1. Another point when I am using ,,, ( coma ) in order to keep blank for Row & Column argument -there is an error message .What should I use.
Try downloading the workbook using the link in the description. It takes you to the blog post. Scroll down and download. Your separator might be ; instead of , - it depends on the regional settings. You'll see which one, once you download the workbook. It will automatically adjust to your version.
@@LeilaGharani THANK YOU ....soo much. I have been able to do the scenario successfully and had downloaded the work book and the issue has indeed got resolved.
I'm currently creating a computation tabsheet from different rates tabsheets. Can you help me?
WOW Leila, Well done!
I am very impressed :)
Great channel
Thank you. Glad you like it :)
can we do this using switch function for this mam
Can you use this formatting for larger table numbers? For example, I'm trying to set up the data validation lists but there are 21 lists. Evidentally I can only have 3 arguments?
Hi Preston, You can have as many lists - as long as you are only returning one of them. If your lists have different headers other than 1,2,3 etc... you can use the match function inside the index to return the correct list, based on the drop-down selection - to see how to use match, inside index, check out this video: th-cam.com/video/F264FpBDX28/w-d-xo.html
Hi, I have a question relating to the "Array". Its the area where my answer is .So here when we key in the App Column number then the answer against it is present in the whole spread sheet .So why did we not select the whole MAP nstead we selected only the first row.
I would like to ask how can we create two distinct lists based on gender from a single table.
can you show me to do this on a different worksheet? I plan to make a a summary worksheet using this method.... thank
Maybe a combination of the techniques used in these two videos is what you need: Index match and different sheets: th-cam.com/video/2erErC7LvPY/w-d-xo.html
and dependent drop-down with multiple words: th-cam.com/video/w7cHgsFirLk/w-d-xo.html
When I do the same and choose which list I want, the return pads the last records with zeros. How do I make it like yours so that it shows blanks when the return list is shorter than the return area?
Right-mouse click on the cell you have the drop-down - go to format cells and for number custom formatting, type in #;-#;;@ This hides the zeros.
Superb
hello, thanks for useful videos
I've a problem it's shown zero instead of clear cell ? can you help me to fix that
thanks a lot
Hey Leila can you help me for creating a Sheet where there is a 100 of Items in the Drop Down List and the other column is link with it showing the Suppliers as per the Items selected which then goes on with Sizes, Specification, Rate Per Sq.Ft, Cost Per Piece, Guarantee/Warranty Period in the different different column with the drop down list. especially like once I select the Items & Vendor then the other data should appears automatically column wise.
Hi Virendra - maybe this video helps: th-cam.com/video/w7cHgsFirLk/w-d-xo.html - also check out the playlist for the dependent drop down lists...
how to make this working if cell G41 is not used as a fix cell. i mean what if there is a table and index function needs to work one row down with each no entry.
Hi, I wanted to show a drop down arrow with this so I used a combo box instead of data validation, but it didn't work. Could you please explain how to do this with a combo box?
Combo boxes have a different logic - this video should help clarify that: th-cam.com/video/6OtHMdTCeoQ/w-d-xo.html
your link no longer seems to include the workbook for this video. please update.
Thanks for that. Yes, I shifted things around on the site - forgot to update this. It's corrected now. It'll link you to the blog post. Scroll all the way down and you can download the complete workbook.
How to i hide the parent table and keep only the select list ?
Hello! I am struggling with something in excel. I am trying to create a dropdown list that can show a different information than the one finally displayed. Is there a way to do that? I've been trying with data validation, but i havent been able to find a way
We'll need the selection in a separate cell and then can use VLOOKUP or index match to get the other value in another cell. We can't really replace a cell value with another value (a cell can either be input or have formulas behind), so you'll have to do that in a separate column - unless you use some type of VBA code.
How to hide the items which is already used in drop down list?
BUT SO MANY SHORTCUT KEYS , WHICH I DON'T KNOW, WANNA LEARN MORE ABOUT THEM, WITH THIS I WANT ANY KIND OF LARGE DATA IN WHICH WE CAN USE VLOOKUP, INDEX, PIVOT AND OTHER FOMULAS IN ONE SHEET, AND HOW WE CAN RECOGNIZE THAT WHICH ONE IS GOING TO APPLY ON THAT DATA..
In a lot of cases you can use different formulas and features to get the same result. Some are faster, some slower. The more techniques we learn, the more options we have to tackle any problem at hand. That's what keeps working with Excel interesting :)
If I do that formatting with F2 and Ctrl + Enter, I get zeros in the empty cells. How do I make sure that the cells display nothing instead of zeros?
To not show the zeros, you can use custom formatting to hide them from view. I think I explain it here: th-cam.com/video/tGY70sdpaLc/w-d-xo.html
excellent!
Thank you.
How would you eliminate duplicates to come up with a unique drop-down list? I tried =IFERROR(INDEX(data!$A$2:$A$1315,MATCH(0,COUNTIF($D$4:D4,A2:$A$1315),0)),"") + CSE but Name Manager will not accept an array.
Hi Kanoock. My approach is to create a unique list in Excel cells first - using Index & Aggregate and then use OFFSET or INDEX to get the dynamic list depending on length in the drop-down...
Please ,,
I need to create a drop list with (a default chosen item)
For example :
If you working in a company and about 90% of the individuals are working as a (worker)
To save the time I shouldn't select the worker every time I enter the date .....
Regards
Hi Seddiq - I'm not sure about your query. Isn't this something that VLOOKUP can handle?
How to get that 1,2 ,3 selection in the dropdown instead of the Cell values
Click in the cell that is going to have "1,2,3" drop-down
Select data validation, allow = list
In the source field simply type "1,2,3" (without double quotes)
Done!
سپاس
hi, wanna learn macros , lil bit confused and vba ? its functions , purpose and all...thnx
Hi Rekha - I'm planning to make some videos on macros in the next months... stay tuned...
thnx, it means alot.
Leila, i have one challenge if i add new app's name in any list no, didn't show, basically i m taking about data validation drop down, in this formula we have fixed area/ reference cells, however, i want as if i add new app in any list , automatically show in drop down list, could you assist me? Thx
Good question Sanjay. You can make it add apps automatically to the drop down if you turn it into an Excel table. Click on any cell inside the table and press "Control T" - then go to the bottom any type a name. The drop-down list will automatically update...
Thank you so much , yes i did.(dynamically data validation List).
Mam, pl told me, whether this formula is used in next page i.e. in 1st page the statement and 2nd page this formula ! I've try it but it cannot be used !
Not sure about your request Shirish. you can download the workbook at the bottom of the blog post and check. Alternatively you might need this version for your dropdown: th-cam.com/video/w7cHgsFirLk/w-d-xo.html
Hi Leila. I am beginning to think your videos do not in fact work with MS Excel for Mac at all. Every example I try even recreating your data and following along does not work. And Microsoft is apparently unable to determine if its software is capable. Do you have any suggestions?
As far as I'm aware the drop-down functionalities and form controls are all compatible with Mac. They might just be called something else or be under a different menu. Have you tried downloading the workbook (link is in the descriptions) to see if it works on the mac?
Hi Leila, I have another question. For starters, your column headers (List 1, List 2, List 3) do not match the drop down options in cell G41 (1, 2, 3). So how does it index anything at all? Also, it's unclear why you chose the first cell/row from each list as in every other example, you have used the full range. I wonder if there is rationale that is not explained.
I copied your formula exactly using my correct cell values. It does not work for Excel for Mac. #REF for the entire data set. Also, just curious - as the methods you teach are far more advanced than any of the examples provided in Excel, how did you learn all of these advanced techniques and tricks to manipulating data in Excel? I'd love to learn, although it appears I must abandon using on Mac to do so, or spend the additional time to learn and document all the ad-hoc methods to make formulas work in Excel for Mac.
Thanks.
Hi Stacey, I explain the Index formula basics in this video: th-cam.com/video/F264FpBDX28/w-d-xo.html - Here I explain in detail how Index works. In this example though, I don't need to match for the List name because I am using the list address based on the number - for example the 3 tells the index function to move three columns and that's list 3. The reason I only index the first row, is because you only need to index the area where your answer is - as I drag down the formula, my index area moves down as well.
Yes - Mac is not optimal if you use Excel a lot. At least not until now....
How can I get list of customer from whom they has purchased by just entering customer code
This video might help you if your data is arranged in columns: th-cam.com/video/avVLznHODVA/w-d-xo.html
Hi Leila.
I have a question. Can i use DV + INDEX in the following situation......
La Liga
Name Goals
Ronaldo 22
Messi 21
Villa 14
Rossi 12
Llorente 12
That's one team. I could have 4 or 6 teams.
I tried, but only get the results for the first team. For the other teams i get #REF! error.
Many thanks.
How is the data for the other teams organized? Is it right below or on the next column. If you have them right beside each other with the team name as header, you can use a match for the columns - You can send me a sample data set if you'd like - my contact info is on my site, but I'd probably only be able to look at these in June....a lot of things to get done in the next two weeks....
Hi Leila and thank u for ur reply. Each team has two columns of data + 6 or 5 or 9 rows/lines of active data. But thank u very much for introducing us to INDEX(Reference.....) that solved my problem totally.
There's only one minor thing.....
In my example stated earlier u can see 5 lines or rows of active data.
But the next team has only 4 lines or rows of active data. So instead of text i get zeroes in the blank cells. How can this be resolved. Million thanks for ur valuable time.
Hi James - maybe custom formatting would work in this case. Highlight your cells, right mouse click - go to custom format - for custom number format, type something like this: #;-#;;@ - this should hide the zeros.
Hello Leila. So grateful for ur help. #;-#;;@ works just fine. Thank U!
I tried playing around with Choose(MATCH but it returns error
CHOOSE(MATCH($F$29,$C$4:$E$4,0),C5:C26,D5:D26,E5:E26)
If you're returning ranges, you will need to use data validation drop-down - for that you will also need to fix the returned ranges....
Great!
merci
I saw the problem..
i need to put the selection to be days.
Example... if i put monday it will show for that day.
When i tried it showing #value!
Yes - if you use text instead of numbers, you need to include the match function inside of the index - see th-cam.com/video/F264FpBDX28/w-d-xo.html for more details.
Leila Gharani... got it. Thanks
Why it showing for me #value
You are dynamic.....
😘😘😘😘
Index function with array showing me #value
You'll have to press "control" "shift" "enter" instead of the enter after you write the formula...
💓 💓 💓
Hi Leila. Hope all is well with you. Can I ask help from you? Can I send it to your email?
Hi Lila, please share your mail id where I can share my excel problem with you, hope it's ok with you
Regards
Ashish
Hi Ashish - I'm currently busy with a few projects - best is to post your specific question either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...