Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Sheet Level Named Ranges

| | Information Helpful? Why Not Donate.

Worksheet Level Named Ranges/Excel Worksheet Named Ranges

Back to Excel Formulas Index << Back to Excel Named Ranges

Worksheet Level Excel Named Ranges

Normally, when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!

Assume we have a Workbook with 3 Worksheets. These 3 Worksheets are simply named Sheet1, Sheet2 and Sheet3. We want to have a named range called MyRange (can be any legitimate name) that will refer to the range Sheet1 A1:A10 when on Sheet1, Sheet2 A1:A10 when on Sheet2 and Sheet3 A1:A10 when on Sheet3. Here is how;

1) Activate Sheet1
2) Select the range A1:A10
3) Click in the Name Box. This is to the left of the Formula Bar below File Edit on the Worksheet Menu Bar.
4) Type Sheet1!MyRange
5) Push Enter

Do the same for Sheet2 and 3 using Sheet2!MyRange and Sheet3!MyRange

Now activate any sheet and click the drop arrow on the Name Box. You should see only one occurrence of the name MyRange. Select this and you will be taken directly to the range A1:A10. Now activate any other sheet and do the same. You will always be taken to the range A1:A10 of the active sheet.

The reason we can do this is because we preceded the name with the sheet name followed by the ! (exclamation mark). If you go into Insert>Name>Define you will note that you only see one name and again that name is the one that refers to the current active sheet.

If your Worksheet name includes spaces you cannot simply use Sheet1!MyRange. What you must use is 'Sheet 1'!MyRange. In fact you can use the single apostrophes with a Worksheet name with no spaces. It is a good idea to always use the single apostrophes when referring to Worksheet name as it covers all bases.

<< Back to Excel Formulas Index << Back to Excel Named Ranges

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft