Hi Rana.. I am really thanksful to you. Now I am working as an acccountant in Oman.. My company launched last month. So they dont have accounting software. Yesterday my senior accountant came and he copied this datavalidation sheet from his usb .. Actually he also dont know how to create cash book as data valiation system. Today i tried more time to get that data validation entry.. After 2 hour search in youtube i found ur video . wish u a late happy lndependance day. I proud of u as an Indian
Thanks so much for posting this video. I have been searching for 2 nights for help on doing this in openoffice. This is a great help and has pointed me in the right direction. Tutorial is very well explained and simple to follow. Thanks again.
hey! did a bang up job!! it seems you are going from strength to strength and I'm sure you will continue that way. Never stop doing what you do best!! :)
Thanks for your kind comment. I am glad that you got a fix for your problem. Actually I am from Bangladesh but of course, we share the same spirit when it comes to helping people out :) take care and best of luck.
In excel 2003, only way to delete named ranges/formulas is through Insert>Name>Define as you pointed. Shortcut to reach Define name box is Ctrl+F3. Excel 2007 added an incredible 'Name Manger' to take care of that along with other benefits. Hope this helps. Thanks for your query
Thank you for your kind comment and pointing out an error in my annotation. I have shown a way to avoid the 'IF" function by using the "INDIRECT" function at about 3:40. I think you have used the latter and used the absolute reference, like this: =INDIRECT($A$3). The correct formula would be: =INDIRECT(A3). I have corrected the annotation and it should be working fine now. My apology for that mistake and thanks again!
To make, for example, A1 non editable: do the following: a. Select the whole worksheet b. Press Ctrl+1 and go to the 'Protection' tab c. Uncheck 'Locked'- first step to make all the cells unlocked and editable. Press ok. e. Then select A1, press Ctrl+1, go to 'proteciton' tab and check 'Locked', press ok f. In excel 2010, go to file>info>'Protect workbook'>'Protect current sheet' and select the actions that you would like to allow on the 'unprotected' cells. You can even put a password. Cheers!
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?
Hi Rana. In your example, when you completed the different lists, and you selected for example "Furniture", the 'Apple' item remained in there. Is there a way in which when you select a different category, the sub-category field gets cleared-out (blank), or at least changed to the first item of the sub-category? Thank you.
Glad that you like it! :) I was not in touch for a long time but planning to return with more videos on Excel. Please check out and suggest if you want anything specific. Thanks
Please make sure that you've done the following things right: 1. Name of the ranges cannot contain any 'space'. Excel simply won't allow you to do that! 2. Make sure that the names used in this formula are exactly the same as you defined e.g. you've used 'CrossSkilled' and 'Crossskill'. 3. Don't forget to end the quote! Notice the Cross Skilled where end quote is missing. Other than these issues, the formula looks fine and should be working. Thanks for your query. Cheers
I’ve tried this and it has worked on my spreadsheet, however when I’ve saved my work, come out of it and gone back in the data validation is no longer there - help! Thanks
What if along with the two inputs in the column, someone wants to automatically populate the third column. For example, in the video, after selecting fruits and apple, the unit cost of an apple should be displayed. How does one go about doing that?
Hello. First of all, I like your video class. However, when it all goes wrong here at the moment I try to copy down to multiple rows (5000). I am not working with Categories and Fruits, but with Cities and Hospitals in that city. In my first column "City" it works fine, but in my second "Hospital" it copies the validation with reference to the first cell in "City" instead of referring to the cell in the same row..In other words: I get an "absolute" copy of the "formula" instead of a "relative".
I am not sure I fully understand your query. Would you please explain a little bit more. My initial thought is that multiple selection for a single cell entry won't be valid unless of course if you plan to put multiple items in multiple cells after users make the selection.
Hi Jonny Scott, formula for validation tab does not have any special limitation! It's only limited by the formula itself. For example, if you are using an IF function, you cannot nest more than 7 IF functions within a formula. But there are a lot of efficient ways to deal with that sort of situations. For example, you can use functions like INDIRECT and a variation of LOOKUP as alternatives. I have already shown the use of INDIRECT function in this video (annotated-visible from computer)
Rana Chowdhury, I was hoping you could help answer a little more detailed question. If you have three sheets and the first one with a simple list that you want to work from. But Below the list you want each item in the list to be able to answers questions from things listed below, and the go to your next item on the list and the same thing. then have those questions that you answered totaled on the next sheets from all your items listed?
Hello, Whenever i select Ok after the applying if function a message appears saying "the list source must be delimited list, or a reference to single row or column. " I have checked all the steps so many times still its coming . Please can you help in it . ?
Dear Rana, Your video is great. but going to complete list of fruits is terrible.. if i want to select a fruits starting with "z" then i have to go down complete list. So can i directly go on fruits starting with "z"?
Hi, thanks for liking it and apology for the epic delay! I hope you have already found the solution, in case if you have't , here's a shortcut to select the entire list until the last entry (or more formally, last non-blank cell) in your list: CTRL+SHIFT+Down arrow. You can use CTRL key with any of the arrow key to navigate to the end of a list and hitting the SHIFT at the same time will select till the end of the list.
It's because of the naming convention that almost all the analytical applications follow. I would use an underscore to join two words. So, my suggestion: use NEW_YORK and it should work fine. Hope it helps. Cheers!
Mmm naming ranges when my first list 500 cells long and my second is 60,000 long will take far too long. Im trying to figure out another way, but its proving difficult
I hope you have already found the solution, in case if you have't , here's a shortcut to select the entire list until the last entry (or more formally, last non-blank cell) in your list: CTRL+SHIFT+Down arrow. You can use CTRL key with any of the arrow key to navigate to the end of a list and hitting the SHIFT at the same time will select till the end of the list. Thanks :)
Good video but i am getting a message appears"THE LIST SOURCE MUST BE DELIMITED LIST, OR A REFERENCE TO SINGLE ROW OR COLUMN" i have done it so many times but same result.waiting for positive response.
Hi Rana.. I am really thanksful to you. Now I am working as an acccountant in Oman.. My company launched last month. So they dont have accounting software. Yesterday my senior accountant came and he copied this datavalidation sheet from his usb .. Actually he also dont know how to create cash book as data valiation system. Today i tried more time to get that data validation entry.. After 2 hour search in youtube i found ur video . wish u a late happy lndependance day. I proud of u as an Indian
Thanks so much for posting this video. I have been searching for 2 nights for help on doing this in openoffice. This is a great help and has pointed me in the right direction.
Tutorial is very well explained and simple to follow.
Thanks again.
Thanks for your comment. needed some inspiration and you just boosted it up :)
hey! did a bang up job!! it seems you are going from strength to strength and I'm sure you will continue that way. Never stop doing what you do best!! :)
Thanks for your kind comment. I am glad that you got a fix for your problem. Actually I am from Bangladesh but of course, we share the same spirit when it comes to helping people out :) take care and best of luck.
In excel 2003, only way to delete named ranges/formulas is through Insert>Name>Define as you pointed. Shortcut to reach Define name box is Ctrl+F3.
Excel 2007 added an incredible 'Name Manger' to take care of that along with other benefits.
Hope this helps. Thanks for your query
gr8 tutorial
waiting for 'use of array' in upcoming sessions
Thank you for your kind comment and pointing out an error in my annotation. I have shown a way to avoid the 'IF" function by using the "INDIRECT" function at about 3:40. I think you have used the latter and used the absolute reference, like this: =INDIRECT($A$3). The correct formula would be: =INDIRECT(A3). I have corrected the annotation and it should be working fine now. My apology for that mistake and thanks again!
Thank you very much. Indeed, I used the INDIRECT function and I did found out about the dollar signs. All ends well.;-)
To make, for example, A1 non editable: do the following:
a. Select the whole worksheet
b. Press Ctrl+1 and go to the 'Protection' tab
c. Uncheck 'Locked'- first step to make all the cells unlocked and editable. Press ok.
e. Then select A1, press Ctrl+1, go to 'proteciton' tab and check 'Locked', press ok
f. In excel 2010, go to file>info>'Protect workbook'>'Protect current sheet' and select the actions that you would like to allow on the 'unprotected' cells. You can even put a password.
Cheers!
Very well explained. 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?
The only way to delete an existing range name, or modify its cell reference, is via Insert>Name>Define
Hi Rana. In your example, when you completed the different lists, and you selected for example "Furniture", the 'Apple' item remained in there. Is there a way in which when you select a different category, the sub-category field gets cleared-out (blank), or at least changed to the first item of the sub-category? Thank you.
Great to know it worked! Cheers...
superb video!!!!
Exactly what I wanted... you explained in a detail and quick way....
thanks a lot mate :)
Glad that you like it! :) I was not in touch for a long time but planning to return with more videos on Excel. Please check out and suggest if you want anything specific. Thanks
Glad to know it helped!
Please make sure that you've done the following things right:
1. Name of the ranges cannot contain any 'space'. Excel simply won't allow you to do that!
2. Make sure that the names used in this formula are exactly the same as you defined e.g. you've used 'CrossSkilled' and 'Crossskill'.
3. Don't forget to end the quote! Notice the Cross Skilled where end quote is missing.
Other than these issues, the formula looks fine and should be working. Thanks for your query. Cheers
thank i learn many thing from this but formulu u do on u r tutorial are not applided in my computer why????????????
I’ve tried this and it has worked on my spreadsheet, however when I’ve saved my work, come out of it and gone back in the data validation is no longer there - help! Thanks
What if along with the two inputs in the column, someone wants to automatically populate the third column. For example, in the video, after selecting fruits and apple, the unit cost of an apple should be displayed. How does one go about doing that?
Hello. First of all, I like your video class. However, when it all goes wrong here at the moment I try to copy down to multiple rows (5000). I am not working with Categories and Fruits, but with Cities and Hospitals in that city. In my first column "City" it works fine, but in my second "Hospital" it copies the validation with reference to the first cell in "City" instead of referring to the cell in the same row..In other words: I get an "absolute" copy of the "formula" instead of a "relative".
I am not sure I fully understand your query. Would you please explain a little bit more. My initial thought is that multiple selection for a single cell entry won't be valid unless of course if you plan to put multiple items in multiple cells after users make the selection.
awesome... thanks :)
THANKS. IT IS EDUCATIVE
Tnx For That
Hi Jonny Scott, formula for validation tab does not have any special limitation! It's only limited by the formula itself. For example, if you are using an IF function, you cannot nest more than 7 IF functions within a formula. But there are a lot of efficient ways to deal with that sort of situations. For example, you can use functions like INDIRECT and a variation of LOOKUP as alternatives. I have already shown the use of INDIRECT function in this video (annotated-visible from computer)
Rana Chowdhury, I was hoping you could help answer a little more detailed question.
If you have three sheets and the first one with a simple list that you want to work from. But Below the list you want each item in the list to be able to answers questions from things listed below, and the go to your next item on the list and the same thing. then have those questions that you answered totaled on the next sheets from all your items listed?
Hello,
Whenever i select Ok after the applying if function a message appears saying "the list source must be delimited list, or a reference to single row or column. "
I have checked all the steps so many times still its coming . Please can you help in it . ?
Perhaps, I can help you optimize the formula to make it more robust. Please feel free for any suggestion. Thanks.
Dear Rana, Your video is great. but going to complete list of fruits is terrible.. if i want to select a fruits starting with "z" then i have to go down complete list. So can i directly go on fruits starting with "z"?
Hi, thanks for liking it and apology for the epic delay! I hope you have already found the solution, in case if you have't , here's a shortcut to select the entire list until the last entry (or more formally, last non-blank cell) in your list: CTRL+SHIFT+Down arrow. You can use CTRL key with any of the arrow key to navigate to the end of a list and hitting the SHIFT at the same time will select till the end of the list.
thanks
What happens when you run out of space on the data vaildation tab? I have a lot of rules but cannot fit them all on!
It's because of the naming convention that almost all the analytical applications follow. I would use an underscore to join two words. So, my suggestion: use NEW_YORK and it should work fine. Hope it helps. Cheers!
how do i delete a name list (furniture, fruits ,etC) ??
thank you
How can I make the list cell a non editable cell?
Mmm naming ranges when my first list 500 cells long and my second is 60,000 long will take far too long. Im trying to figure out another way, but its proving difficult
I hope you have already found the solution, in case if you have't , here's a shortcut to select the entire list until the last entry (or more formally, last non-blank cell) in your list: CTRL+SHIFT+Down arrow. You can use CTRL key with any of the arrow key to navigate to the end of a list and hitting the SHIFT at the same time will select till the end of the list. Thanks :)
Good video but i am getting a message appears"THE LIST SOURCE MUST BE DELIMITED LIST, OR A REFERENCE TO SINGLE ROW OR COLUMN" i have done it so many times but same result.waiting for positive response.
before you start applying the formula select any one item from the list above
any one item from category list..i mean
Thank you so much....This really very helpful...u saved me...hehehehe =)
if the cell empty then what
plz relply me