This video was the closest I came to getting it right! I got as far as entering the 1st dependent list..but it would not work for all the other cells! Thank you. It was a great video.
That is simpler than the one I was always doing. I usually went for OFFSET and MATCH combo for the second list. I didn't need to create table for each list separately, but this formula is much cleaner. Always good to learn something new :)
@@forhadleobd Sure. You just put all the values a little bit like in the video, so that you have headers in the first row of a sheet (in this example "Income" and "Expense") and corresponding values below it (So column A1 would be "Income", A2 - "Salary" etc. B1 - "Expense", B2 - "Rent" etc.). Then let's say you put your selector (as in "Income" or "Expense" that would determine which list to choose from) in G2 as it is in the video. Then the formula is =OFFSET($A:$A;0;MATCH(G2;$1:$1;0)). The formula in the video looks much better and it is easier to see what is going on if you for exaple look at this file after a while and try to remember how it works, but the version I wrote doesn't require you to create a separate table and name it every time, and is faster for very large data sets. In conclusion, if you want something for your home usage or for managing small amounts of data you should probably use the formula from the video. On the other hand if you are managing big database with loads of options, then probably my formula would perform better. If you have any questions, I would be glad to answer :)
Good tutorial! I realize that my comment exceeds the primary scope of this tutorial, but I do have to ask. When changing from “Expense” to “Income”, (at 00:19) the “Category“ field didn’t warn of the mis-match of data. Is there a method to “trap” an error such as this?
Thank You very much for what seems to be by far the easiest way to do this, as well as the cleanest one, as everyone else seems to like the idea of having the variables on the same sheet, I like the way you did it. One followup question though: Can I input some sort of sum formula which helps me with getting specific sums, that is - how much was side hustle in a specific month? Thanks a bunch for the video.
I'm glad it was useful for you. 🙏👍 About the sum formula with a criteria, you can use SUMIF for one criterion or SUMIFS for more than one criterion. I have two practical videos that can help you: SUMIF ruclips.net/video/KUBTVv0m3K8/видео.html SUMIFS ruclips.net/video/7mnv35sEmRA/видео.html
Thanks Bro for this video. But i am having some kind of challenge seeing that after using the indirect function, if you change to either Income or Expense the list pops out all of them but mine i have to drag and fill before it displays. Pls is there any settings i should do in my excel for easy automation. Thank you
Can you make it so when you choose for example the rent option the expenses label auto populates? So like you skip entering it but its just there already?
Please give me total of better than more excel sheet tricks and ms word symbols create in far than better beautiful❤ lovely symbols in 45 typeses ok so all the best sir i give you some more like this chennel on now 💯👌🙏
This is great! However, my table name has space but I think space and special characters are not supported in table names. Do you have suggestions for these?
Hello! Space and special characters cannot actually be used in Excel tables currently. My suggestion is to use underscore ___ instead of space, what do you think?
What do you do if your headers have multiple words, for example, check fraud? You want a clean drop down that says "check fraud" instead of "check_fraud"
Eu sempre procuro conteúdo em inglês porque é muito mais vasto, geralmente no Brasil é tudo muito limitado. Além disso, o alcance é muito maior em inglês.
I entered the =Indirect(G2) and the only word that appears is the first word in list "rent". May I ask what went wrong in my entry? Update: I didn't stop until I got the right data. Thank you for your help :)
For the indirect function part, mine will show whatever inside the row only. Example, if i create an indirect function expense for row 3, then only the rent will show
@@aqsanaeem6955 type your indirect function and press enter, you will see just one record as u mentioned above. Later, select this result with some cells below at the same time and press crtl+shift+enter. Unfortunately, u see the records at the number of below cells u selected.
If you delete the spreadsheet which contains the options used in the list, the list will no longer works. However, if you don't want to use multiple sheets to place your lists, you can insert the options into the list itself manually. So, you go to data, list, and source will be like: Option 1,Option 2,Option 3,Option 4... It is very important not to forget to use the comma to separate each option.
When you are pressing "enter" it shows you all the values in the table but when I press enter at my pc, it only shows the first value. What am I doing wrong?
Hi How can I make a data validation list from cells in a row. I have a data sheet containing customer name, contact Name 1, Contact Name 2 and Contact Name 3. in another sheet I have a cell where I can (using data validation) pull in customer name. now there is another cell with Contact Name. Now I want the 3 names (Contact Name1...3) show up as drop down. how can this be done? Thank you
When you rename a Table through "Table design" then "Table name" you can't actually use space. But anyway, it will not work with space. Maybe you can try underscore or hyphen... 🙏👍
This video was the closest I came to getting it right! I got as far as entering the 1st dependent list..but it would not work for all the other cells! Thank you. It was a great video.
Have you figured it out yet? I’m having same challenge.
@jopa Excel im also getting the sane challenge, Can you Please clarify this ?
Check to ensure that your cell reference is not fixed. The cell reference in the INDIRECT function should not have $ anywhere
agree excel sucks
That is simpler than the one I was always doing. I usually went for OFFSET and MATCH combo for the second list. I didn't need to create table for each list separately, but this formula is much cleaner. Always good to learn something new :)
@@forhadleobd Sure. You just put all the values a little bit like in the video, so that you have headers in the first row of a sheet (in this example "Income" and "Expense") and corresponding values below it (So column A1 would be "Income", A2 - "Salary" etc. B1 - "Expense", B2 - "Rent" etc.). Then let's say you put your selector (as in "Income" or "Expense" that would determine which list to choose from) in G2 as it is in the video. Then the formula is =OFFSET($A:$A;0;MATCH(G2;$1:$1;0)).
The formula in the video looks much better and it is easier to see what is going on if you for exaple look at this file after a while and try to remember how it works, but the version I wrote doesn't require you to create a separate table and name it every time, and is faster for very large data sets.
In conclusion, if you want something for your home usage or for managing small amounts of data you should probably use the formula from the video. On the other hand if you are managing big database with loads of options, then probably my formula would perform better.
If you have any questions, I would be glad to answer :)
BRILLIANT - Thanks for the video solved a problem for me
Thank you for your effort that you put in this tutorial. Really straight forward and helpful. 10/10
Amazing! Thank you. 9:10 can you force the Category column to delete or rest whenever you select Type?
This is cool. I've been toying around with this idea and now you show me how.
Thanks so much.
I'm glad you liked it! Thanks 🙏👍😁
This is really amazing. Thank you!
Brilliant - dependent drop down finally worked for me! Thank you!
This is the best explanition I have found so far. Thank you.
Good tutorial! I realize that my comment exceeds the primary scope of this tutorial, but I do have to ask. When changing from “Expense” to “Income”, (at 00:19) the “Category“ field didn’t warn of the mis-match of data. Is there a method to “trap” an error such as this?
did you find the way of doing this?
@@HarshG28 I have not.
Great, It was really simple to extend to all the column. Congrats, very useful
Thanks. All works!
This was super helpful :D Thanks!!
Thank You very much for what seems to be by far the easiest way to do this, as well as the cleanest one, as everyone else seems to like the idea of having the variables on the same sheet, I like the way you did it.
One followup question though:
Can I input some sort of sum formula which helps me with getting specific sums, that is - how much was side hustle in a specific month?
Thanks a bunch for the video.
I'm glad it was useful for you. 🙏👍
About the sum formula with a criteria, you can use SUMIF for one criterion or SUMIFS for more than one criterion.
I have two practical videos that can help you:
SUMIF ruclips.net/video/KUBTVv0m3K8/видео.html
SUMIFS ruclips.net/video/7mnv35sEmRA/видео.html
@@JopaExcel Can we clear the category whenever we are changing Type?
Thank you bro, its really helpful for me
Thank you very much sir. Wow, you made it easier! Great job!
Thank you! 🙏👍
This was very helpful! Thank you!
Excellent tutorial!! Thanks
This was well explained, many thanks for your time and effort
Direct and Helpful. Thank you
Thanks for such nice explanation . it helped.
Gald you liked it 🙏👍
Thanks Jopa! This really helped! Can we use the same process for bring over the table completely with formatted cells (add’l pick lists, etc.)?
Excellent boss you solved my problem
Thanks Man !
Thank you for your effort, it's so helpful
I'm glad it was helpful, Julia! 🙏👍
Excellent stuff! Thanks.
It is exactly what I need but on Google sheets it doesn't work. Would it be possible to do it on Google sheets?
Thanks!
Thank you so much! This was super helpful!
Glad it was helpful, Emma! 🙏👍
Thank you, very clean and understandable tutorial.
Glad it was helpful! 🙏👍
@@JopaExcel I got as far as entering the 1st dependent list..but it would not work for all the other cells! Thank you. It was a great video.
This is awesome, sir! Thank you for the tutorial!
Excellent video!!!! Thanks so much,
I'm glad you like it! Thanks for the feedback 🙏👍
Awesome tutorial...very informative....
Thank you so much ☺
Thank you. I was doing it the hard way.
I'm glad it was useful for you. Thanks for the feedback 🙏👍
Super simple and well explained
Thanks Bro for this video. But i am having some kind of challenge seeing that after using the indirect function, if you change to either Income or Expense the list pops out all of them but mine i have to drag and fill before it displays. Pls is there any settings i should do in my excel for easy automation. Thank you
thank you very much for you video :)
Thank you.... Is it possible to implement the same in Google sheets?
Can you make it so when you choose for example the rent option the expenses label auto populates? So like you skip entering it but its just there already?
Thank you!
Thank you so much
God Bless You
Excellent Thanks
Thanks for the video
Your welcome 🙏👍
very helpful. thanks
Glad it was helpful!🙏👍
What version of Microsoft you're using, as I'm using 2019 and doesn't show the whole data, just extract the data of the first row.
very helpful.
Thanks man! i really needed to learn this 🤍👍😀
Hi, Good job.
After writing the indirect func and clicking on Alt, it selects only one variable? Please help
Please give me total of better than more excel sheet tricks and ms word symbols create in far than better beautiful❤ lovely symbols in 45 typeses ok so all the best sir i give you some more like this chennel on now 💯👌🙏
This is great! However, my table name has space but I think space and special characters are not supported in table names. Do you have suggestions for these?
Hello! Space and special characters cannot actually be used in Excel tables currently. My suggestion is to use underscore ___ instead of space, what do you think?
@@JopaExcel hmm yeah. I used underscore instead. And perfectly works. Thanks 👍
Can google sheet do the same thing like this?
really cool, and useful
Thank you 🙏👍
his really helped!
I'm glad it was useful! Thanks for the feedback 🙏👍
great man
It works but for only with specifc block, any other formula for linking with whole column, everyone is open for answers
Thanks!
is good if you can reset the second dorop down list in the moment you change the category
For sure, I know what you mean. It would be awesome. But, unfortunately we can't do it, maybe via VBA it's possible.
Thanks Teacher
Super
nice one😊. its select everything. not every one!!😘
Ok. Thank you! 👍
How do you color code each category?
What do you do if your headers have multiple words, for example, check fraud? You want a clean drop down that says "check fraud" instead of "check_fraud"
you can anther colonne with a formula =if this cellul equal chek_fraud put chek fraud
Can you do this in Google Sheets?
Eu sempre procuro conteúdo em inglês porque é muito mais vasto, geralmente no Brasil é tudo muito limitado. Além disso, o alcance é muito maior em inglês.
So for each row in Column E we have to manually enter Data validation from INDIRECT function?
No, you can use the "small green square" in the bottom right corner to copy the information in the cell.
It should also update from C3 to C4, C5, etc
Thank you
Great video ❤ New subbie here 👋
Thank you! 😁🙏👍
how if you make it with a amount then you make a separate table for the Income Amount and Expenses Amount how to do that
Instead of this we can use xlookup function. We can use for big data and also less steps
I entered the =Indirect(G2) and the only word that appears is the first word in list "rent". May I ask what went wrong in my entry?
Update: I didn't stop until I got the right data. Thank you for your help :)
How did you donit
amazing
That Final part INDIRECT(X6) . I have merged cells over there. If i entered X6 shows me Error. How can i Resolve ??
For the indirect function part, mine will show whatever inside the row only. Example, if i create an indirect function expense for row 3, then only the rent will show
Mine too.. were you able to solve that issue?
Facing the same prob. Did you find a solution?
@@aqsanaeem6955
type your indirect function and press enter, you will see just one record as u mentioned above. Later, select this result with some cells below at the same time and press crtl+shift+enter. Unfortunately, u see the records at the number of below cells u selected.
What happens if I delete the other sheet where I put the options? Is it necessary to include that in my report if ever?
If you delete the spreadsheet which contains the options used in the list, the list will no longer works. However, if you don't want to use multiple sheets to place your lists, you can insert the options into the list itself manually.
So, you go to data, list, and source will be like: Option 1,Option 2,Option 3,Option 4...
It is very important not to forget to use the comma to separate each option.
@@JopaExcel Oh is this okay in any report?
When you are pressing "enter" it shows you all the values in the table but when I press enter at my pc, it only shows the first value. What am I doing wrong?
try ctrl+shift+enter after the result with some cells below selected
@zarinahaciyeva1784 i tried but its not showing the way it's showed in the video.
I want add sub item to drop down list item in Google form
Hi How can I make a data validation list from cells in a row. I have a data sheet containing customer name, contact Name 1, Contact Name 2 and Contact Name 3. in another sheet I have a cell where I can (using data validation) pull in customer name. now there is another cell with Contact Name. Now I want the 3 names (Contact Name1...3) show up as drop down. how can this be done? Thank you
It doesn't work on Google sheets... Any similar tips for google sheets?
Have u gotten answer to this. I would like to know thanks
Waow I just tried it and it is working. Click on data, left click on data validation then add rules😅😅😅
Right click...
Hope its helpful
It's seems better on Google sheets than excel
Try selecting the value with / it's not working.can you handle the data which is having many chars such as *,",#,₹
It doesn't work if Table name having space?
When you rename a Table through "Table design" then "Table name" you can't actually use space. But anyway, it will not work with space.
Maybe you can try underscore or hyphen... 🙏👍
Good video. BUT I prefer investments instead of investiments
😂
Thanku
10/10
LIBRARY SYSTEM PLEASE
This leads to an error, try enters a different value
Hello, Roland! What type of error? Could you give me more details, so I can help you. Thanks!
@@JopaExcelHi I was editing a sheet on web. Got an error when I tried to name the table after header saying names already exists it should unique.
❤
👍
🙏👍
doesnt work
Hello! Which part of the video did you have a problem with? Give me more details, otherwise I cant help you... Thanks!
sir plz get it fast do not people time. it was good but always make short video
You might want to show us how to create the dependencies first before you start talking about the other stuff.
That’s a different video. I watched it earlier today.
thank you
Welcome! 🙏👍
❤
NOT WORKING