8 Excel Functions that Return References - Do you know them all? - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Excel
Dear Mynda,
Regarding the slightly more effective use of -INDIRECT()- to return a reference (a range of cells), one can take the following steps:
(0) Select the range -B7:E7- , i.e. the range of data cells inclusive of the column headers.
(1) Using the _-Create Names From-_ Excel shortcut -CTRL+SHIFT+F3- , define AUTOMATICALLY CREATED names for each individual vertical range of cells in columns B through E. This will result in 4 automatically created names --Year-, -Category-, -Product-- and --Sales-- .
(2) Write in cell -B13- the name of the cells (i.e. the name of the range) which you want to display in cells -B14:B17- . For example: -Sales-
(3a) If you're using Excel 365, write in cell -B14- the following formula: -=INDIRECT(B13)- and let Excel 365 spill the resulting array onto cells -B14 through B17- .
(3b) If you're using Excel 201x, select the cells -B14:B17- , press the button F2 and write the same formula; however, this time enter it with -CTRL+SHIFT+ENTER- as an array formula. Voila!
In case some column headers are made up of multiple words separated by blank spaces, then the formula needs to be changed to:
-=INDIRECT(SUBSTITUTE(B13,- -,-_-))- since Excel replaces blank spaces with the underline character when creating names from column and/or row headers of a data table.
I shall enter this as a comment below your blog post too. I have been following you together with my students since Spring 2016. And thanks to my students of the Spring semester in 2017, I also got the same --I simply EXCEL-- t-shirt as yours. (:D
reply
Dear Mynda,
Regarding the slightly more effective use of -INDIRECT()- to return a reference (a range of cells), one can take the following steps:
(0) Select the range -B7:E7- , i.e. the range of data cells inclusive of the column headers.
(1) Using the _-Create Names From-_ Excel shortcut -CTRL+SHIFT+F3- , define AUTOMATICALLY CREATED names for each individual vertical range of cells in columns B through E. This will result in 4 automatically created names --Year-, -Category-, -Product-- and --Sales-- .
(2) Write in cell -B13- the name of the cells (i.e. the name of the range) which you want to display in cells -B14:B17- . For example: -Sales-
(3a) If you're using Excel 365, write in cell -B14- the following formula: -=INDIRECT(B13)- and let Excel 365 spill the resulting array onto cells -B14 through B17- .
(3b) If you're using Excel 201x, select the cells -B14:B17- , press the button F2 and write the same formula; however, this time enter it with -CTRL+SHIFT+ENTER- as an array formula. Voila!
In case some column headers are made up of multiple words separated by blank spaces, then the formula needs to be changed to:
-=INDIRECT(SUBSTITUTE(B13,- -,-_-))- since Excel replaces blank spaces with the underline character when creating names from column and/or row headers of a data table.
I shall enter this as a comment below your blog post too. I have been following you together with my students since Spring 2016. And thanks to my students of the Spring semester in 2017, I also got the same --I simply EXCEL-- t-shirt as yours. (:D
reply
Cory
I'm definitely a self-taught excel user and I don't know much.
I was wondering if you could help me out.
I am making a spreadsheet that will have multiple sheets with similar data, the difference will be that individual employee will have an assigned sheet and in that sheet will be what they accomplished throughout the year by month. I'll have a -Summary- sheet at the end that I wish to -pull- data from the other sheets to see particular information that I need at the time.
For example, I want to see how Person A performed during the months of January - March. So perhaps a drop down list or something so that when I choose the name and month, the employee's month performance is displayed. Then i continue to do that for the other months and then average those months out to see -historical work ethic- as we call it.
I don't know if I'm explaining it right but it almost seems like this video answers that question but I can't -see- it. I will rewatch this video a few more times to see if I can figure it out
reply
I'm definitely a self-taught excel user and I don't know much.
I was wondering if you could help me out.
I am making a spreadsheet that will have multiple sheets with similar data, the difference will be that individual employee will have an assigned sheet and in that sheet will be what they accomplished throughout the year by month. I'll have a -Summary- sheet at the end that I wish to -pull- data from the other sheets to see particular information that I need at the time.
For example, I want to see how Person A performed during the months of January - March. So perhaps a drop down list or something so that when I choose the name and month, the employee's month performance is displayed. Then i continue to do that for the other months and then average those months out to see -historical work ethic- as we call it.
I don't know if I'm explaining it right but it almost seems like this video answers that question but I can't -see- it. I will rewatch this video a few more times to see if I can figure it out
reply
Rico
Great video. I use Xlookup for charts on workbook data if the data is ordered. For example if I have a bunch of data for all companies, ordered -Company 1-, -Company 2- etc, then I can create a dynamic chart from a company validation list. By using Xlookup normally I can get the first row of data for company 1, then using the start from bottom of range criteria in the xlookup I can get the last occurrence. I can use this xlookup:xlookup reference in a named range for reference in the chart and use the validation list to change company. Hope that makes sense!
reply
Great video. I use Xlookup for charts on workbook data if the data is ordered. For example if I have a bunch of data for all companies, ordered -Company 1-, -Company 2- etc, then I can create a dynamic chart from a company validation list. By using Xlookup normally I can get the first row of data for company 1, then using the start from bottom of range criteria in the xlookup I can get the last occurrence. I can use this xlookup:xlookup reference in a named range for reference in the chart and use the validation list to change company. Hope that makes sense!
reply
Wayne
Hi Mynda.. great video and lesson on functions that return references. All are great, but INDEX is still tops for efficiency and flexibility. I use each of these regularly, but pick which one depending on the circumstances. Thanks for all the great examples and insights. Thumbs up!!
PS - Agree wth Reda below.. learning these functions and the tricks to using them opens up tons of options and possibilities. Thanks for sharing your wisdom and knowledge :))
reply
Hi Mynda.. great video and lesson on functions that return references. All are great, but INDEX is still tops for efficiency and flexibility. I use each of these regularly, but pick which one depending on the circumstances. Thanks for all the great examples and insights. Thumbs up!!
PS - Agree wth Reda below.. learning these functions and the tricks to using them opens up tons of options and possibilities. Thanks for sharing your wisdom and knowledge :))
reply
Dashrath
Hey,
It's a very well collated video for the return of references. I am a fan of you and the way you explained not even here, also in udemy courses is amazing and effective. Now the choice of fastest method would become easy post this video..well done
Thanks for sharing!
I did your -Dashboard in one hour course- and found superb content that can be used in different ways.
Great Leaning!!!
Keep Sharing :)
reply
Hey,
It's a very well collated video for the return of references. I am a fan of you and the way you explained not even here, also in udemy courses is amazing and effective. Now the choice of fastest method would become easy post this video..well done
Thanks for sharing!
I did your -Dashboard in one hour course- and found superb content that can be used in different ways.
Great Leaning!!!
Keep Sharing :)
reply
Chris
Awesome video as per usual. Is there an advantage to creating a named range reference for a table with a formula over formatting as a table to auto create a named range?
Also, one helpful adaptation for the index / match combo has been using - as AND operator to select on multiple column or row criteria. For example selecting data for a person in a particular year and location.
reply
Awesome video as per usual. Is there an advantage to creating a named range reference for a table with a formula over formatting as a table to auto create a named range?
Also, one helpful adaptation for the index / match combo has been using - as AND operator to select on multiple column or row criteria. For example selecting data for a person in a particular year and location.
reply
Iv-n
Complete and in-depth video of these functions and their respective analogies and differences.-
A single point:- The functions IFS, SWITCH (and also MAXIFS, MINIFS, CONCAT and TEXTJOIN) they appeared in the 2016 version and logically were remained in the 2019 version.
Exceptional level of explanation. Thank you Mynda!
reply
Complete and in-depth video of these functions and their respective analogies and differences.-
A single point:- The functions IFS, SWITCH (and also MAXIFS, MINIFS, CONCAT and TEXTJOIN) they appeared in the 2016 version and logically were remained in the 2019 version.
Exceptional level of explanation. Thank you Mynda!
reply
Advance
Nice video mam , I use mostly match function for returning functions.
And sometimes count and countif.
Very good guide -.
I also try to explain few tutorial but in other languages, hope you.will share your valuable feedback.
reply
Nice video mam , I use mostly match function for returning functions.
And sometimes count and countif.
Very good guide -.
I also try to explain few tutorial but in other languages, hope you.will share your valuable feedback.
reply
darryl
Hi Mynda!Great Tutorial On These Functions,I Use The XLOOKUP Function Quite A Bit With Work.Would Agree With REDA Below In That Every Excel User Should Learn These Functions...Thank You So Much :)
reply
Hi Mynda!Great Tutorial On These Functions,I Use The XLOOKUP Function Quite A Bit With Work.Would Agree With REDA Below In That Every Excel User Should Learn These Functions...Thank You So Much :)
reply
Rodney
Great summary of these functions. I like you present. If your data structure is such that you can use whole column and/or whole row references (A:A)(1:1), does that significantly slow down Excel?
reply
Great summary of these functions. I like you present. If your data structure is such that you can use whole column and/or whole row references (A:A)(1:1), does that significantly slow down Excel?
reply
Add a review, comment
Other channel videos
