Excel Relative Named Ranges - A Secret of Excel Pro Users - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
BlueFish
Very good! Thank you! Your videos I very clear and useful.-
But I want to use (insert) the data from a cell with a specific name range into another cell but to come together with the formatting ( font size, back color,, or frame).-
For instance,, I have a cell A7 with a letter inside -V- and range name -tt- and frame and red background color. And I go in cell K9 and type tt to appear V with frame and the same back color but not. Appear only V if I type +tt without format.-
I saw this effect in one Excel template (Employee Absent Schedule) but cannot understand how they did this.-
Could you advise me on how I can do this, please?
reply
Very good! Thank you! Your videos I very clear and useful.-
But I want to use (insert) the data from a cell with a specific name range into another cell but to come together with the formatting ( font size, back color,, or frame).-
For instance,, I have a cell A7 with a letter inside -V- and range name -tt- and frame and red background color. And I go in cell K9 and type tt to appear V with frame and the same back color but not. Appear only V if I type +tt without format.-
I saw this effect in one Excel template (Employee Absent Schedule) but cannot understand how they did this.-
Could you advise me on how I can do this, please?
reply
Sajimon
Thank you for sharing your knowledge. It's a great opportunity and really glad to follow your videos. I have a request. Please give me better way to find out the solution without using VBA or Macro. I just want to prepare an order sheet based on the ingredients given for each menu by selecting or putting total quantity of portioning of multiple menu selected. I just need a summarize report of ingredients based on the selection and total portioning quantity. If you can find a solution for this, please share it with us as soon as possible. Awaiting for your reply. Thank you.
reply
Thank you for sharing your knowledge. It's a great opportunity and really glad to follow your videos. I have a request. Please give me better way to find out the solution without using VBA or Macro. I just want to prepare an order sheet based on the ingredients given for each menu by selecting or putting total quantity of portioning of multiple menu selected. I just need a summarize report of ingredients based on the selection and total portioning quantity. If you can find a solution for this, please share it with us as soon as possible. Awaiting for your reply. Thank you.
reply
Bart
Thank you Mynda, great video. I never new this. I love working with names, use it a lot. I always use -create from selection- but the problem is that you can not change the scope of the name. If you have a lot of sheets and for every sheet you have something like -turnover- this is a hurdle. Any idea how you can use -create from selection and change the scope? Btw: your sparkline solution was sophisticated and relevant for this video, but I would use the insert table feature, works easy!
reply
Thank you Mynda, great video. I never new this. I love working with names, use it a lot. I always use -create from selection- but the problem is that you can not change the scope of the name. If you have a lot of sheets and for every sheet you have something like -turnover- this is a hurdle. Any idea how you can use -create from selection and change the scope? Btw: your sparkline solution was sophisticated and relevant for this video, but I would use the insert table feature, works easy!
reply
SABYASACHI
Hey thanks for the video but can you tell me, suppose you are using sum formula but you also want to include new cells added to the worksheet in that same sum formula. i mean to say suppose you are calculating sales value from jan to march ...then next month april will be added so we need to include april month values also in the sum formula so...how to do that...Pls help me out
reply
Hey thanks for the video but can you tell me, suppose you are using sum formula but you also want to include new cells added to the worksheet in that same sum formula. i mean to say suppose you are calculating sales value from jan to march ...then next month april will be added so we need to include april month values also in the sum formula so...how to do that...Pls help me out
reply
Ian
Brilliant! Thanks, Mynda.
I didn't know you could create relative named ranges! I use named ranges a -lot- in my work where I need to set up a large number of formulae (sometimes in conjunction with INDIRECT, if used sparingly) and to make it easier to audit formulae. I can see how (sometimes) making them relative would be very useful.
reply
Brilliant! Thanks, Mynda.
I didn't know you could create relative named ranges! I use named ranges a -lot- in my work where I need to set up a large number of formulae (sometimes in conjunction with INDIRECT, if used sparingly) and to make it easier to audit formulae. I can see how (sometimes) making them relative would be very useful.
reply
Geert
Great video, as always! :-)
Revisiting this video, I want to add that there-s one thing I would like to be able to do, and that is: to be able to change the scope of the defined name after its creation. Excel doesn-t let us - the only way is to delete it and define it anew.
reply
Great video, as always! :-)
Revisiting this video, I want to add that there-s one thing I would like to be able to do, and that is: to be able to change the scope of the defined name after its creation. Excel doesn-t let us - the only way is to delete it and define it anew.
reply
Star
I want to use a named range in an excel Table. I have many tables that use the same data and I want them to all update automatically or dynamically. When I try this I get an error message that -you cannot use a named range in a table-. How do I accomplish this?
reply
I want to use a named range in an excel Table. I have many tables that use the same data and I want them to all update automatically or dynamically. When I try this I get an error message that -you cannot use a named range in a table-. How do I accomplish this?
reply
Dagwould
In the dynamic range related to the sparklines table (at about the 9 minute mark), instead of extending the range to accommodate growth, could you instead calculate the last position in the range using offset and counta?
reply
In the dynamic range related to the sparklines table (at about the 9 minute mark), instead of extending the range to accommodate growth, could you instead calculate the last position in the range using offset and counta?
reply
Jim
Great tip & lesson, as always, Mynda. Never tried this consciously before, but noticed dynamism in named ranges from time to time. Never understood why or how to control it. Now I do. It will be very useful. Thank you!
reply
Great tip & lesson, as always, Mynda. Never tried this consciously before, but noticed dynamism in named ranges from time to time. Never understood why or how to control it. Now I do. It will be very useful. Thank you!
reply
Shady
Great video, I never knew you could use named ranges in this way!
Just one question, would the extensive use of Relative Named Ranges in a 15MB size file slow down the calculation to any noticeable degree?
reply
Great video, I never knew you could use named ranges in this way!
Just one question, would the extensive use of Relative Named Ranges in a 15MB size file slow down the calculation to any noticeable degree?
reply
Add a review, comment
Other channel videos
11:15
5:53
