Grab the file I used in the video from here 👉 pages.xelplus.com/dynamic-dropdown-file
@carlladd119
6 ай бұрын
Hello mam, I am needing to have a blank cell for selection in the dependent drop down but I cannot make it work, is this doable? I am able to make it happen on the initial drop down, just not the dependent one. Any suggestions or can you help me put that together??? Thank you, Carl
@carlladd119
6 ай бұрын
Also, can the dependent drop down be tied to another workbook such as a room roster whereby the room is vacant evidenced by no name being in the occupant name cell for the room.
@andrehinds7501
4 жыл бұрын
I love excel and I've been known to be able to create some nice sheets and simple workflows but seeing your skills. I feel like such a novice but in a good way. I am extremely impressed with your tone, your explanations and you examples. You are awesome. You've gained a subscriber today. I hope you gain many many more!
@anillukhi9335
3 жыл бұрын
Leila, since I have discovered your videos, you have been my go to for ALL excel related questions. There are countless videos out there but what sets you apart is the way you explain it! Thank you for posting the amazing videos....
@tayanchakraborty6283
4 жыл бұрын
How automatically update 2nd drop down when change 1st drop down.
@fran123456
4 жыл бұрын
I did it with VBA, unfortunately in case you can't use them
@Ebiru2387
4 жыл бұрын
@@fran123456 is there a sample code you can provide by chance?
@MrSupernova111
4 жыл бұрын
This is incredibly complicated. Its amazing to me how the human brain's intuition can quickly deduce information effortlessly compared to the difficulty in passing on that problem solving ability to a sophisticated program like Excel.
@bensharratt8451
5 жыл бұрын
you do the best excel tutorials online, fact. Just wish you did a few more about macros though :(
@opt6037
5 жыл бұрын
Alt + D + L will bring up the data validation details/window for your selected cells... super helpful when doing lots of these and playing around with ideas 👍🏼
@mdcs1992
4 жыл бұрын
I know you get a lot of comments but I am in my sixties and these days struggle learning new things. it can be so worrying. This video, as all your videos is the best available anywhere on KZitem. It gets me over that "what's wrong with me, why can't I get this" hump. Thank You.
@HollandHillSpies
5 жыл бұрын
Thanks Leila! two thumbs up for both methods, I find the table method far more intuitive and easier to recall and reuse.
@utibe007
4 жыл бұрын
Totally agree... In fact, I just used the table method right now
@germanpetslover.lahori.8184
2 жыл бұрын
@@utibe007 1%qqqqq%qqqqqq%
@kethanchordia
6 жыл бұрын
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..
@sojiogungbesan5547
4 жыл бұрын
Very helpful, many thanks. When I change "Productivty" to "Game" in my own file the cell value doesn't change automatically, though the dropdown values are correct, please how do I fix this?
@sktneer
6 жыл бұрын
Another way to create the dynamic named range using tables is... myList = CHOOSE(MATCH($F$4,$A$4:$C$4,0),TableProd,TableGame,TableUtility)
@LeilaGharani
6 жыл бұрын
Thank you sktneer! That's a great function too! Thank you for your feedback :)
@sktneer
6 жыл бұрын
You're welcome Leila! :)
@femeeee1
5 жыл бұрын
how to summarize the datas of my parent drop down and its dependent drop down?
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
@ExcelForFreelancers
4 жыл бұрын
Great Leila, this training was super helpful
@keithmctaggart7663
4 жыл бұрын
Hi Leila, I have a triple DDDL scenario: choice 2 depends on choice 1 and choice 3 depends on named ranges of 1 & 2 concatenated which are located on another page. This worked until I made all my named ranges dynamic with OFFSET. Is this fixable using something like in this video?
@harish19kumar
5 жыл бұрын
Hello, Many thanks for helping us with your simple yet step by step process with respect to Excel. I tried the offset method and am running into issues. 1. I see # value error but when i press f9 I can still the full still. Q: Why does this error is shown ? 2. When I copy the formula and paste under data validation , it shows the dependant lists but the 2nd list doesn't change when the 1st Drop down values are changed ? Any help in this regard would be highly appreciated . Please note , I would want to avoid macros usage as much as possible due to limitations .
@vshnumacha2114
Жыл бұрын
I am using same formula =OFFSET($BD$1,1,MATCH($C$9,$BD$1:$BJ$1,0)-1,COUNTA(OFFSET($BD$1,1,MATCH($C$9,$BD$1:$BJ$1,0)-1,20,1)),1) but as and wen I select values in first cell .. in second cell values are getting reduced gradually. the second row has 13 values.. like when I select first cell value - it is displaying all 13 , next row - when I select it is displaying 12 only.. and at certain point nothing is getting displayed..please help
@gig777
Жыл бұрын
Leila, great video, but I think there is a minor error in one of the formulas on your website which is: =OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1)),1,) It looks like that comma after the last "1" should be deleted. I was getting an error message and then deleted it and it worked perfectly. :)
@yoosuf425
5 жыл бұрын
Wonderful video, could you please tell me how to create a dropdown list with new data entry in the same field.
@Howdy1836
4 жыл бұрын
After watch your first index drop-down list, I was so much hoping you had a follow-up. You are my new sensei. Great video, Thank you. =OFFSET('Table_ER_GL_Acoounts'!$A$1,1,MATCH(INDEX(ER_Worksheet!$C$2:$C$30,A24,0),'Table_ER_GL_Acoounts'!$A$1:$F$1,0)-1,COUNTA(OFFSET('Table_ER_GL_Acoounts'!$A$1,1,MATCH(INDEX(ER_Worksheet!$C$2:$C$30,A24,0),'Table_ER_GL_Acoounts'!$A$1:$F$1,0)-1,100,1)),1)... geez...lol
@tressagailgaming5127
5 жыл бұрын
Hi Leila, i have a questions regarding this formula and the application to the spreadsheet that i am currently writing. i have a range of data sorted and indexed by this formula: =IF(ROWS(D$143:D143)>$D$101,"",INDEX($D$103:$D$139,SMALL(IF(FREQUENCY(IF($D$103:$D$139"",MATCH($D$103:$D$139,$D$103:$D$139,0)),ROW($D$103:$D$139)-ROW($D$103)+1),ROW($D$103:$D$139)-ROW($D$103)+1),ROWS(D$143:D143)))). your video above has solved all of my problems baring one. the drop down list now created has blanks at the bottom due to the sorting formula that puts a "" in the blank cells. this is being counted in the formula in your video. how could i amend the formula to remove these blanks from the drop down lists that have been created? the current formula i am using is: =OFFSET(Matrix3.3SWIP!$D$142,1,MATCH('steve WIP'!E3,Matrix3.3SWIP!D142:AA142,0)-1,COUNTA(OFFSET(Matrix3.3SWIP!$D$142,1,MATCH('steve WIP'!E3,Matrix3.3SWIP!D142:AA142,0)-1,25),1)) would appreciate any help you can give me here. i have been trying to get this to work for a couple of days now. Thanks Steve
@johnallenrichter
2 жыл бұрын
Hi Leila. All the secondary data for each main category fills down in rows. How do I prevent this? Yur example onoly shows the first option in the second cell. Mine fills down all the options.
@macroiu3778
2 жыл бұрын
I dont understand why I got N/A when replicating your formula to reference table other than Sheet1 with following revision: =index(('Sheet2'!TableProd[,Productivity],'Sheet2'!TableGame[Games],'Sheet2'!TableUtility[Utility],,,MATCH($F$$,$A$4:$C$4,0))? Pls help me out, tks a lot!!
@NiteshRS
3 жыл бұрын
Hi Leila. I am facing an issue with copying the formula. I copied it the way you said but however when i execute, it seems linked to only the first row that i copied from. It seems the cells have not got locked or any other reason. Can you please verify my formula if it is correct? OFFSET($J$4,1,MATCH($G$8,Category,0)-1,COUNTA(OFFSET($J$4,1,MATCH($G$8,Category,0)-1,15,1)),1) When i copy this formula across all rows, they don't work as expected, it works only with the row cell that i copied from.
3 жыл бұрын
I faced this similar error in data validation yesterday and I have been looking for the source of error. The formula is using structured references. I'm happy I can now figure out why my formula was not accepted. =UNIQUE(FILTRE(_TabMasterLev5[Level 2 Description];_TabMasterLev5[Level 1 Description]=_Level1Selection;""))
@joicefargen.g6692
4 жыл бұрын
Please give a video about match formula for MULTIPLE DROPDOWN LISTS & get a result by that MULTIPLE DROPDOWN LISTS. And the dropdown lists and result are show in ANOTHER SHEET in the excel.
@mafarozzo
4 жыл бұрын
Hi, awesome vid, but I need some help urgently! I wanne create 2 independent dropdowns (easy) with the answers ("A";"B") and ("C";"D"), and then the answers in a third dropdown ("E";"F" / "G";"H") shall depend on the combination of answers in the first two (A&C; A&D; B&C; B&D)... Any way of doing this? Thanks in advance!
@avitalalef9947
2 жыл бұрын
IT SAYS I PUT TOO MANY ARGUMENTS :( =OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1)),1,O15)
@sergeymelik-yolchyan2128
Жыл бұрын
Perfect match with what I do needed! Thank you for your help!!! I have tried the formula and had the following result: in case i choose [height]=1 everything works properly and correct data reverted. But when i try to change the height into "2" (i.e. 2nd line in the same column) it reverts #VALUE! (but the value is written in the second cell). Continuously, i cannot use the "=counta" function here to show the range out of which i want to take the filled data out. Could you please advise on this situation? Thank you! P.S. does the version of the excel matters here? I use 2016 edition.
@tshepomoleko6708
5 жыл бұрын
If A1="NA", B1 must return 0 and lock to prevent any value entry. However, if A1="A", B1 must unlock to allow value entry from any of the specified values chosen from a drop down menu. Let's say 0,2,4 and 6 are such specified values. Please help me.
@ges05
3 жыл бұрын
Leila, you are great!! And another great video of yours. Thank you very much! Nevertheless, I'm having a problem with the data validation. For some reason, I just get as error when clicking "ok" after pasting the formula, in the "data validation" dialog box... The formula it's the same as yours. The only difference is that I have the main data in another sheet. Could that be a problem? I believe not. Many thanks and all the best.
@shamimislam9674
Жыл бұрын
Hi, in Offset tab height is only 20, it's not dynamic. Anyway i solved it by using " COUNTA($A:$C)" in replace of 20 . Can help it any alternative ways
@insitemyhead6585
3 жыл бұрын
I have : - a list of colors as a table in file1 - a dropdown list of colors in file2 My problem is.. When dropdown list in file2 is "red" and I change the list of colors in file1 from "red" to "reds", the dropdown list on file2 is not updated.. it still "red" unless I scrolldown and choose "reds". How can I make this dropdown list updated. I use power quary to append 2-4 sheet but still.. it doesn't updated.. Many thanks..
@tier1athlete829
11 ай бұрын
How can I use offset to generate a name drop down list based on gender of just females who are in grade levels of 7,8,Freshman, Sophmore, Junior, Senior & Post College and males who are just in grades 7th & 8TH? In my data sheet I am names in a column, gender in the next column & then next column is grade.
@relaxingblessings3984
2 жыл бұрын
Hi Leila, am facing issues.Its not changing dynamically. The dropdown list is not changing after selecting the 2nd row lists, like if i select Games and Kryptis. And when I change to produtivity, then the dropdown list remains Kyptis, its not changing to productivity Group list dynamically. Also, in your video, you are not selecting the other dropdown list. Request your support.
@entropicplays162
4 жыл бұрын
This is fairly close to my original problem but still doesn't quite address it, which is that I have an existing index column which is dynamic based on a list i.e. if I pick A, it will give me all items in the index related to A, but if I pick B, it will give me all the items related to B and so on, and I want to in turn transform this index result into a list. However, because the ranges of A, B and so on, are different, the index list also grows and shrinks in size, meaning if I make a list based on the total number of cells my index list covers, then the final list result will show a lot of blank ("") cells I don't want there. The workaround is to do what you did here and make A, B etc. separate data columns rather than based on a dynamic index list, but that means that I have to redo a majority of my sheet. Is there a way to make a dynamic list based on a dynamic index result without having the blank ("") cells appear? EDIT I solved it by using the SUM function together with COUNTBLANK for the height argument.
@genevievewilson9271
3 жыл бұрын
What happens of I have a lot of blank spaces in the middle of my data? Imagine if 'Voltage' and 'Inkly' in Productivity Column suddenly never existed. How would I be able to get the dropdown down to exclude the spaces that now exist while continuing to list with 'Sleops' and everything after?
@zerberoo9937
4 жыл бұрын
I have just written below your video about the dependent combo boxes.. This is the start of solving my described problem... But I'm getting a new one right away. ;) Is there a way to implement this tutorial into the combo boxes. They are much nicer for my sheet... I'll test arround. Perhaps I'm getting a solution or find another of your videos. Thx btw. For the help. It's easy to understand and simple to follow. Have a nice and sunny day
@falakfalak3853
2 жыл бұрын
Hi Leila, your videos r amazing. I'm trying to make a dependent drop down list with multiple names but it's not done, plz make a video of "dependent drop down list" with multiple names like heading "productivity items name". Because in dependent drop down list not accepting "space". Its accept only "underscore sign".. thanks
@rgenesis07
3 жыл бұрын
Hello, Thankful for your help but I do have a question. What if I did not want the listed items to be shown as a drop down list. For example, in this video, is it possible that say I click "Productivity" or "Games" from the drop down list, that it would automatically list the listed items instead of showing as a drop down list? Not sure if it made sense to you but hopefully you can help! Thanks again!
@BalkanBiker
2 жыл бұрын
Seriously I have been looking for a dropdown list that copies parts of the other sheets, like Sheet 1 has dropdown lists that gets parts from Sheet 1-50000000000, and then edit them on sheet 1 so that the change reflects on whatever sheet I choose in my drop down list on Sheet 1. But there are like zero videos on it! Really frustrated of watching videos that lead me nowhere. This video was informative, but not in my case, fuck Excel. Outdated 90s crap that need a proper modern replacement.
@steemin71
4 ай бұрын
In the video, the 2nd drop down seems to update automatically. On my own sheet, the drop down options DO change, but I still have to go in and reselect. You know why that would be?
@Regular.Biceps
5 ай бұрын
What if my list has multiple entries? Like a name is appearing multiple times in productivity list Where shall I use UNIQUE function?
@AmitChandra_YouTube
3 жыл бұрын
Ma'am, Excellent Tutor you are. Thanks a lot. Your solution saved my time through the 1st method. I have to modify it very little and working in my worksheet effortlessly. Be Safe and Healthy, and happy also.
@CYPHQ
4 жыл бұрын
Thanks Leila. Is there a way to create dependent drop-down lists using Method 2 (and without VBA) so that an inexperienced Excel user can just conveniently rename the columns and every dependent cell automatically updates itself? In essence I'm trying to create a blank template for users.
@jonathanmartin9139
6 ай бұрын
Thank you for the video. Instead of returning the first cell it actually spills the whole list down. I also can't paste into data validation. Do you know what I'm doing wrong.
@PFunk-kt9gc
11 ай бұрын
I really appreciate your videos. I'm trying to create a dynamic dropdown list with dynamic vlookup. It works fine when my source data and my input screen are on the same worksheet but doesn't work when source data and input screen are on two different worksheets. Any ideas???? My source data consists of three columns of data converted to a table. My input screen has one column setup with data validation for column 1 of the data table. Then the second two columns are setup to use vlookup to pull that matching data from the second and third column of the data table. If I add new data to the bottom of the data table it will be recognized when the input and data are located on the same worksheet but will not be recognized then they are on two different worksheets. I'm using Microsoft office Home and student 2019
@nidamazharali5301
10 ай бұрын
Hi Leila , I would like to know , you made drop down list by using table and once you use that list as a drop down in a cell lets say from your example it is *Games* but later you chnage the name instead of Games you name it *Playstation* , your dropdown list will be automatically updated but the cell where you have already Games selected did not change automatically, do you have any suggestions for me like that pls ? becasue in my sheet I used games but now I chnaged the name and have to check manually . which is time cost for 20 sheets . Thank you in adavnce .
@happytimecreations38
11 ай бұрын
Hi Leila, I love this trick you are awesome. I have a doubt I applied the OFFSET and it is working but only thing is when I change the source cell the destination cell default selection remains the old dropdown value. How can i refresh and show the values connected to the source selection as default thanks in advance?
@mostafa4321
5 жыл бұрын
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 :)
@RunicMike
3 жыл бұрын
Followed the vid, did exactly as you did and neither way worked. Well one did, but I couldn't replicate the success. Not your fault, just frustrating.
@Kooseandco
3 жыл бұрын
Thank you so much for starting with a hard example because obviously people that have to look up tutorials know what they're doing and are obviously already fluent with the match function
@FamilyCare-km3zn
4 жыл бұрын
Perfect Lelia; smooth teaching; but offset is hard to understand as described quickly; so table method is the best ; can you pls. make a special video for Match & offset parameters... You are one of the best Excel teachers on KZitem... Keep on.. pls. create a new videos for the new version 2019 features for advanced excel...
@FamilyCare-km3zn
4 жыл бұрын
Solved; Leila made another video for more offset fn tutorials at the link: kzitem.info/news/bejne/mKOlmneFp5lohpw; ***** five stars
@anupr1435
2 жыл бұрын
After putting counta formula my drop down list stops working....& If I didn't do counta it's works fine. What could be the reason ?
@tamimihayat
4 жыл бұрын
@Leila Gharani... My 2nd drop down does not automatically change text unless i click on it and choose an option from new list.... Now the question is, Is there a way to make drop down list an optional thing? for example if i chose option 1 in list A1 then i should get a drop down in B1, but if i chose option 2 in list A1 then I should not get any drop down in B2 rather a text appears that tells me not to put any value in B1... Is that possible.
@andrepandohie582
2 жыл бұрын
Do you have a video that show drop down with Multiple choices with multiple value and columns using dynamic? For example I wanted to make a year to date and quarterly chart with drip down but the source are from 4 groups.
@tong.tranquang
2 жыл бұрын
Thank you for your video, I'd like to ask what if we change values in dropdown list tables and I want these new values automatically to be updated in the big table (in which I selected the old values), how can we do that? Thank you.
@mibrahimmeo
2 жыл бұрын
Nice but I am stuck in other situation. I want drop down of current column. Which can be expandable. I tried and failed with error. Counta bounds the length of current unique values & new addition can't enter.
@mikeaikens7823
5 жыл бұрын
Your videos are great! Thank you for all you do. Is it possible to add data to the source list by typing into the combo-box? Thank you!
@areebahi
4 жыл бұрын
This index-match formula for creating dependent drop-down does not work when we are using table. If our columns are not part of any table then it works fine. Do you have any solution for this?
@McIlravyInc
3 жыл бұрын
My data is arranged differently, in that (to use your data as example) all apps are in one column, and the division is an entry in a 'division' column. I find it hard to use some of your examples because of this. Is your data laid out in order to eliminate any duplication? Is this a limitation of your example or is there some kind of data arrangement best practice that I am unaware of, which you could point me to? Thanks!
@rickiemichaels
2 жыл бұрын
If we select a value from the secondary list ( say the 5th or 6th item ), then go ahead to change the value is the primary list, the value in the secondary cell doesn't update automatically to the first item of the new primary value lists., Any workaround for this
@Ebiru2387
4 жыл бұрын
Is there a way to have the list refresh when a new value is selected? For example, i select fruit from list 1, which displays 3 options in list 2, and i choose apple from list 2. But when i select vegetable from list 1, apple still remains in the cell even though the vegetable values are present in the drop down. Is there a way in my example for a default vegetable value to appear or to have that cell go blank?
@Marlon5k
5 жыл бұрын
Awesome tuts! I tried the 1st method, but when dropping down the list, it shows me values repeated, and they're not repeated on my table. How can it only show unique? Thanks
@mutlunun
2 жыл бұрын
Hi Leila. How can I prepare a list of monthly sales using multiple droodown lists???
@ajamschon2
4 жыл бұрын
Hello, great video... Could it be possible to make a drop down list case sensitive? I am looking for that solution for more than two days online but I can´t find a solution. So in case the client decides to type instead using the drop menu but get the right name but not in capital case the client gets en error. Could something like this be possible? or at least make make that the client can´t type on the drop down list? Thank you
@bulkoxx
10 ай бұрын
Hi; Did anyone noticed that once you’ve selected the value from the 2nd filter list, the formula is gone and you won’t get the new drops refreshed after that?… is there a way to fix it?
@nikhilgohel2069
4 жыл бұрын
You have solved my lot of problems leila, thanks a lot..
@mustafakhalil1616
3 жыл бұрын
How can I apply the index and match functions on multiple cells? I want to connect two columns with each other. For example, If I want to add the option "Cards" from the dropdown list on the first column, I want to see options that are dependent to the "Cards" option in the first cell's drop down list on the second column. Please help.
@mohitkhandelwal5556
4 жыл бұрын
Your video is awesome. But I have need some different way. i.e I have two different list in two columns like first one is category and another one is product. I cannot create a column list for every category to create list. Can you suggest something?
@goyalmukeshkumar
Жыл бұрын
very super drop down list both formula offset and index match👍
@rummaan17
6 жыл бұрын
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.
@sardorbekhayitboyev6177
4 жыл бұрын
Hello. Can I protect xls file from copied one computer to another computer with VBA? Do you know?
@glory-mayjacobs7061
3 жыл бұрын
How do you automatically update data that has already been selected with the dropdown list when you correct an entry in the dropdown list. E.g. I have a dropdown with a spelling error. After completing the data with the dropdown, I realise there is a spelling error on the dropdown. I fix the error, but now I have to go back to the data and reselect the entry. Is there no way that it automatically updates the data?
@GianRamos
4 жыл бұрын
can you use this formula for the whole column? i tried it. it does not work. so i used =indirect instead. let me know if that's possible
@prabhatseason
2 жыл бұрын
Hello Leila, it working for one cell only whenever I try to put the same in another row it's not picking the results from their drop down list it's always picking from the frist one only could you please help me on this?
@mohammadrezahosseinian1094
2 жыл бұрын
Dear Leila, i have done exactly as your tutorial, but my data validation list only shows 8 values! what is the problem?
@grafikerinbiri
Жыл бұрын
I want to create "product ingredients with dropdown menu" in Google Sheets. Any ideas?
@andyb19822
7 ай бұрын
Hi, I am struggling with this formula as I have the data table on another tab and each time I go to that tab then try to go back to the original tab excel is giving me an error code. How to work around this?
@jagjeetsachdeva5829
3 жыл бұрын
It didn't work for me. I have some blanks in my data. And, the formula is returning 0.
@zulfiqarqureshi3802
2 жыл бұрын
Hi Leila, Love your videos. plz can u help me as how i can get data of columns in a single drop own list. means in one sheet i have monthwise product consumption and on another sheet i want the drop down list of months where product consumption changes monthwise so that i can make graph on month basis on this sheet.
@MrTosha43
3 жыл бұрын
Does anyone know why I get am error?,formula is written the same as this but when I press enter to test the error You've entered too many arguments for this function. Tia
@kerguule
Жыл бұрын
Any idea how to make a dynamic drop down list but exclude not only empty rows but also rows with certain text or color for example?
@Najfl
4 жыл бұрын
Hi. I am looking to do the dependent dropdown list as this one with vba, but when i record a macro and try it it gives me an error. Is there a way to do this with VBA?
@CollinsChukwuemeka-io4dj
Жыл бұрын
Hi,can you guide me as a new person learning to work with excel.I need to learn excel 101 and master it.
@queenbitterblue
3 жыл бұрын
Is there a way to incorperate what you've done here into another sheet? Assuming you're doing real life data and you don't want this rough work in the main data page.
@harrystephenson1101
Жыл бұрын
i keep getting a spill# after ive done the counta and second offset =OFFSET(Sheet1!$A$1,1,MATCH($A$3,Sheet1!$A$1#,0)-1,COUNTA(OFFSET(Sheet1!$A$1,1,MATCH($A$3,Sheet1!$A$1#,0)-1,20,1)),1) sheet 1 holds my source data and its all in row 1 $A$1# - its also dynamic using a transpose formula from another page , is that the issue? ( =TRANSPOSE(UNIQUE('1st - Floors'!A2:A1000)) ) from a data range called floors
@LeilaGharani
Жыл бұрын
Something is blocking the spilling range. Maybe this is helpful: kzitem.info/news/bejne/wn2uvn-vs3mHkpw
@7Bloodfire
Жыл бұрын
I did have a problem when trying to create 2 columns of dependent dropdowns on another sheet. Results in a #REF error.
@Sazz_kiran
3 жыл бұрын
i have 4 rows and a dropdown in 5th. I want to make dropdown in 5th row available only if other 4 rows are not empty. Please help how can i do this.
@boombaby1990
2 жыл бұрын
Madam, Is there any way to create a list without making a separate list which is having blanks?
@ciprryenutza
4 ай бұрын
hi. when switching from Productivity to Games the text listed for dependant drop down list remains the one from Productivity, even though Games has been chosen. e.g. when switching from Productivity to Games, the text in F5 still shows WenCaL now, but if you click the drop down you have the updated list. How can we empty cell F5 when switching from Productivity to Games? Maybe the user will change first drop down and forget to change the second one.
@LeilaGharani
4 ай бұрын
This should be helpful: kzitem.info/news/bejne/2I2X2Y1-gGOdoII
@ahmedhashish9211
5 ай бұрын
2nd method doesnt exclude blanks, what am I missing?
@shortestshadow
4 жыл бұрын
@leila can we streamline the formula with dynamic array formulas?
@zahoorahmad3632
4 жыл бұрын
Hello mam hope u r fine i need help in data valadation.. can you plz tell me how i share my vedio with you containing problem
@AhmadMohassebElroby
4 жыл бұрын
I'm using Excel every day but when i see your lessons for Excel i love Excel too much. ♥️
@seshadrir85
3 жыл бұрын
Can you please showcase how to use this with Named Ranges.. I am getting an error while using the named ranges.
@Andrius1115
3 жыл бұрын
THANK YOU VERY MUCH! I was trying to do it 2 days already !!! I have tried with CELL NAMES from another sheet and INDIRECT function for DATA VALIDATION, but it seems that it's not possible that way... For those who have formulas (with " " results) instead of plain data, I suggest using COUNTIF formula with a criteria of "?*" ---> that way you won't get blank values in a dropdown menu.
@antnfs
2 жыл бұрын
I know you posted this a long time ago and got no responses but want you to know that if this works it’ll give a solution to the exact problem I have so thank you
@antnfs
2 жыл бұрын
Yeah it worked, I didn’t know about the wildcard feature in excel. You the MVP
@pedrojorgetavaresrdealmeid9896
8 ай бұрын
Leila, I just discovered your tube stuff know... Congrats!! Your vision about how to drive through the Excel issues is so bright! Even so, I'm dealing with something I can't find the solution » After using Offset formula, with the CountA and Match as you did, my final result is not one match data but a list of all table I've on my data attached. I reviewed several times the video, already tried to exactly copy the same formula, but the result is always the same. Did you have someone facing similar issue? Thanks a lot for your feedback!
@LeilaGharani
8 ай бұрын
You could try downloading my workbook (pinned comment) and compare with your solution.
Пікірлер: 566