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!
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!
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!
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?
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?