Mynda - I first used Supercalc in London in 1984 and used Excel when it started. As an investment banker I immediately understood the power of pivot tables and used them with SQL and one of our data team, as well as learning VBA myself. After working in New York, San Francisco, and Geneva, I'm retired back in London and Paris running three private companies. In my working life I've been lucky to work with some brilliant Excel people. My point in writing this lengthy and, to your subscribers, pointless comment is to say - yours is the only Excel channel I look at, even a topic like this is worth watching as I know you'll have something truly useful and interesting to say, eg I can't believe I knew nothing of UNIQUE dynamic arrays - how much time have a wasted! Agh! Thanks for producing such fabulous and diligent content. At 64 I have sent many of your videos to family and young friends starting out in finance - telling them they have to know this stuff to be efficient and have the skills so that they can own the data and the decisions. Richard
Good Morning, II'm in Canada. I discovered your UA-cam capsules recently. Knowing Excel quite well (not to say very well) (a little modesty), I have seen applications which can surely provide a different view of my personal data. Thank you for everything. I will follow you ! Have a good one !
I thought I was tripping when it cell reference changed when you had it as an absolute reference when you first selected the data. I use to always use offset to make it dynamic. This is much easier. Always a fan of your work!
lot of thanks for you ! really this is what I was missing to understand especially using the height and width in offset formula . please keep going posting such valuable videos .
Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then reference to the table's headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction.... happy to share my findings with you 😊
in the source of the data validation if you named the cells as you did you can press F3 and select the available lists. No matter on what page they are
First, thank you for all the informative info, and professional videos. I want to mark that Data Validation list will not be updated as shown in the video if the list is on another sheet (maybe a bug?).
Thanks for the information. Usually, when I use a table on another sheet I would use the indirect command for data validation. Example: =INDIRECT("ExtCategoryList[Categories]") The defined name seems a lot easier.
Indeed, the Named Range is easier, it is dynamic and does not result in performance issues that may be caused when using volatile functions like INDIRECT. If you're only using one or two INDIRECT functions then you won't notice a difference, but if you're using them a lot then they can be problematic.
3:55 That explains why my coworker struggled with dropdowns. For some reason, people are amazing at not actually using Excel tables but using regular background colors to "show" a table.
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Very timely information! Would you provide a recommendation on setting up something similar to a form for users to query a dataset, using 5 fields, to get to a unique record? I have a dataset of > 800K records that users need to find a unique record based on the 5 fields. I can use Excel 365 or Power BI for this form.
Great to hear, Harry. Please see these tutorial on search forms: www.myonlinetraininghub.com/display-all-matches-from-search-in-userform-listbox www.myonlinetraininghub.com/searching-for-data-with-user-form
Not sure what you mean by using two columns. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Definitely timely... I would welcome something describing the following: Dropdown List of Names - BUT - when selecting the Name - it actually returns a numeric value associated with the Name. Ex: I have a TBL with 2 columns - Region No & Region; I want the User to choose the Region - BUT - I need the Region No to be returned... Region No = 1 and Region = EU Dropdown shows = EU but when selected - the value for Region No = 1 is placed in the cell. I can accomplish with a multitude of steps - but I'm thinking some variation of Data Validation would work
Interesting, however you'd have to write some VBA to do this if you wanted to replace the value selected in the list with a number. Otherwise, just put it in a column beside using a lookup formula.
Mynda - many thanks! Let's hope that MS push out those updates to the wider community as soon as possible. Also, enable you to directly reference a structured Table name directly in the DV List Source dialog. That can't be an oversight - there must be a good reason why that isn't supported?
Very clear.... Can you suggest me formula for date series when date of Saturday & Sunday are automatically removed when draging that formula from cell A2 to A30 ?
Mynda, thanks for the beautiful video. Although I am registered to Beta channel and I have an Office 365 license, unfortunately I cannot use many features used after a while. Excel software developers need to solve this quickly. We use many formulas developed for test purposes in our data analyzes, but then the formulas in our work have not started to work because the access to these formulas was restricted! I have a question for data verification: Can we create more than one data verification list in a single cell?
Hi Emre, when you sign up for the beta channel it is on the condition that you cannot rely on new features to not change/be removed as they're in the testing phase. As frustrating as that may be, I think it's fair enough. I'm not sure in what scenario you'd want multiple data validation lists in a single cell. I'm not aware of any way for that to work. Perhaps what you mean is dependent data validation: ua-cam.com/video/pjLAnpBM9dk/v-deo.html
Thanks, Mynda. I really thought that you had solved my problem when you spoke about named ranges on another sheet. Alas, it doesn't seem to have helped. Nor has moving the list back to the current sheet. Problem: I want to use searching inside drop-down lists. I had it working in Excel 2019. Then it stopped working (probably because I moved the list to another sheet, or I hid it) So I upgraded to Office 365 (not MS Insider). It still doesn't work. Suggestions?
Currently searchable drop down lists are only available in Excel Online. They did release it to the desktop versions of Excel, but have removed it due to some issues that were found. We're hoping they'll be back soon, but no timeline on when, sorry.
Hi Mynda! This is a fantastic video. I am having an issue I'm hoping you've encountered. I have a bunch of data validation lists in cells in some complex worksheets. Sometimes after running some VBA to automate some updates and other functions, the dropdown lists are frozen (but only sort of). 1. I can click on the cell, the arrow becomes visible, if I click the arrow, the list is displayed, but I cannot left click to select any of the options... 2. However, I CAN use my Arrow keys to move up and down the list, then hit enter and the selection updates. 3. If I try to click a selection as normal, I get a windows sound almost like there's a dialog box open or something. I've tried using the Escape key to clear that, no luck. *If I save and re-open the file, everything works again. Have you ever run across this?
Thank you! Great review of the nuances of Lists in Data Validation, some of which had left me scratching my head from time to time. Question: when I explored employing the DefinedName approach (as a 365 user), I didn't need to add the #. Worked fine w/o it. In fact, with the # added on, I got an error msg ~"Source currently evaluates to an error. Do you want to continue". [And I'm happy to report that I still have the searchable drop down list] Thought it might be worth putting my experience on the table for others' sake. Again, thanks !
As always, this was very informative. I wonder why the data validation table update was rolled back to no longer auto update as new items are added. The spilled ranges do auto update
So when you use an alternate worksheet and a table, then you use a defined name, when you are filling in the list for data validation, type "=" and then use the F3 key to see the list of Named ranges.
Ignore Blank allows the user to enter a blank in the cell, it doesn't remove blanks from the source data list. You need to create a list without your blanks. If you're stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great Video’s! I have a question about 06.45 in this video. In this case I still need to make a list on a separate sheet and then use this as the reference in de date validation section. Would it also be possible to directly write in the date validation section>List>Source the formula, like: =unique(data[Geography]) I have tried different options but I couldn’t get it to work.
In earlier versions of Excel, If I am not wrong 2003 and before, even simple list was not allowed from another sheet for which we had to use name manager
I found in 365 with a drop down list on 1 sheet, to use it on another in a drop down I had to make the source: =DDList!C$3# otherwise the source changed the reference to C3# to C4#, etc. Do you confirm that happens to you as well? I didn't think it should change.
Mynda, thanks! if I want show two column showing in the dropdown but only return information of one value, e.g. have Product ID /Name showing in the drop down, once select only return the Product ID. is that possible? thanks
In excel, i have a cell b6 in sheet 1(main). it has a data validation from a drop down list in sheet 2 in column a2-100. i wish to get a function which can allow me to write a new value in main which is not in the original drop down list but gets updated in the dropdown list in sheet 2 and auto sorts alphabetically. Thanks.
I have noticed that if I change a one of the items in the data validation list, example Utility to Utilities, it only changes it in that list but if I have already categorised all my product and applied a „category” from the drop down section prior to updating the name, it doesn’t change the name on my categories list and leaves the name as it was before. I also set up DV „stop” if a category does not exist in the DV list but it doesn’t tell me there is an issue. Is there a way to fix this?
What you describe is a limitation because DV can only check upon you editing the cell. You can use Find & Replace to change the other cells to 'Utilities'.
There will be a way, but you probably need an intermediary step. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
The formula is not correct. You can try inserting the formula in a cell to troubleshoot the problem. Once it's working in a cell, you can move it to the data validation list. If you're still stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
You can see my Excel for Beginners playlist here: ua-cam.com/video/MnGIaqpqLTU/v-deo.html And my comprehensive courses here: www.myonlinetraininghub.com/
I have an Excel sheet that contains a linelist of measles cases , the province from which it was reported and the district from which the case was reported. Column A unique identity number of a case, Column B- province of the reported case, Column C -district of the reported case. This Excel sheet is sent to me every week as new cases are reported. Another sheet contains a list of all the provinces in column A and list of the districts within each province in column B. Is there way to create a dynamic table or list that shows districts yet to report a case for each province such that as more results are receive the list automatically updates or reduces
What were the chances of that??? I was using a spreadsheet (I do everyday!) but the data in the list wasn't available in the drop down so I had to do a bit of research (google) to sort it out. However, now I can learn more about it and properly :D
I had a date with product Group and product name I want to populate by selecting a product group and below list of product name should appear Can any one help me on this
If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Good afternoon Sir, How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours. example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir. 2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir. In CCC to be completed time also same formula given sir. Can u please help me with the formula sir?
You're missing out on my quick tips. They're also on Facebook, Twitter, Instagram, TikTok and Pinterest. You can find our social medial links at the bottom of this page: www.myonlinetraininghub.com/links.htm
One the best "dynamic drop-down list" tutorials out there on UA-cam, currently. thanks ma'am.
Wow, thank you!
That part of using unique and sort is amazing for those with office 2021or Microsoft 365. I always get something new from your tutorials..big thanks
Great to hear, Jon!
Mynda - I first used Supercalc in London in 1984 and used Excel when it started. As an investment banker I immediately understood the power of pivot tables and used them with SQL and one of our data team, as well as learning VBA myself. After working in New York, San Francisco, and Geneva, I'm retired back in London and Paris running three private companies. In my working life I've been lucky to work with some brilliant Excel people.
My point in writing this lengthy and, to your subscribers, pointless comment is to say - yours is the only Excel channel I look at, even a topic like this is worth watching as I know you'll have something truly useful and interesting to say, eg I can't believe I knew nothing of UNIQUE dynamic arrays - how much time have a wasted! Agh!
Thanks for producing such fabulous and diligent content.
At 64 I have sent many of your videos to family and young friends starting out in finance - telling them they have to know this stuff to be efficient and have the skills so that they can own the data and the decisions.
Richard
Thanks so much for your kind words and support! Don't feel too bad about dynamic arrays. They are relatively new 😉
One of the tutorials on drop down lists. You answered my question about the data source being on a different sheet. Thank you so much.
Glad it was helpful!
Good Morning,
II'm in Canada. I discovered your UA-cam capsules recently. Knowing Excel quite well (not to say very well) (a little modesty), I have seen applications which can surely provide a different view of my personal data. Thank you for everything.
I will follow you !
Have a good one !
Great to hear you found my videos helpful 🙏😊
Learn a new thing again.. Thanks for uploads..
Glad to hear that 😊
I thought I was tripping when it cell reference changed when you had it as an absolute reference when you first selected the data. I use to always use offset to make it dynamic. This is much easier. Always a fan of your work!
Glad it was helpful, Kyle!
lot of thanks for you ! really this is what I was missing to understand especially using the height and width in offset formula . please keep going posting such valuable videos .
Great to hear!
Thanks for your great validation updates 🙏❤️👍😍
Glad you like them!
in the other hand, i adore your accent. i always love british accent
Thank you 😊
Awesome..
Simple and easy to follow..
Thank you so much..
Thanks, Mimi!
Again, strangely, just what I particularly needed to know.
I have mind reading powers 😉
MUCH better than other videos out there.
Glad you found it helpful 😊
Excellent, nice tips! Thanks Mynda!
Thanks so much, Luciano!
Thanks video explaining various options for drop down lists nicely.
Glad it was helpful!
Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then reference to the table's headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction....
happy to share my findings with you 😊
Thank you! Sure, that'd be great if you're able to email it to me as I won't see follow up replies to this comment: website at MyOnlineTrainingHub.com
in the source of the data validation if you named the cells as you did you can press F3 and select the available lists. No matter on what page they are
Yes! I love F3. It's a great shortcut. Thanks for mentioning it here.
Wish I found this video sooner! Very clear & concise. Thank you so much. Liked & subscribed
Glad it was helpful, Chris!
Thank you. I knew I could do this and couldn't work out why I was failing yesterday (I have the source data in different tab).
Glad I could help!
Really Helpful it became useful to me for Analysing my Personal Budget more convinietly.👍👍😀
Great to hear 😊🙏
I found this tutorial very useful and will share with my team. Thank you !
Great to hear! Thanks for sharing 😊
First, thank you for all the informative info, and professional videos.
I want to mark that Data Validation list will not be updated as shown in the video if the list is on another sheet (maybe a bug?).
Oops... didn't watch the video to its end to see that you mentioned this issue.
Thorough as always.
Thank you a lot, that was really valuable, I like your videos, they are straightforward and detailed
That's awesome to hear, Malika!
Thanks for the information. Usually, when I use a table on another sheet I would use the indirect command for data validation. Example:
=INDIRECT("ExtCategoryList[Categories]")
The defined name seems a lot easier.
I also use the INDIRECT method. Named range would be easier, but will it be dynamic?
Indeed, the Named Range is easier, it is dynamic and does not result in performance issues that may be caused when using volatile functions like INDIRECT. If you're only using one or two INDIRECT functions then you won't notice a difference, but if you're using them a lot then they can be problematic.
Brilliant!Thank you, it did the trick
Glad it helped! 😊
Yes, it is helpful for a 255 column mail merge source.😮
THis was exactly what I was looking for, thank you.
Glad to hear it!
So much easier creating good dynamic validation now, no need for offset formulas. You can also wrap your unique formula in the sort function as well.
Indeed 😊
Outstanding! Best regards from Limón, Costa Rica!
Thank you very much, Jerry!
Thank you for sharing your expertise.
My pleasure, Elmer!
3:55 That explains why my coworker struggled with dropdowns. For some reason, people are amazing at not actually using Excel tables but using regular background colors to "show" a table.
Glad it was helpful 😊
Thanks Mynda! Great update for me on the latest.😁
You are most welcome, David!
Great video. You use the list in a column. Is it possible to use the same list in different cells that they are not in the same column?
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Always inspiring !
So glad! 🙏
Short and Informative as usual
Glad you think so!
Very timely information! Would you provide a recommendation on setting up something similar to a form for users to query a dataset, using 5 fields, to get to a unique record? I have a dataset of > 800K records that users need to find a unique record based on the 5 fields. I can use Excel 365 or Power BI for this form.
Great to hear, Harry. Please see these tutorial on search forms:
www.myonlinetraininghub.com/display-all-matches-from-search-in-userform-listbox
www.myonlinetraininghub.com/searching-for-data-with-user-form
Perfect. Couldnt get my list to update with added items on another sheet and define it with a name fixed that. Thanks!
So glad I could help.
Excellent video Mynda!
Cheers, Chris!
Another thanks from a very satisfied follower! Is there a way to bypass Excel on using two columns to create a drop down list? And thanks!
Not sure what you mean by using two columns. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Definitely timely...
I would welcome something describing the following:
Dropdown List of Names - BUT - when selecting the Name - it actually returns a numeric value associated with the Name.
Ex: I have a TBL with 2 columns - Region No & Region;
I want the User to choose the Region - BUT - I need the Region No to be returned...
Region No = 1 and Region = EU
Dropdown shows = EU but when selected - the value for Region No = 1 is placed in the cell.
I can accomplish with a multitude of steps - but I'm thinking some variation of Data Validation would work
Interesting, however you'd have to write some VBA to do this if you wanted to replace the value selected in the list with a number. Otherwise, just put it in a column beside using a lookup formula.
Mynda - many thanks! Let's hope that MS push out those updates to the wider community as soon as possible. Also, enable you to directly reference a structured Table name directly in the DV List Source dialog. That can't be an oversight - there must be a good reason why that isn't supported?
I'm not holding my breath on Microsoft making structured references recognised in all dialog boxes, but we MVPs keep asking 😉
Very clear.... Can you suggest me formula for date series when date of Saturday & Sunday are automatically removed when draging that formula from cell A2 to A30 ?
See this tutorial: ua-cam.com/video/UviDXEB0ePo/v-deo.html
Hi Mynda!Super Helpful Tutorial..Thank You :)
Great to hear, Darryl!
thank you for teaching in love
It's my pleasure 😊
Mynda, thanks for the beautiful video.
Although I am registered to Beta channel and I have an Office 365 license, unfortunately I cannot use many features used after a while.
Excel software developers need to solve this quickly. We use many formulas developed for test purposes in our data analyzes, but then the formulas in our work have not started to work because the access to these formulas was restricted!
I have a question for data verification:
Can we create more than one data verification list in a single cell?
Hi Emre, when you sign up for the beta channel it is on the condition that you cannot rely on new features to not change/be removed as they're in the testing phase. As frustrating as that may be, I think it's fair enough. I'm not sure in what scenario you'd want multiple data validation lists in a single cell. I'm not aware of any way for that to work. Perhaps what you mean is dependent data validation: ua-cam.com/video/pjLAnpBM9dk/v-deo.html
Thanks, Mynda. I really thought that you had solved my problem when you spoke about named ranges on another sheet. Alas, it doesn't seem to have helped. Nor has moving the list back to the current sheet.
Problem: I want to use searching inside drop-down lists. I had it working in Excel 2019. Then it stopped working (probably because I moved the list to another sheet, or I hid it) So I upgraded to Office 365 (not MS Insider). It still doesn't work. Suggestions?
Currently searchable drop down lists are only available in Excel Online. They did release it to the desktop versions of Excel, but have removed it due to some issues that were found. We're hoping they'll be back soon, but no timeline on when, sorry.
Hi Mynda! This is a fantastic video. I am having an issue I'm hoping you've encountered. I have a bunch of data validation lists in cells in some complex worksheets.
Sometimes after running some VBA to automate some updates and other functions, the dropdown lists are frozen (but only sort of).
1. I can click on the cell, the arrow becomes visible, if I click the arrow, the list is displayed, but I cannot left click to select any of the options...
2. However, I CAN use my Arrow keys to move up and down the list, then hit enter and the selection updates.
3. If I try to click a selection as normal, I get a windows sound almost like there's a dialog box open or something. I've tried using the Escape key to clear that, no luck.
*If I save and re-open the file, everything works again.
Have you ever run across this?
Glad you liked the video 🙏 never experienced that issue before, sorry.
Do you have a video on how to connect multiple drop down list? Thank you
Here you go: ua-cam.com/video/pjLAnpBM9dk/v-deo.html
very helpful video Thank you
Great to hear!
Thank you! Great review of the nuances of Lists in Data Validation, some of which had left me scratching my head from time to time.
Question: when I explored employing the DefinedName approach (as a 365 user), I didn't need to add the #. Worked fine w/o it. In fact, with the # added on, I got an error msg ~"Source currently evaluates to an error. Do you want to continue". [And I'm happy to report that I still have the searchable drop down list]
Thought it might be worth putting my experience on the table for others' sake.
Again, thanks !
Hi Bill, I can't reproduce that. Would you mind sending me your example file via email so I can see what you did? website at MyOnlineTrainingHub.com
@@MyOnlineTrainingHub Happy to provide file and pics. Stay tuned....
As always, this was very informative.
I wonder why the data validation table update was rolled back to no longer auto update as new items are added.
The spilled ranges do auto update
Glad you liked it, Patrick! I suspect the table on another sheet auto-updating introduced some bugs so it was rolled back until they iron them out.
So when you use an alternate worksheet and a table, then you use a defined name, when you are filling in the list for data validation, type "=" and then use the F3 key to see the list of Named ranges.
Yes, but if you're using a Table, then you don't need to define a name unless that Table is on the same sheet.
Lots of thanks for you ! but how to get rid of blanks while ticking on the "ignore blank" Data validation box?
Ignore Blank allows the user to enter a blank in the cell, it doesn't remove blanks from the source data list. You need to create a list without your blanks. If you're stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you so much!
My pleasure 😊
Very good video.
Glad you liked it 😊
Great Video’s! I have a question about 06.45 in this video. In this case I still need to make a list on a separate sheet and then use this as the reference in de date validation section. Would it also be possible to directly write in the date validation section>List>Source the formula, like: =unique(data[Geography]) I have tried different options but I couldn’t get it to work.
You can’t write a formula in the data validation source field.
@@MyOnlineTrainingHub Thank you for your prompt reply!
Can you have Two Columns of Data to select. E.G. Tour Name + Tour Date ?
In earlier versions of Excel, If I am not wrong 2003 and before, even simple list was not allowed from another sheet for which we had to use name manager
Which is probably why Tables didn't from the outset, because Tables are the new improved Lists.
U rock girl.. !!!!!
Do You know Mynda !!!
I Love You !
Aaah, Off Course ! You know it !
Kanwaljit
😊 glad you enjoy my tutorials.
Thanks. Can we updates the information of the data validation drop list ? How?
I cover this in the video. If you prefer a written tutorial, please see the instructions here: www.myonlinetraininghub.com/excel-drop-down-lists
@@MyOnlineTrainingHub
Thanks, i will see the video again and download the written tutorials.
Can you make drop-down lists from data models?
If you load the data to the worksheet via a PivotTable or cube function. You can't connect a data validation list directly to the data model.
I found in 365 with a drop down list on 1 sheet, to use it on another in a drop down I had to make the source: =DDList!C$3# otherwise the source changed the reference to C3# to C4#, etc. Do you confirm that happens to you as well? I didn't think it should change.
Whenever you reference a cell or range on another sheet you must qualify it with the sheet name first.
@@MyOnlineTrainingHub I did use the sheet name: DDList as in =DDList!C$3# I thought =DDList!C3# should be ok.
Mynda, thanks! if I want show two column showing in the dropdown but only return information of one value, e.g. have Product ID /Name showing in the drop down, once select only return the Product ID. is that possible? thanks
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
In excel, i have a cell b6 in sheet 1(main). it has a data validation from a drop down list in sheet 2 in column a2-100. i wish to get a function which can allow me to write a new value in main which is not in the original drop down list but gets updated in the dropdown list in sheet 2 and auto sorts alphabetically. Thanks.
You can use the dynamic array method as shown at 6:44 in the video.
Really useful as it's really annoying when lists don't update when your data is on a different tab.
Great to hear, Mark!
Thanks
Welcome 😊
I have noticed that if I change a one of the items in the data validation list, example Utility to Utilities, it only changes it in that list but if I have already categorised all my product and applied a „category” from the drop down section prior to updating the name, it doesn’t change the name on my categories list and leaves the name as it was before. I also set up DV „stop” if a category does not exist in the DV list but it doesn’t tell me there is an issue. Is there a way to fix this?
What you describe is a limitation because DV can only check upon you editing the cell. You can use Find & Replace to change the other cells to 'Utilities'.
Thanks 👍
You're welcome 😊
Can I make a drop down list pulling data from different tables? I am finding an error when I do this as I am unable to use union
There will be a way, but you probably need an intermediary step. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for the video, but it didn’t work on dates data, i don’t know why, so if anyone knows please inform me.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I encountered error saying "The Source currently evaluates to an error." How do I solve this? Appreciate your help. Thank you in advance.
The formula is not correct. You can try inserting the formula in a cell to troubleshoot the problem. Once it's working in a cell, you can move it to the data validation list. If you're still stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I really like your lessons but being a bit green on excel, i find myself floating most of the times.
How can i get introductory lessons?
You can see my Excel for Beginners playlist here: ua-cam.com/video/MnGIaqpqLTU/v-deo.html
And my comprehensive courses here: www.myonlinetraininghub.com/
@@MyOnlineTrainingHub Thanks a lot for your kindness.
I have an Excel sheet that contains a linelist of measles cases , the province from which it was reported and the district from which the case was reported. Column A unique identity number of a case, Column B- province of the reported case, Column C -district of the reported case. This Excel sheet is sent to me every week as new cases are reported. Another sheet contains a list of all the provinces in column A and list of the districts within each province in column B. Is there way to create a dynamic table or list that shows districts yet to report a case for each province such that as more results are receive the list automatically updates or reduces
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello blogger, thank you for your sharing, I watched a few videos you made, which are very lively and interesting, how can I contact you?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Are you interested in cooperation?
What were the chances of that??? I was using a spreadsheet (I do everyday!) but the data in the list wasn't available in the drop down so I had to do a bit of research (google) to sort it out. However, now I can learn more about it and properly :D
So pleased it will be useful 😊
How do you make the font in the drop-down list larger ?
The only way is to zoom in on the page.
i wanna ask, why there are not command sort and unique in my excel? i use 2019 microsoft office
These are new functions only available in Excel 2021 onwards.
Madam, How to remove the blank from the slicer.. Kindly advise me
You can edit the settings to 'hide items with no data'. If it still appears, then you have to remove the blanks in the dataset.
@@MyOnlineTrainingHub thanks
When i update the table, the drop down list is not getting updated , even though the source list is formatted as table
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I had a date with product Group and product name I want to populate by selecting a product group and below list of product name should appear Can any one help me on this
If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub
Good afternoon Sir,
How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours.
example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir.
2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir.
In CCC to be completed time also same formula given sir.
Can u please help me with the formula sir?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I really don't understand why such a usefull function would be used online but then excluded in the desktop version..
It’s available in the desktop version now too if you have Microsoft 365.
Drop list on google is better. I hope excel can update 😌
It’s getting there!
at 6:10 just use F3 on keyboard as shortcut.
Yep. If you follow me on LinkedIn you'll see I have a quick tip coming out on the F3 key on 28th June 😉
@@MyOnlineTrainingHub im not using linkedin but im following u here. youre my favorite excel girl.
You're missing out on my quick tips. They're also on Facebook, Twitter, Instagram, TikTok and Pinterest. You can find our social medial links at the bottom of this page: www.myonlinetraininghub.com/links.htm