8:51 If you want to avoid adding an underscore in "Fruit_Smoothie", make it look more professional/readable, you can add a substitute function to replace the space with an underscore. =INDIRECT(SUBSTITUTE(B7," ","_"))
I was going to suggest the same thing. Personally I was thinking of using CamelCase for the named range, and then removing the space within the Indirect function, but both options would accomplish the same thing.
In addition, you can indicate the name range directly in the "Name Box" instead of clicking the "Create from Selection". Anyway, this video shows the basic steps. Great! Looking forward for the next video!
I submit that rather than creating a Fruit_Smoothie that you put as the source - =indirect(substitute(b7," ","_",)) - I also suggest adding headings for each of the other drinks and create a selection with a blank cell - this way on the dropdown it will have a blank cell as (the only) value for Plain Milk or Chocolate Milk selections.
KEVIN! This is sooo useful. I recently discovered data validation but had no idea I could make my life easier by naming the drop-down list. Thanks a mill.
Kevin, thank you very much for another great tutorial. I watched your old dependent drop down list video from 3 years ago, and I was struggling to apply the formula to multiples rows, but this method solve that problem. Thanks again.
Again and as usual absolutely brilliant! I thought I was going to climb the walls from trying to get an answer for this. So many tips again in the process so thx a mill, you're definitely my No1 go-to for anything Excel 🙂
Great video. So now building off this, I want to track quantities. Not just the for this order, but for history. This person orders a regular coffee with 2 sugar cookies, so I add another column for quantity, easy enough. But want to export that information to know how many coffees or sugar cookies or whatever are ordered over the month to see if I should make more or take them off the menu. Just a thought for the next video to build off of.
That only works if the values on the first and second column are unique values. My case test requires that the drop down on the third column considers results from column one AND two. Additionally, column 2 results will essentially show a list of 2 values that can be applicable to items on column 1 but will generate different results in column 3.
Hi- the wealth of info you provide in your videos is priceless. Thank you for sharing. Do you know if it is possible to parse full(historical not incoming) email threads ? If it is, do you offer any type of instruction that would cover that subject?
In Lotus 123 -indirect- was not necessary. I don't know why MS has simplified this function. Same with the "D" functions: Dget, Dsum, etc. They are not dynamic, just useless. Thank you K.
This video was great, thank you! I also wanted to ask if there is any possibility on how to dynamically change the name meaning that if i change the value in the first list then the values in all the follwing lists change to the first option (does not matter) in all the other lists?
Yes, it will work if your lists are tables. But you will still have to select the table and give it a range or list name. You cannot use the table name.
Yes it will. I think. You have to select the table column data, when defining the name range. This is very good if you want to keep adding options and the named range dynamically updates.
This is a great video, I'd like to make one additional comment which drove me crazy and couldnt figure it out. The Item can only be one word if you have something that consists of two words than the indirect function wont work. Id like to know and learn if there is a work around for that.
Appreciate the great video as always! I've never figured out a way to adjust the font size of the list within the dropdown box, or is it even possible?
super helpful, many thanks for uploading this! do you know if it is possible to create a named list that only displays sorted and unique values when referenced in the data validation? for your example, in my first list i would have multiple iterations of 'cookie' and 'drink', but then when i am selecting the item in data validation i only want one option for each of them since they match
That was amazing and simple. Question. If you add more to the lists is there a way that the order form will pick up the changes automatically? Or would you like me to make each column in the list a table?
If you update the named list to include a new value, the drop-down list will automatically pick that up when someone goes to select an item. However, if someone has already chosen a value from the drop-down list, even if the list itself changes, the selected value won't change.
kevin please what if you want a specific price for each item on every row base when you select the ITEM, the type , and the specific price for example when you select coffee, the specific price should come with in the next drop down
This method is easier than the XLOOKUP one but I still prefer to use XLOOKUP, mostly because then you can avoid the underscores which don't look good. And also, you can add SORT to put the lists in alphabetical order. Or maybe there is a way to also have the list in alphabetical order with this Named Range method?
Hello all & @stratvert, Will it be possible to change the drop down list with multiple selection of input in from another drop down. Ex (selecting cookies & Drink , other cell should show the value together in drop down list?
you can do it with vba. Put this code directly into the Order Form sheet code window, not in a module Private Sub Worksheet_Change(ByVal Target As Range) Dim rngList1 As Range Dim rngList2 As Range Dim rngList3 As Range Set rngList1 = Me.Range("b7") Set rngList2 = Me.Range("c7") Set rngList3 = Me.Range("d7") If Not Intersect(Target, rngList1) Is Nothing Then ' Clear List2 and List3 when List1 changes rngList2.ClearContents rngList3.ClearContents End If
If Not Intersect(Target, rngList2) Is Nothing Then ' Clear List3 when List2 changes rngList3.ClearContents End If End Sub
Mr. Kevin I had a question please... what if I had only one fruit smoothie like vanilla and I want excel to fill it automatically when I choose Smoothie and the rest still as explained.
Many thanks man. May i request one guidance At 12:09 , if i change first field from Drink to Cookie, i want depedent cells to become empty. How to achieve that?
couldn't get the (=indirect) formula to work. I restarted the entire process several times and was not able to make the dropdown lists. good video though
Kevin, what if you wanted to list that produced and displayed data using XLOOKUP. For instance if you select COOKIE is displayed the COOKIE List, If you select DRINK it displays the DRINK list. What if i wanted to display the COOKIE AND the DRINK list...could I have a BOTH or an ALL in the drop down?
@@KevinStratvert thank you Kevin! Another question, I'm struggling to hide or remove the underscores from my header category in name manager. They come randomly as you mentioned. But the only solution I've managed to "clean up" is by find and replace method on my Order Form. Is there a way to get rid of those underscores that come randomly? I've also tried the indirect substitute formula but it sort of messes things up
Good Day, when I go to data validation and input =indirect(A7) press OK, I get the error (the source currently evaluates to an error. do you want to continue? I have tried starting from a blank sheet several times, nothing changes.
what about if my list is another excel worksheet? Is there a way to also pull the price? For example, you have your item and each item has a price as a column on the list, how can the price be pulled in another column after you have selected the correct product?
Interesting, but... this only works for a small number of predefined lists. How about another video where you have a bigger number of lists, or when they come from a query and aren't known in advance...
The way I’ve gotten around this is I make the dropdown lists I want to use a table. For example tblList[Item], tblList[Cookie], tblList[Drink] For the name manager Item = tblList[Item], Cookie = tblList[Cookie], Drink = tblList[Drink] Your first dropdown data validation formula is: indirect(“tblList[Item]”) The dependent dropdown validation is: =Indirect(A1) (assuming that’s your reference cell). The benefit of the table is that you can add items later on and you don’t have to define a set range. The downfall is if your list is large enough the dropdown always starts at the bottom, best way I’ve gotten around that is in your table leave the first row blank. I doubt this answers your question about queries or that this is the most optimal, but another way I’ve gotten it to work decently for what I need. I guess in theory if your query will always spit out lists under a specific Header you could get it to work
@jeremys3951 Definitely! Many (or even most?) businesses use Excel, so learning it is recommended if you work for someone. As an Engineer, I had to learn it in order to "deep dive" into our business's Manufacturing Resource Planning (MRP II) data in order to analyze it for specific projects. If you're a student, you may very well have to use it in some college classes; I don't know what is taught in High School nowadays, so it may not be useful to you there if that's where you are now.
Thank you! But when I tried it, it let me do it with the first list but was giving an error "the source currently evaluates to an error, do you want to continue?" and then doesn't work either way. What does this mean!
hello, i just came from your “how to clean installe windows video” i went to download windows 11 from microsoft website to my portable ssd which i thought was ok my portable ssd has now been renamed ESD-USB which contained folders like boot, efi, sources I CANT SEEM TO FIND MY OTHER FILES THAT IS WORTH A LOT OF STORAGE DID IT REPLACE OR REMOVE THE FILES FROM MY PORTABLE SSD (sandisk)???? I rlly need yur help😢😢😢😢 i tried rebooting it but it wouldnt my laptop is TUf F15
It is wonderful all the way until i tried to copy & paste to follow at 10:53. The subsequent lines for dependent function is not working. My Microsoft 365 does not allow me to input Source as =Indrect(A7), and stating there is error. Only allowing =Indirect($A$7). Can't figure out why?
what would cause a popup that states "the source currently evaluates to an error" I made sure the drop down list in the first column matches the other list identically but it still won't work
For some reason I did not have to correct the underscore problem. Mine did say that an error will occur with the =indirect(A7), but I clicked anyway. It still pulled up the fruit smoothie. Also, when I was creating the list, the words "Item", "Type", "Additional" got replaced with the select choice and I don't know why. This caused me to put indirect A6 instead.
8:51 If you want to avoid adding an underscore in "Fruit_Smoothie", make it look more professional/readable, you can add a substitute function to replace the space with an underscore.
=INDIRECT(SUBSTITUTE(B7," ","_"))
Love it! Great suggestion.
Nice! I was wondering about that the minute I saw the underscore
I was going to suggest the same thing. Personally I was thinking of using CamelCase for the named range, and then removing the space within the Indirect function, but both options would accomplish the same thing.
In addition, you can indicate the name range directly in the "Name Box" instead of clicking the "Create from Selection".
Anyway, this video shows the basic steps. Great! Looking forward for the next video!
Also, why Kevin it not using Tables for the list?
I submit that rather than creating a Fruit_Smoothie that you put as the source - =indirect(substitute(b7," ","_",)) - I also suggest adding headings for each of the other drinks and create a selection with a blank cell - this way on the dropdown it will have a blank cell as (the only) value for Plain Milk or Chocolate Milk selections.
Name Range is one of the most overlooked features in Excel, yet one of the most powerful/useful of all.
This was the most simplest explanation ever,Kevin is living legen
'most simplest"? I presume you mean "simplest". "most simplest" is a double superlative.
@@Li.Siyuan nerd
KEVIN! This is sooo useful. I recently discovered data validation but had no idea I could make my life easier by naming the drop-down list. Thanks a mill.
Genius for its simplicity. I was watching your previous video regarding this subject and this last video is a lot easier.
Thanks Kevin! Brilliant way of using indirect method
Congrats on 3 million subscribers ! 🎉
Kevin, thank you very much for another great tutorial. I watched your old dependent drop down list video from 3 years ago, and I was struggling to apply the formula to multiples rows, but this method solve that problem.
Thanks again.
Great to hear!
Again and as usual absolutely brilliant! I thought I was going to climb the walls from trying to get an answer for this. So many tips again in the process so thx a mill, you're definitely my No1 go-to for anything Excel 🙂
Thank you Kevin! Brilliant explanation…👏👏👏
Simplified and well understood. Thank You for the videos!
Thanks ,God bless you
Great video. So now building off this, I want to track quantities. Not just the for this order, but for history. This person orders a regular coffee with 2 sugar cookies, so I add another column for quantity, easy enough. But want to export that information to know how many coffees or sugar cookies or whatever are ordered over the month to see if I should make more or take them off the menu. Just a thought for the next video to build off of.
Very informative! Please make a video about the dynamic dropdown list.
Thanks Kevin. I am working on a food cost software template and this is handy to know.
This is awesome. Thank you!
Thanks Kevin for the tutorial. 💪
Brilliant video!
Excellent!👍
That only works if the values on the first and second column are unique values. My case test requires that the drop down on the third column considers results from column one AND two. Additionally, column 2 results will essentially show a list of 2 values that can be applicable to items on column 1 but will generate different results in column 3.
Please more Excel for Accounting
Clicking the subscribe button from Africa, Malawi
Thank you for sharing
Thanks you saved the day😊
Happy Father's Day sir❤
This is gold!
Very nice video!
This is extremely awesome! 😊
Thanks Kevin
Thanks for the tutorial but for some reason my excel is not letting me do this and I don't know why
It may be that you have downloaded it incomplete and that is why you cannot use it correctly.
I figured I could do that, so what do you recommend?
I think it's better that you look for an office key to see if all its functions are unlocked
If it could be that it helps me, thank you very much for your suggestion.
Great information, Kevin
thanks for your sharing...appreciate it
Thank you!!
Hi- the wealth of info you provide in your videos is priceless. Thank you for sharing. Do you know if it is possible to parse full(historical not incoming) email threads ? If it is, do you offer any type of instruction that would cover that subject?
Thank you
Great video. Thanks for sharing,
great explanation - thqnk you
Glad it was helpful!
Thank you So much,,,,Amazing
Awesome...
SO, if I need training on Power BI, SQL how can I register? Also consider training on XLS forms, creating survey questionnaires in ODK etc.
Thanks its helpful
In Lotus 123 -indirect- was not necessary. I don't know why MS has simplified this function. Same with the "D" functions: Dget, Dsum, etc. They are not dynamic, just useless. Thank you K.
This video was great, thank you! I also wanted to ask if there is any possibility on how to dynamically change the name meaning that if i change the value in the first list then the values in all the follwing lists change to the first option (does not matter) in all the other lists?
That’s very helpful. Just curious, will it still work if the item list were in excel tables instead of the named ranges. Thanks 🙏🏽
No cuz the list spills. it is possible, but u have to do a lot more reference many lists to get what u want.
Yes, it will work if your lists are tables. But you will still have to select the table and give it a range or list name. You cannot use the table name.
Yes it will. I think. You have to select the table column data, when defining the name range.
This is very good if you want to keep adding options and the named range dynamically updates.
This is a great video, I'd like to make one additional comment which drove me crazy and couldnt figure it out. The Item can only be one word if you have something that consists of two words than the indirect function wont work. Id like to know and learn if there is a work around for that.
can you also make a video about facebook ads ? thank you!
Appreciate the great video as always! I've never figured out a way to adjust the font size of the list within the dropdown box, or is it even possible?
super helpful, many thanks for uploading this! do you know if it is possible to create a named list that only displays sorted and unique values when referenced in the data validation? for your example, in my first list i would have multiple iterations of 'cookie' and 'drink', but then when i am selecting the item in data validation i only want one option for each of them since they match
That was amazing and simple. Question. If you add more to the lists is there a way that the order form will pick up the changes automatically? Or would you like me to make each column in the list a table?
each list will need to be formatted as a table to be able to do this.
If you update the named list to include a new value, the drop-down list will automatically pick that up when someone goes to select an item. However, if someone has already chosen a value from the drop-down list, even if the list itself changes, the selected value won't change.
kevin please what if you want a specific price for each item on every row base when you select the ITEM, the type , and the specific price
for example when you select coffee, the specific price should come with in the next drop down
This isn't working in my Paid Microsoft Office 365 in Win 11. Ah! Found my error! Got it! Thank you so much! Awesome!
What was your error because I’m still getting an error message when trying to use the indirect function
Great video! super helpful for others but mine indirect formula is not working with me. I don't know why. :(
This method is easier than the XLOOKUP one but I still prefer to use XLOOKUP, mostly because then you can avoid the underscores which don't look good. And also, you can add SORT to put the lists in alphabetical order. Or maybe there is a way to also have the list in alphabetical order with this Named Range method?
Hello all & @stratvert,
Will it be possible to change the drop down list with multiple selection of input in from another drop down. Ex (selecting cookies & Drink , other cell should show the value together in drop down list?
Hi Kevin. Is there a way to authomatically clear the depending selections once you select a different source list?
you can do it with vba. Put this code directly into the Order Form sheet code window, not in a module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngList1 As Range
Dim rngList2 As Range
Dim rngList3 As Range
Set rngList1 = Me.Range("b7")
Set rngList2 = Me.Range("c7")
Set rngList3 = Me.Range("d7")
If Not Intersect(Target, rngList1) Is Nothing Then
' Clear List2 and List3 when List1 changes
rngList2.ClearContents
rngList3.ClearContents
End If
If Not Intersect(Target, rngList2) Is Nothing Then
' Clear List3 when List2 changes
rngList3.ClearContents
End If
End Sub
Does this work for older versions of excel without array formulas
Mr. Kevin I had a question please...
what if I had only one fruit smoothie like vanilla and I want excel to fill it automatically when I choose Smoothie and the rest still as explained.
Many thanks man.
May i request one guidance
At 12:09 , if i change first field from Drink to Cookie, i want depedent cells to become empty. How to achieve that?
couldn't get the (=indirect) formula to work. I restarted the entire process several times and was not able to make the dropdown lists. good video though
can we do this on google sheet
Kevin, what if you wanted to list that produced and displayed data using XLOOKUP. For instance if you select COOKIE is displayed the COOKIE List, If you select DRINK it displays the DRINK list. What if i wanted to display the COOKIE AND the DRINK list...could I have a BOTH or an ALL in the drop down?
thanks good , i liked , . . . how can i do this for google sheets , thanks before
🔥🔥🔥🔥🔥🔥
Like @Swane38, I would like to know why the font of the items in the drop lists is so damn small. Isn't there a way to make it bigger?
Need to expand to tables so the list can grow with increased items in the lists
this is exactly what i want but i don't want user to see the list tab. how do i do it??
Question. Can 2 different order forms refer to the same list sheet? Provided contents are similar
Yes, you can refer to a named list any number of times 👍
@@KevinStratvert thank you Kevin! Another question, I'm struggling to hide or remove the underscores from my header category in name manager. They come randomly as you mentioned. But the only solution I've managed to "clean up" is by find and replace method on my Order Form. Is there a way to get rid of those underscores that come randomly? I've also tried the indirect substitute formula but it sort of messes things up
Good Day, when I go to data validation and input =indirect(A7) press OK, I get the error (the source currently evaluates to an error. do you want to continue? I have tried starting from a blank sheet several times, nothing changes.
I am having the same issue
Could someone tell me if this works in excel web? I seem to be stuck on using the "=indirect(XX)" function in the Data Validation Tab, thanks!
what about if my list is another excel worksheet? Is there a way to also pull the price? For example, you have your item and each item has a price as a column on the list, how can the price be pulled in another column after you have selected the correct product?
Interesting, but... this only works for a small number of predefined lists. How about another video where you have a bigger number of lists, or when they come from a query and aren't known in advance...
The way I’ve gotten around this is I make the dropdown lists I want to use a table. For example tblList[Item], tblList[Cookie], tblList[Drink]
For the name manager Item = tblList[Item], Cookie = tblList[Cookie], Drink = tblList[Drink]
Your first dropdown data validation formula is: indirect(“tblList[Item]”)
The dependent dropdown validation is: =Indirect(A1) (assuming that’s your reference cell).
The benefit of the table is that you can add items later on and you don’t have to define a set range. The downfall is if your list is large enough the dropdown always starts at the bottom, best way I’ve gotten around that is in your table leave the first row blank. I doubt this answers your question about queries or that this is the most optimal, but another way I’ve gotten it to work decently for what I need. I guess in theory if your query will always spit out lists under a specific Header you could get it to work
Create Dependent Like Subscribers 👍
so i need the searchable dropdown list here , how should we made for different level?
Is excel still worth learning?
@jeremys3951 Definitely! Many (or even most?) businesses use Excel, so learning it is recommended if you work for someone. As an Engineer, I had to learn it in order to "deep dive" into our business's Manufacturing Resource Planning (MRP II) data in order to analyze it for specific projects. If you're a student, you may very well have to use it in some college classes; I don't know what is taught in High School nowadays, so it may not be useful to you there if that's where you are now.
Thank you! But when I tried it, it let me do it with the first list but was giving an error "the source currently evaluates to an error, do you want to continue?" and then doesn't work either way. What does this mean!
hello, i just came from your “how to clean installe windows video”
i went to download windows 11 from microsoft website to my portable ssd which i thought was ok
my portable ssd has now been renamed ESD-USB which contained folders like boot, efi, sources
I CANT SEEM TO FIND MY OTHER FILES THAT IS WORTH A LOT OF STORAGE DID IT REPLACE OR REMOVE THE FILES FROM MY PORTABLE SSD (sandisk)???? I rlly need yur help😢😢😢😢
i tried rebooting it but it wouldnt
my laptop is TUf F15
👍👍👍👍
i try to use the indirect function in the source box but it gives me an error
It is wonderful all the way until i tried to copy & paste to follow at 10:53. The subsequent lines for dependent function is not working. My Microsoft 365 does not allow me to input Source as =Indrect(A7), and stating there is error. Only allowing =Indirect($A$7). Can't figure out why?
what would cause a popup that states "the source currently evaluates to an error" I made sure the drop down list in the first column matches the other list identically but it still won't work
For some reason I did not have to correct the underscore problem. Mine did say that an error will occur with the =indirect(A7), but I clicked anyway. It still pulled up the fruit smoothie. Also, when I was creating the list, the words "Item", "Type", "Additional" got replaced with the select choice and I don't know why. This caused me to put indirect A6 instead.
Ah yes chocolate, my favorite fruit
Struggling to get a drop down list with items with their current prices to use
ive done this like 50 times, same name and all and still comes up invalid.
I noticed, he never replied on error a lots of users are getting....bummer
not working for me
Ahmed Naviwala
Are you an Ai?
I saw that no one is mentioning this, no offense , I think you are a really helpful person kevin but 1:12 you are not a funny person
Always fantastic