Very nice thanks. Indirect is also particularly useful for making dependent drop-down validation lists where the value you choose from one list affects the available values downstream. So, if you choose "Cars" from one list, you'd see a list of cars in the next list. If you choose "Motorcycles", you'd see just bikes in that next list. The trick is to set up your lists of allowed values on a worksheet and use Named Ranges so that the list of cars is called "Cars", etc. The first drop-down just references your master list of categories ("Cars", "Motorcycles") as normal and the second list then uses the Indirect function (add the formula using the Custom option under 'Allow') pointing at the first list's cell to read whatever you selected. So, if you choose "Cars" in the first list, the second list will read that value and use Indirect to find the Named Range called "Cars". You can chain lists together like that. Very handy!
You are so easy to follow and understand. I have watched multiple videos of yours today while working on a big workbook and its going to be the most amazing thing ever. My client just need an old 97-2003 workbook "template" updated and tidied up but its getting a full makeover! I have it all drop down boxes and xlookups, with locked cells. I have been using Excel for over 2 decades but the tricks I found in your playlists today are such time savers it is incredible. Not usually a fan girl but you are the Taylor Swift of excel sheets!
Thanks! Very easy to understand! I automated my individual marking rubric into each student tabs just like in your video, instead of month Jan, Feb.. I changed it to S1 (student 1), S2 (student 2).. it really saved my time keying marks for my students!
Is there any way to utilize the indirect function with a range of tabs in a workbook? So instead of referencing every sheet in the formula (a,b,c,d,e,f,g) I could use indirect (a:g) to get the results from every sheet between a and g?
This is huge! Thank you!.... One question - I noticed if my Tabs or Sheets are two words, that it's not calculating. Giving me "#REF!" - Any tweaks to the formula for it?
@@ExcelLevelUp Hi I tried to rename the tab and " ' " is not acceptable as Name, Please help as I have my sheet created and I need to rename many names and its tab name to hyperlink to work !!
Very nice thanks. Indirect is also particularly useful for making dependent drop-down validation lists where the value you choose from one list affects the available values downstream. So, if you choose "Cars" from one list, you'd see a list of cars in the next list. If you choose "Motorcycles", you'd see just bikes in that next list. The trick is to set up your lists of allowed values on a worksheet and use Named Ranges so that the list of cars is called "Cars", etc. The first drop-down just references your master list of categories ("Cars", "Motorcycles") as normal and the second list then uses the Indirect function (add the formula using the Custom option under 'Allow') pointing at the first list's cell to read whatever you selected. So, if you choose "Cars" in the first list, the second list will read that value and use Indirect to find the Named Range called "Cars". You can chain lists together like that. Very handy!
Great example that I never considered before. Thanks for contributing for others to learn.
You are so easy to follow and understand. I have watched multiple videos of yours today while working on a big workbook and its going to be the most amazing thing ever. My client just need an old 97-2003 workbook "template" updated and tidied up but its getting a full makeover! I have it all drop down boxes and xlookups, with locked cells. I have been using Excel for over 2 decades but the tricks I found in your playlists today are such time savers it is incredible. Not usually a fan girl but you are the Taylor Swift of excel sheets!
Literal lifesaver! Tried looking up so many indirect functions and they were all so complicated compared to yours. Thank you!!
ugh I love you, this saved me so much time
saved me a bunch of time while budgeting, thank you!
I’m happy to hear it
Thanks! Very easy to understand! I automated my individual marking rubric into each student tabs just like in your video, instead of month Jan, Feb.. I changed it to S1 (student 1), S2 (student 2).. it really saved my time keying marks for my students!
Glad it helped!
Would be great to this example but across another workbook based on the workbook's file name (assuming consistent file naming conventions).
Thanks!☕
Very helpful
Is there any way to utilize the indirect function with a range of tabs in a workbook? So instead of referencing every sheet in the formula (a,b,c,d,e,f,g) I could use indirect (a:g) to get the results from every sheet between a and g?
This is huge! Thank you!.... One question - I noticed if my Tabs or Sheets are two words, that it's not calculating. Giving me "#REF!" - Any tweaks to the formula for it?
Hmmm. Try putting single quote marks before and after the tab name.
@@ExcelLevelUp Hi I tried to rename the tab and " ' " is not acceptable as Name, Please help as I have my sheet created and I need to rename many names and its tab name to hyperlink to work !!
What if indirect the namedrange array? Not the months? Is there a way? I want to for loop but don’t know how
Doesn't work me when my month is a date field. I.e. jan-23 i.e. 01/01/23 in the formula bar. Is there a workaround