Leila you make me love EXCEL day after day I never tired of learning with you here and in udemy .although I am not good in English but I get what you say and what you explain
Brilliant - as always when Leila delivers work. I use validations frequently and still learned a ton watching your video. Thank you for all your hard work - you really are making a difference!
I was looking for an hour now to the solution with the indirect-function you show at 6:50 - thank you so much! I was getting so mad... This doesn't seem to be a problem in other tutorials I saw, it always worked without it so the content creator didn't (have to) show this function.
This really save my stress! Please I would like to know how to search when you have a very long list validated or how to use few letters to get a validated word in the list
Hi Leila, It's always wonderful to watch your videos. And as you rightly said; it's really useful when the spreadsheet is going to be used by many users. Using data validation, one can easily take care of the obvious mistakes that users could make. Lovely.
Nice video, all information u share in this video is nice. I most of time use data validation when I want to share my file with other people who don't put wrong information. One new learning from this video is the indirect function use in data validation for pic data from a table. Thanks u mam.
May I just say how wonderful it is to find a content creator who takes their time explaining and referencing each step! From the bottom of my heart ❤ THANK YOU! 😊 💓
@@LeilaGharani I need to work with Matlab, and I want to make reports with excel, have you got some advice. I need to make reports of 15 million rows; I know that I have to use Power Pivot. Will you give me some videos to watch?. I need to work with two software. I guess that you know what I mean. Thanks in advance
Thanks for this. I've been trying to use data validation on cells that have formulas in them. But it seems like it doesn't work even on the latest excel versions? Just wondering if you already have a video up on this. For example, cell A2 contains formula =B2+C2. If i set the validation on cell A2 saying that the sum shouldn't be greater than 10, any number could still be put into cells B2 and C2, and A2's error message won't be triggered even if their sum is greater than 10...
Very good video. How can I make my drop down list to a autocomplete one? I want to type one or two first letter and see available items in the list starting with these letter.
This is great 👌 But Just wanted to check with you if there's a way we can give similar multiple drop down list more than one sheet single excel sheet for a quick check of some important numbers or info. Thanks, Eagerly waiting for your favorable response Best, Aamir I regularly watch your videos for sharpening my excel skills soon shall enroll your premium program for VBA and advanced Excel
Hi, I like most of your video, I have questions when I create a vendor list and sometime each vendor have so many location contact information do to data validation is greater only use for small data, but I have huge list lot of vendor and each vendor might have 15 contact information base on location. Would you let me know how to do that for the huge list? Tahnk yoiu
Thank you Leila. I just wanted to mention here under input messages, If you ONLY give a title and leave the input message blank, the title won't show on the sheet. If you want only the title to show, for example: "Select from the list" then you should at least add a space to the Input Message. Is there any other way you can do this?
Is there a way to select items from a drop down from a dynamic filtered list, but validate against a main list? For example, I want to drop down against a filtered supply order list with pending items, but when the items from the list is cleared, it will not be in the filtered list. How can I get to check from the main list while restricting options to the filtered list?
Hi Leila, I really don't get the reason why when creating a list the output is the whole list. So, I would like to choose between the elements of the list and not the whole list. Can you pls explain me where it possibly could be my mistake
I have a query in data validation - I have more than 50 drop down list, How can i select by entering first 2 or 3 character and able to find the dropdown list shorter instead of all 50 dropdown list. Hope you can understand my query. if required, i can explain more in details
Hi Leila, how about data validation where you put single input bar where if it equals or contains in any of the list will make the cell on the list turn green, and if not equals to any of the list will become red, is this possible
Hello Leila, thanks for the video. A remark and a question: You don't have to use INDIRECT when working with an ExcelTable. you can just select the column, just hoover the cursor over the columheader until you get a bold black arrow down. then select. in the name manager you see a normal reference (absolute) but this will always be updated. Then the question: you can also later add validation rules over existing data. You don't get an error if your entry was not correct. If you want to see the invalid data you can use: validation circles. that works fine. But now my question: is there a way to avoid the validation circles in your column for project start date?? Then over time all these dates are before the current date, but at the moment of entry they were correct.
Hi Bart :) yes - you mean through name manager right? Regarding validation circles - yes, they are helpful to pinpoint wrong inputs. With regard to a validation expiring - you have the ability to clear the circles. Is this what you mean?
Hello Leila, No I did not mean to use the name manager, you can just select a column with the black arrow above the header of a column. Even our friend Mike Girvin does not use this. I think it is just a matter of habit. But I think "my" method is faster. Forget about the validation circles, this question was too theoretical. By the way: you can drive people to madness if you enter a very strange validation rule and later apply the validation filter. If "they" don't know about data validation, there is no way to remove the circles....:)
I used the separator for the static names but it did not appear as a list, the divisions appeared on in a single line with commas separating them. What could be the reason for this?
Very helpful presentation. See minute 15:41, Name table appearance =TableDiv[Division] impossible. It appears only address like A1:A25. What is happening?
If you already have a table, but you don't see the table references, maybe you have the option turned off. Go to File, Options, Formula and make sure there is tick mark for using table references.
@Leila Gharani -- At 14:16, you were explaining about pasting the values in your data validation to apply to the cells below. When I do it, it doesn't work. :( Why is that? For every selection that I choose in the drop-down list created, it removes the top entry from that list when I go to the next cell below. :(
Question- is there any way that I can automate entering "input message" based on a list of values? e.g. I have three columns 'Field Name', 'Field Value', and 'Field Definition'. I want to show the definition in the 'Field Name' Column itself as a "Input Message" when the cell is selected. Entering one by one in the data validation tab is a time-consuming thing. Any way/formula I can use to get all the definition in data validation's "input messages" for each field in the list?
I need to create a dropdown list contains names, and then when you select a name it'll display a table or list of names that stored under another tab or sheet
hey Leila Gharani really thanks for this wonderful data validation i will use more of data validation going forward keep up the great work of uploading more videos and i would like to know in the company code when i attemped it is not allowed to type or input text as in abc123 then i press enter it shows error which should have not come why so i have selected option text lenghth and then equals to option. Further in your video you have inputed S2010 which is accepted by excel but not in my case excel it is not accepting alphabets but only 5 numbers are accepted by excel like this 12346. kindly help..
You're welcome Dhiraj - have you selected option text length or whole number? For the whole number option, it will only accept values but for text length it should accept any character input. So in your example you can put text length = 6 and then you should be able to input abc123. If you put text length > 4, then you can input S2010 or also abc123.
Hi Leila, I'm loving your videos as I've been setting aside time to upgrade my Excel skills. At 13:25 of this video, you mentioned to drop a note if we are looking to learn how to use the custom function and the corresponding formula for the "Company code." Can you direct me to the video or the instructions for this? Thank you!
Hello Sir, Can i get a help? Can i email separate excel result sheet to 800 student in picture format automatically from excel? Currently i send 800 different email manually to 800 different email address. Is there any short cut that will do it automatically?
So for people that don't know, and maybe having some trouble with the ( , )part, try and use semicolon ( ; ) instead, Because Excel has different settings depending on the language installation among other things. So if , doesn't work anywhere in any formula, try ; :)
Hi Leila, always a pleasure when you explain thing s so clearly. I am a community health worker and I use validation for collecting data. Your videos help to clarify and organize my data. 👍😊💖
Another wonderful video, Leila. I use validation for dropdowns all the time, using the Indirect/table method. I teach Excel in a US public library and recommend your channel and Udemy courses to all my students.
The harassment on this channel is gross. If you creeps have anything to say about her looks or are compelled to make sexual compliments, keep it to yourself, sickos!
Thank You Leila I would Like To Understand How is the Data Validation Works for Multiple Columns.For instance Next Column Data Depend on One Selection from the List of the Previous Column for example The First Column Consist of Colors Data and I Would Like When one is Selecting Yellow the Second Column will Prompt to give only Contents of Yellow e.t.c
I wish I had learned this when I was younger. I am 55 and working with time sheets and this could really help me. Your information is helping me understand slowly I will try to lean as much as I can. 😊 Thank you.
Hi Leila , Please Help .... I have date format 02-03-2022 (want to change cell format date to text) 1. if i change manually by text to column, i get 02-03-2022 (DD-MM-YYYY) 2. If i use text to column by vba code, i get 2-3-2022 (D-M-YYYY) Please help, how to fix this.......
I wonder how I could do an actually very simple thing: a drop down list with a sequence of number. I thought the solution would be: Data Validation -> List -> Source : =SEQUENCE(....) That didn't work, so I changed Source to : =TEXTJOIN(", ",TRUE,SEQUENCE(....)) This does not work either. If I enter the same sequence manually in a cell it does not work either, but if I enter that sequence manually under 'Source' it works. Unbelievable! Please Microsoft make it user friendly. Such a basic thing, one should not need to be an Excel expert.
Can you tell me what i'm doing wrong here? I have another sheet with the name "All" and i'm trying to find the word "Hello" (for example) within all of column B of that Sheet and have the rest of the formula locate/use the ROW that that word is found on (which can vary and is very random) and append that ROW with the known E column and then covert what it sees in that cell to a Value. So, Column A contains the word and Column E contains a number on the same Row i need recorded elsewhere in another cell that has the formula below.. Here is what i have so far, but it gives me an error of #Name? =VALUE(All!E(MATCH("Hello",All!B:B,0))) So in the formula i have above, I put ALL!E to indicate the known sheet and column.. but since i dont know the row number, as that can vary I put in a Match formula to find that row automatically, which works by itself. Using just "=MATCH("Hello",All!B:B,0)" gives me the correct row when i use that formula by itself, so i know that is working... but i can't figure out how to append that with the sheet and column "All!E" I figured I could just squeeze the Match formula right behind the E.. but simply plugging that in doesn't seem to work for me.
A quick question regarding 3:30 -> change the name of a list entry: How can I command Excel to update/reflect that change automatically if the changed entry was chosen before? E.g. you are changing GAME into GAMES. Provided, the GAME division was already selected, I'd logically expect that the validation list shows automatically the new value GAMES without selecting it once again from the drop down. Is it possible? Maybe with VBA? Thanks in advance!
Hi Leila. I have one query and I know you must have a way out for it. I am a sales executive and I use VLOOKUP and HLOOKUP formulas for my daily Reporting and Funnel. But I don't know how will I Summaries the total of my Won account or say business I did through out the month as the format is vertical and at the end I want to have the answer of Won cases with VLOOKUP and HLOOKUP?
Hye Leila.. It's great to found your channel & I subscribed already.. I'm currently learning how to use data validation in excel(I even don't know what's call till found your video). It' really a big help. I'm stuck with two things. 1) On your example on the Tab Data validation Basics, Column Division, is that possible to enter one letter so it's much easier to select which data we looking for. Example, if I type keyword "B", only data with the header "B" letter on the list will appear, instead scrolling all the data on the list. It will so much help if there's a lot of data on the list. 2) Is it possible If I want to make 2nd column auto update when I select data for the 1st column? On excel, I already create a table contain, Name, Tel No, Address & email.. so, on the data validation part, when I select a particular name which is at the first column , automatically tel. no also will appear on the next column. Hopefully you'll understand my question. Thank you
Lela, what if I wanted to make a data validation list where things are priorities 1-10 and if I change a row to say 'priority 2', everything below 2 will be renumbered to 3,4 ,5.. . i.e. you can only have a single row with a priority 2 column.
Wonderful! Ive used data validation when creating Adobe forms, but didn't know how to do it in excel until today. Thank you. I was searching high and low date validation and you provided it easily along with other helpful nuggets. How do you do data validation to require dollar amounts? I see decimals, but I would like the dollar sign included.
Halo Mrs. Leila, would you explain a bit more? in my case my i can select more than 4000 data dropdownlist, there is a way for shorcut copy paste validation? thank you..
Hello Leila, What about using Validation on Table records? instead of copying and pasting validations, how can the validations carry over when inserting new records? I noticed the formulas and formats do carry over to the new inserted records but the Validations Don't. Is there a way to resolve this? As always, thanks for all your excellent tutorials!
I used the data validation to my file then when i open back my laptop, the set up is no longer available to my file it only remains a text that i was selected. What happen, maam? Thank you.
Hello sir... I am bit in trouble please help me I have two spread sheets one is called stocks and another is calculator In a stock there is three columns category, subcategories and price And in a calculator i enter category value by list from stock sheet same as subcategories but i can't able to get a price in calculator please help me For ex category is fruit and subcategory is apple and the price is 100 So i can get a fruit and apple by list in calculator page how i get a price of it in price row Please reply if you know Looking forward to hear from you..
Is there a way to autocomplete? I have a list of countries users can choose of. I found two tutorials, but one requires KUTools, the other one a combobox and some coding...
Leila you are one of the best Excel teacher in RUclips.
You are still my number one source for Excel queries. You never let me down. Thank you!
Great to hear, Leo!
Это то что я хотел знать об этой фунции.
Коротко и по делу.
Огромное спасибо Вам.
С Уважением.
My pleasure. Glad to help.
Thanks Leila. I've gained a lot of insights from lecture on data validation on audit findings relevant departments.
Leila you make me love EXCEL day after day
I never tired of learning with you here and in udemy .although I am not good in English but I get what you say and what you explain
Brilliant - as always when Leila delivers work. I use validations frequently and still learned a ton watching your video. Thank you for all your hard work - you really are making a difference!
ruclips.net/channel/UCtNr753qbiVw61oY_ow_aPw
New channel need your valuable support 🙏❣️😇😍
Your explanation is crystal clear
thanks a lot. you are one of the best Excel expert in my opinion .
I was looking for an hour now to the solution with the indirect-function you show at 6:50 - thank you so much! I was getting so mad... This doesn't seem to be a problem in other tutorials I saw, it always worked without it so the content creator didn't (have to) show this function.
Love your explanations
God know how long I've look for this answer! I love you!
This really save my stress! Please I would like to know how to search when you have a very long list validated or how to use few letters to get a validated word in the list
So helpful (and timely)! God bless you.
Very informative content and I especially like the way you deliver it! Thank you, Leila!
Congratulations on 1M by the way!
Superb.it will be of great help for my current job
I just love you Leila! Thanks for sharing such valuable information and in such a simplex manner. Kudos!
One of the best channels I ever subscribed!
Thanks for the kind words. It's great to have you here.
This video has helped me understand lists and data validation
I'm glad the tutorial is helpful Shaun.
Alt + D + L is a great shortcut for data validation window also 👍🏼
Wonderful video! Video on how to customize data validation would be fabulous.
Thank you Monica - Ok sure. Will add it in about a month's time :)
The video is clear and makes things easy ! thank you so much 🙏
Hi Leila,
It's always wonderful to watch your videos. And as you rightly said; it's really useful when the spreadsheet is going to be used by many users. Using data validation, one can easily take care of the obvious mistakes that users could make.
Lovely.
Thank you Sachin for dropping by :)
Great video, thanks!
Nice video, all information u share in this video is nice. I most of time use data validation when I want to share my file with other people who don't put wrong information.
One new learning from this video is the indirect function use in data validation for pic data from a table.
Thanks u mam.
Agree - data validation is great to help minimize mistakes specially when sharing workbooks. I'm happy you found something new here :)
Excellent, thank you! Very informative.
Can you please provide a video on List - when we use formulas like =If(B1="WoW", then pull the list from X table else from Y table).
Thank u Leila, u are amazing 😊
May I just say how wonderful it is to find a content creator who takes their time explaining and referencing each step! From the bottom of my heart ❤ THANK YOU! 😊 💓
pretty useful and well explained, thanks
Glad you liked it.
@@LeilaGharani I need to work with Matlab, and I want to make reports with excel, have you got some advice. I need to make reports of 15 million rows; I know that I have to use Power Pivot. Will you give me some videos to watch?. I need to work with two software. I guess that you know what I mean.
Thanks in advance
Great content, concise and to the point!!
Big Thank You ! 🙏
Thank you so much for this video! I learned this through a coworker, but then couldn't remember "how"...
ruclips.net/channel/UCtNr753qbiVw61oY_ow_aPw
New channel need your valuable support 🙏❣️😇😍
Thanks for this. I've been trying to use data validation on cells that have formulas in them. But it seems like it doesn't work even on the latest excel versions? Just wondering if you already have a video up on this.
For example, cell A2 contains formula =B2+C2. If i set the validation on cell A2 saying that the sum shouldn't be greater than 10, any number could still be put into cells B2 and C2, and A2's error message won't be triggered even if their sum is greater than 10...
Very good video.
How can I make my drop down list to a autocomplete one? I want to type one or two first letter and see available items in the list starting with these letter.
Madam you are amazing
Thanks a lot!!
You're very welcome Esan!
This is great 👌
But Just wanted to check with you if there's a way we can give similar multiple drop down list more than one sheet single excel sheet for a quick check of some important numbers or info.
Thanks,
Eagerly waiting for your favorable response
Best,
Aamir
I regularly watch your videos for sharpening my excel skills soon shall enroll your premium program for VBA and advanced Excel
That was helpful !
Thank you
Thank you Leila mam very useful video it's improve confidence & interest in work. Awesome
i love your insght.....got u!!!
I'm happy to hear that Samuel.
Thanks alot
Hi Leila great video, is there any way you can do this with Google Sheets?
Hi, I like most of your video, I have questions when I create a vendor list and sometime each vendor have so many location contact information do to data validation is greater only use for small data, but I have huge list lot of vendor and each vendor might have 15 contact information base on location. Would you let me know how to do that for the huge list? Tahnk yoiu
Another great short lecture. Thanks!
Really amazing
Thanks alot
You're very welcome. Glad you like it.
Thank you Leila.
I just wanted to mention here under input messages, If you ONLY give a title and leave the input message blank, the title won't show on the sheet. If you want only the title to show, for example: "Select from the list" then you should at least add a space to the Input Message.
Is there any other way you can do this?
Thanks Leila so much for such an amazing video as usual
You're so welcome!
Is there a way to select items from a drop down from a dynamic filtered list, but validate against a main list? For example, I want to drop down against a filtered supply order list with pending items, but when the items from the list is cleared, it will not be in the filtered list. How can I get to check from the main list while restricting options to the filtered list?
Hi Leila, I really don't get the reason why when creating a list the output is the whole list. So, I would like to choose between the elements of the list and not the whole list. Can you pls explain me where it possibly could be my mistake
HI mam Please make a separate video for Excel Interview questions.
That's a good one. I've added it to my list. Thanks for your suggestion.
I have a query in data validation - I have more than 50 drop down list, How can i select by entering first 2 or 3 character and able to find the dropdown list shorter instead of all 50 dropdown list. Hope you can understand my query.
if required, i can explain more in details
Hi Leila, how about data validation where you put single input bar where if it equals or contains in any of the list will make the cell on the list turn green, and if not equals to any of the list will become red, is this possible
Hello Leila, thanks for the video. A remark and a question: You don't have to use INDIRECT when working with an ExcelTable. you can just select the column, just hoover the cursor over the columheader until you get a bold black arrow down. then select. in the name manager you see a normal reference (absolute) but this will always be updated.
Then the question: you can also later add validation rules over existing data. You don't get an error if your entry was not correct. If you want to see the invalid data you can use: validation circles. that works fine. But now my question: is there a way to avoid the validation circles in your column for project start date?? Then over time all these dates are before the current date, but at the moment of entry they were correct.
Hi Bart :) yes - you mean through name manager right?
Regarding validation circles - yes, they are helpful to pinpoint wrong inputs. With regard to a validation expiring - you have the ability to clear the circles. Is this what you mean?
Hello Leila, No I did not mean to use the name manager, you can just select a column with the black arrow above the header of a column. Even our friend Mike Girvin does not use this. I think it is just a matter of habit. But I think "my" method is faster.
Forget about the validation circles, this question was too theoretical. By the way: you can drive people to madness if you enter a very strange validation rule and later apply the validation filter. If "they" don't know about data validation, there is no way to remove the circles....:)
Bart Titulaer o
Hello! Even after using the indirect function, I still am getting the same pop-up window. Please help me with it.
Great Video, Thank you Leila khanom
احسنت
can the smart drop down list be down using a source from a different workbook
Great video!!
Thank you very much :)
I used the separator for the static names but it did not appear as a list, the divisions appeared on in a single line with commas separating them. What could be the reason for this?
Very helpful presentation. See minute 15:41, Name table appearance =TableDiv[Division] impossible.
It appears only address like A1:A25. What is happening?
If you already have a table, but you don't see the table references, maybe you have the option turned off. Go to File, Options, Formula and make sure there is tick mark for using table references.
@Leila Gharani -- At 14:16, you were explaining about pasting the values in your data validation to apply to the cells below. When I do it, it doesn't work. :( Why is that? For every selection that I choose in the drop-down list created, it removes the top entry from that list when I go to the next cell below. :(
For the drop-down make sure you have the $ signs set in the data validation for your cell reference.
Question- is there any way that I can automate entering "input message" based on a list of values?
e.g. I have three columns 'Field Name', 'Field Value', and 'Field Definition'. I want to show the definition in the 'Field Name' Column itself as a "Input Message" when the cell is selected. Entering one by one in the data validation tab is a time-consuming thing. Any way/formula I can use to get all the definition in data validation's "input messages" for each field in the list?
I need to create a dropdown list contains names, and then when you select a name it'll display a table or list of names that stored under another tab or sheet
hey Leila Gharani
really thanks for this wonderful data validation i will use more of data validation going forward keep up the great work of uploading more videos and i would like to know in the company code when i attemped it is not allowed to type or input text as in abc123 then i press enter it shows error which should have not come why so i have selected option text lenghth and then equals to option.
Further in your video you have inputed S2010 which is accepted by excel but not in my case excel it is not accepting alphabets but only 5 numbers are accepted by excel like this 12346. kindly help..
You're welcome Dhiraj - have you selected option text length or whole number? For the whole number option, it will only accept values but for text length it should accept any character input. So in your example you can put text length = 6 and then you should be able to input abc123. If you put text length > 4, then you can input S2010 or also abc123.
Please upload video
For Custom Formula
I need first 3 digit should be
Num than 1 digit alphabet than again number
Sure - I will cover some examples like this in an upcoming video (within a month's time)
How can i do
I run Command FILTER in Excel 2007
= FILTER (array, include, [if_empty])
Please Guides the video.
Thanks
Hi Leila, I'm loving your videos as I've been setting aside time to upgrade my Excel skills. At 13:25 of this video, you mentioned to drop a note if we are looking to learn how to use the custom function and the corresponding formula for the "Company code." Can you direct me to the video or the instructions for this? Thank you!
Hello Sir, Can i get a help?
Can i email separate excel result sheet to 800 student in picture format automatically from excel? Currently i send 800 different email manually to 800 different email address. Is there any short cut that will do it automatically?
You deserve the Excel Throne!
ruclips.net/channel/UCtNr753qbiVw61oY_ow_aPw
New channel need your valuable support 🙏❣️😇😍
Whats the Short Cut key to get drop-down of Data validation list ???
So for people that don't know, and maybe having some trouble with the ( , )part, try and use semicolon ( ; ) instead, Because Excel has different settings depending on the language installation among other things. So if , doesn't work anywhere in any formula, try ; :)
👍
Hi Leila, always a pleasure when you explain thing s so clearly. I am a community health worker and I use validation for collecting data. Your videos help to clarify and organize my data. 👍😊💖
Another wonderful video, Leila. I use validation for dropdowns all the time, using the Indirect/table method. I teach Excel in a US public library and recommend your channel and Udemy courses to all my students.
Wow, thanks for your support James!
Leila, you have mastered on what you do. Thank you.
The harassment on this channel is gross. If you creeps have anything to say about her looks or are compelled to make sexual compliments, keep it to yourself, sickos!
I mostly agree. It doesn't hurt that she's beautiful though.
The most complete Data validation tutorials you ever need at one place.
Please Make A Video In VBA Coading 🙏
Thank You Leila
I would Like To Understand How is the Data Validation Works for Multiple Columns.For instance Next Column Data Depend on One Selection from the List of the Previous Column for example The First Column Consist of Colors Data and I Would Like When one is Selecting Yellow the Second Column will Prompt to give only Contents of Yellow e.t.c
I am interested in this answer as well.
Quite informative. Thanks MAM
I wish I had learned this when I was younger. I am 55 and working with time sheets and this could really help me. Your information is helping me understand slowly I will try to lean as much as I can. 😊 Thank you.
i love to do excel because it gives me knowledge and sharpen my brains. So I want to learn excel.
Hi Leila ,
Please Help ....
I have date format 02-03-2022 (want to change cell format date to text)
1. if i change manually by text to column, i get 02-03-2022 (DD-MM-YYYY)
2. If i use text to column by vba code, i get 2-3-2022 (D-M-YYYY)
Please help, how to fix this.......
I wonder how I could do an actually very simple thing: a drop down list with a sequence of number.
I thought the solution would be: Data Validation -> List -> Source : =SEQUENCE(....)
That didn't work, so I changed Source to : =TEXTJOIN(", ",TRUE,SEQUENCE(....))
This does not work either. If I enter the same sequence manually in a cell it does not work either, but if I enter that sequence manually under 'Source' it works. Unbelievable! Please Microsoft make it user friendly. Such a basic thing, one should not need to be an Excel expert.
Can you tell me what i'm doing wrong here?
I have another sheet with the name "All" and i'm trying to find the word "Hello" (for example) within all of column B of that Sheet and have the rest of the formula locate/use the ROW that that word is found on (which can vary and is very random) and append that ROW with the known E column and then covert what it sees in that cell to a Value.
So, Column A contains the word and Column E contains a number on the same Row i need recorded elsewhere in another cell that has the formula below..
Here is what i have so far, but it gives me an error of #Name?
=VALUE(All!E(MATCH("Hello",All!B:B,0)))
So in the formula i have above, I put ALL!E to indicate the known sheet and column.. but since i dont know the row number, as that can vary I put in a Match formula to find that row automatically, which works by itself. Using just "=MATCH("Hello",All!B:B,0)" gives me the correct row when i use that formula by itself, so i know that is working... but i can't figure out how to append that with the sheet and column "All!E"
I figured I could just squeeze the Match formula right behind the E.. but simply plugging that in doesn't seem to work for me.
I love your video's they are very helpful. I'm going to try to use this for during payroll in my company.
A quick question regarding 3:30 -> change the name of a list entry:
How can I command Excel to update/reflect that change automatically if the changed entry was chosen before?
E.g. you are changing GAME into GAMES. Provided, the GAME division was already selected, I'd logically expect that the validation list shows automatically the new value GAMES without selecting it once again from the drop down. Is it possible? Maybe with VBA?
Thanks in advance!
Hi Leila , we can easily copy paste wrong data on validated cells...any solution for this problem
Hi Leila. I have one query and I know you must have a way out for it. I am a sales executive and I use VLOOKUP and HLOOKUP formulas for my daily Reporting and Funnel. But I don't know how will I Summaries the total of my Won account or say business I did through out the month as the format is vertical and at the end I want to have the answer of Won cases with VLOOKUP and HLOOKUP?
Hye Leila.. It's great to found your channel & I subscribed already.. I'm currently learning how to use data validation in excel(I even don't know what's call till found your video). It' really a big help. I'm stuck with two things.
1) On your example on the Tab Data validation Basics, Column Division, is that possible to enter one letter so it's much easier to select which data we looking for. Example, if I type keyword "B", only data with the header "B" letter on the list will appear, instead scrolling all the data on the list. It will so much help if there's a lot of data on the list.
2) Is it possible If I want to make 2nd column auto update when I select data for the 1st column? On excel, I already create a table contain, Name, Tel No, Address & email..
so, on the data validation part, when I select a particular name which is at the first column , automatically tel. no also will appear on the next column.
Hopefully you'll understand my question.
Thank you
Lela, what if I wanted to make a data validation list where things are priorities 1-10 and if I change a row to say 'priority 2', everything below 2 will be renumbered to 3,4 ,5.. . i.e. you can only have a single row with a priority 2 column.
My don't Madam:
Ms office 2021 excel function are available for in this version ?
Xlookup, filter, unique, and some function
My dout Madam:
Ms office 2021 excel function are available for in this version ?
Xlookup, filter, unique, and some function
Thank you, Madam
Wonderful! Ive used data validation when creating Adobe forms, but didn't know how to do it in excel until today. Thank you. I was searching high and low date validation and you provided it easily along with other helpful nuggets. How do you do data validation to require dollar amounts? I see decimals, but I would like the dollar sign included.
Halo Mrs. Leila, would you explain a bit more? in my case my i can select more than 4000 data dropdownlist, there is a way for shorcut copy paste validation? thank you..
Hello Leila,
What about using Validation on Table records? instead of copying and pasting validations, how can the validations carry over when inserting new records?
I noticed the formulas and formats do carry over to the new inserted records but the Validations Don't. Is there a way to resolve this?
As always, thanks for all your excellent tutorials!
I used the data validation to my file then when i open back my laptop, the set up is no longer available to my file it only remains a text that i was selected. What happen, maam? Thank you.
Hi Leila, excel data validation arrows are missing after opening file, Do you know why this happens? How can I solve it??
Hello sir...
I am bit in trouble please help me
I have two spread sheets one is called stocks and another is calculator
In a stock there is three columns category, subcategories and price
And in a calculator i enter category value by list from stock sheet same as subcategories but i can't able to get a price in calculator please help me
For ex category is fruit and subcategory is apple and the price is 100
So i can get a fruit and apple by list in calculator page how i get a price of it in price row
Please reply if you know
Looking forward to hear from you..
Is there a way to autocomplete? I have a list of countries users can choose of. I found two tutorials, but one requires KUTools, the other one a combobox and some coding...