SECRET Excel Named Range Shortcuts to Save Time

Today I had seen a formula. =vlookup("value1"&"value2",'Pivotsheet (S)'!c2:m30,2,0)

May I know, why we use (S) reference along with the sheet name in which we will have 2 pivot tables which are formed by the same source data.
As I seen this formula will combine value 1 and 2 and return the value. But please tell me how this reference fetch data from pivoted cache and return the result.

Wow - I just chanced across your channel today, and that little tip about just using the "!" in front of a name is priceless! A while ago I was adding some sheets to a workbook that used a lot of names, and ran into many issues with them. This at least solves one of them. The other big issue I had was that if I copied one of my worksheets to another workbook it had a tendency to drag along all of the names from the original workbook as well. I ended up writing some macros to clear them out, but it was a right pain, and made me cautious about using names since then. Maybe I will give them another chance, especially since I recently upgraded from Excel 2013 to MS 365, and am now starting to learn about much of the very useful new functionality it provides.

Many thanks for your videos. I have subscribed, and will be watching a lot more of them!

Thanks for this video! I have a similar situation presented in the scope and shortcut section of the video.

I have a workbook for invoicing partners. It has a summary sheet that refers to the subtotals from each additional sheet. Each asset is tracked on a separate sheet, and can be as many as 20 or more assets/sheets. Would using Sheet level scope to define the subtotal columns be the best option for the summary sheet?

As it is now, the subtotal columns are just referenced by their cells and can grow and shrink each quarter. I have to manually adjust the sum formulas on the summary sheet for each asset.

Each quarter, a asset is deleted or added, where I just create a copy of the last sheet. If I use Sheet level scope, will it copy the defined name to the new sheet?

Can we use named cells in conditions of countif for example? I'm trying to write something like this: countif(grades_range,">pass_score") where pass_score is the named cell; but it always gives a zero.

Excellent

How to use named range in sumproduct? Please help

Thanks! Great video

Thank you for your very clear explanation and tricks to defining Names. How can I "print" the list of "Defined Names"?

Very good - clear and concise.

Is her accent British or Australian.? I am confused!

Thank you for covering name scope. So many videos cover just naming a cell or range, but hard to find one that covers everything! Super simple and easy to follow along. Cheers.

That last tip is cool, where using a blank sheet name (just the '!') you get a "this sheet" style reference.

eloquent explanation of the different paths of name creation. didnt know you could create a variable name without first choosing a cell with value, sweet !!

How do I add suffix in sequence 1,2,3,4,5 etc and when renumbered again when the column name change for example
Apple is 1 banana is 2 and Orage is 3, apple again is 2

You are great! Thank you for your work!

Genius

I have a spreadsheet using named ranges in formulas. The formula returns 0 to the cells, yet the formulas evaluate correctly (when I use F9 in the formula bar or use the Evaluate Formula tool). What am I missing? I am current with my M365 subscription.

Thanks a lot. Your videos are always a great help to optimize my job☺️

Hi Mynda, is there any way to hide some cells data in printing without hiding the row of those cells?
Thanks

I had never given much thought to scope when working with named ranges. I appreciate your clear explanation with examples. That last bit using "=!B2" was fantastic!

Hi, how I can use the named range in VBA macros? Thank you. Greetings from Rome, Italy.

Thanks Mynda, I always learn something new from your channel. At last in this video, Removing sheet name infront of ! in formula and using Same Name Range with different values as per different sheets was great idea.

last trick was awesome.

Very useful video! Thanks Mynda for some cool tips on named ranges, which are going to help a lot! 😊👍

It probably not related with the topic. I have problem when locked some data, it could'nt be filtered. Can Excel lock some data meanwhile we still able to filter data we want to filter. Thanks for your response

谢谢😊

Your videos have helped me so much! Thank you for sharing all that you do =)

Good one during complex calculations

I have been using defined names quite a bit lately and it is such a great tool. But, there is always something to learn. There were certainly some good tidbits in here. Thank you.

super

Really interesting stuff, thank you !!

Very clear explanation. Thanks for the solution to copying worksheet and creating duplicate names. Very helpful. Thanks Mynda

Thank Mynda. I was unaware of the various options of named ranges. As powerful as they may be, it seems there is an inherent risk with using the correct named rNge and/or updating values correctly, particularly if the workbook is shared with other users.
Thanks for showing the options nonetheless.

Thank you Mynda. It's good to be reminded, especially as I clearly don't use defined names enough. But the thought of models using Named Constants fills me with dread!

Very good explanation, I know should use the feature more, but found when you start using tables and pivot tables name range management can become a bit of nightmare.

Thank you for another great video. I needed this a few days ago so i came to your channel. Helpful and clear as always.
Ty

Even the videos you think are beginner or intermediate I find useful. I am always picking something up. Thank you!

Vavvv, =!$B$2 is very very hidden tips Mynda! Thank you for sharing.
And, I would liked to see this name ranges including Hyperlink because Hyperlink likes named ranges to navigate between sheets and ranges.
and,
There is an unknown screet tip is that when defining named column and row ranges at the same time for an specific range, you don't need to use index and match or Vlookup or Xlookup formulas. Instead, just use defined named ranges like = A1:E1 B2:B5 (intersection of ranges give us the desired value )
So, name manager and using named ranges in differeten type of the application in excel is either saving time or helping data management system of excel (less consuming time)

Great tips Mynda! Thanks!!

As allways much usefull, thanks
And funny as it is, just today I show a college the function, but you Are much better to give the hole picture

Excellent tutorial, as always!

I have used Names to help with formulas in a person spreadsheet which shows when bills are due and allows for weekends. e.g =IF(OR(MortgageWeekday,MortgageWeekend),MortgageAmount,0) where MortgageWeekday and MortgagWeekend have formulae that look up a date, see if it is a weekday or weekend and also if it matches the due date. What I can't figure out is how to allow for bank holidays within the formulae.

Great tutorial! Thank you Mynda!!!

Thanks Mynda

the last one... makes me swallow my whole cup of coffee in one go !

If you want to create sheet-scoped named range with Name Box, you need:
1) if sheet name does not contain spaces, enter: Sheet2!MyRange (i.e. sheet name plus "!")
2) if sheet name does contain spaces, enter: 'Sheet 2'!MyRange (note single quotes around sheet name)