Couple of suggestions with your final example: Dropdown list: Rather than hard coding the list in, a more dynamic way would be to reference the cell range for the column headers you already have typed at the top of the table data. Eliminates the chance of a typographic error. "Dynamically Constructed Range" instead of "Static Range Name": Biggest fault with using Range Names is they are static in nature and don't allow for easy growth in a table as it evolves. Instead, better to use helper cells to dynamically construct the tables range (Start & End Row & Column values) and use INDIRECT() in conjunction with table searching using the column headers to achieve the same result. Benefit is additional columns and rows can be inserted into the table without the need to then go & manually defining new additional ranges to be used.
Hi.. thanks for this tutorial on INDIRECT which is a super cool and super useful function. I see a lot of Internet chatter about not using it because it is volatile.. which is true.. but as long as you know that fact and unless you are using it excessively in a very large or complicated workbook, it is a non-issue, particularly with how fast processors are these days. You can do so many fun and interesting things with it. Thanks for sharing your tips. Thumbs up!
Hello Sir. TeachExcel, How are you? Happy New Year. I found this video and was extremely excited to learn the format that you shared. I do have a query if during the month and or week, I transfer money from checking to savings and or received refunds from purchases, can you share how the formula can be modified to do so? I really appreciate and look forward to seeing that video. Sincerely New Viewer
How can format a number for instance in cell A4 saying unit if the value is equql to one as one unit, but if it's bigger then one saying units? Thank you
Sorry to tell you, but the indirect function is really not recommended to use. Its a highly volatile function and can hit the performance of a workbook really badly if you use it to often. You can rewrite any indirect formula with the index-match or choose function very easily.
This was so helpful and loved the humor in between. Thanks !!
Thanks, simple but clear introduction. Exactly what I was looking for.
Couple of suggestions with your final example:
Dropdown list:
Rather than hard coding the list in, a more dynamic way would be to reference the cell range for the column headers you already have typed at the top of the table data. Eliminates the chance of a typographic error.
"Dynamically Constructed Range" instead of "Static Range Name":
Biggest fault with using Range Names is they are static in nature and don't allow for easy growth in a table as it evolves. Instead, better to use helper cells to dynamically construct the tables range (Start & End Row & Column values) and use INDIRECT() in conjunction with table searching using the column headers to achieve the same result. Benefit is additional columns and rows can be inserted into the table without the need to then go & manually defining new additional ranges to be used.
Hi.. thanks for this tutorial on INDIRECT which is a super cool and super useful function. I see a lot of Internet chatter about not using it because it is volatile.. which is true.. but as long as you know that fact and unless you are using it excessively in a very large or complicated workbook, it is a non-issue, particularly with how fast processors are these days. You can do so many fun and interesting things with it. Thanks for sharing your tips. Thumbs up!
Thank you for this great tutorial.
Well explained liked the min max part. Context of scenario where you can you indirect. Thanks for sharing
You are very welcome Analir! I'm lad you like it :))
Thanks so much! My indirect wasn't working and I didn't know why until I saw you use the name range manager
Great. Thanks so much.
Hello Sir. TeachExcel, How are you? Happy New Year. I found this video and was extremely excited to learn the format that you shared. I do have a query if during the month and or week, I transfer money from checking to savings and or received refunds from purchases, can you share how the formula can be modified to do so? I really appreciate and look forward to seeing that video.
Sincerely
New Viewer
Not much in use but still can do wonders when used with name, vlookup etc.
How can format a number for instance in cell A4 saying unit if the value is equql to one as one unit, but if it's bigger then one saying units?
Thank you
46,000 views and only 850 thumbs up....that's rude
YOu lost me at "anything with an equal sign is a formula". I realized i had to move along.
🇳🇵
Sorry to tell you, but the indirect function is really not recommended to use. Its a highly volatile function and can hit the performance of a workbook really badly if you use it to often. You can rewrite any indirect formula with the index-match or choose function very easily.