Named Ranges in Microsoft Excel
Names ranges can be very useful. Some people argue that point, whether using named ranges in VBA or formulas. The most common problem regarding names ranges, everyone agrees, is user ignorance of their existence in the application.
Create a Named Range
Select a range of cells using the Shift key, Shift+Ctrl, or Ctrl keys (yes, you can select non-contiguous ranges, like A1, A3, and A5). Then hit InsertàNameàDefine and type a name.
The name you choose cannot have spaces and cannot begin with a number. Certain other symbols are also not allowed.
Alternatively, you can type or change the name right into the Name box (indicated by the red arrow), as shown below. You can name cells, graphics, textboxes…just about anything.
Tip: You are not warned when you overwrite a named range.
Hyperlink to a Named Range
You can insert a hyperlink to a named range by inserting a hyperlink to a workbook, and simply adding #NamedRange to the end of the hyperlink. For instance, C:\Documents and Settings\YourName\Desktop\data.xls#Master, where Master is the named range you created.
Find the Range to Which a Name Refers
Use the Name box. It’s just above cell A1. Hit the dropdown, and choose a name. You’ll be taken to that range.
Another method: Hit InsertàNameàDefine. Select the name, and look at the bottom of the dialog to see the range to which it’s been assigned.
Common Problems
John creates a workbook that uses named ranges to create dropdowns. John is out sick. You decide you’re going to figure out how to add a value to the dropdown list. You hit DataàValidation, and find that it’s using a list called “fruits”.
You find the list of fruits on Sheet2, and add bananas to the bottom of the list. It doesn’t appear in the dropdown. Why?
When you’re looking for a named range, hit the Name drop-down box, to find the named range, and it becomes highlighted. The Name drop-down box appears just above column A, which is shown in the graphic above. Also shown is the selected named range, cells A2 through A6. To rectify the situation, simply select cells A2 through A7, and hit InsertàNameàDefine and type “fruits” again.
A very cool use for a named range is that you can use one cell for your formula values. Suppose you want to mark up a cost to determine your price. Your costs are all in column B. If you copy the formula shown below, each row’s formula will change. The reference to F1 will change to F2, then F3 as you copy it down each row.
Instead, your could use absolute references, and type the formula in C2 as:
=B2*$F$1
Or, you could choose cell F1 and InsertàNameàDefine and type “markup”. Then, you can write the formula in C2 as =B2*Markup. When you copy it down, only the B2 reference will change for each row, and your markup will always point to the appropriate cell. This is particularly nice when you’re jumping around to different spreadsheets, and also keeps you from having to remember what cell the markup value was in.
A hoot to be sure…
Definitely an arithmetic post. You add trouble, subtract pleasure, divide attention, and multiply ignorance….
Wikia…
Wika linked to this place…
marketing online…
[...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]…
adult social networking…
[...]the time to read or visit the content or sites we have linked to below the[...]…
TV Lamps…
The Truth behind Television Replacement Lamps….
Another Website Mentions Your Website…
[...]please visit the sites we follow, including this, the waу it represents our picks from the web[...]…
Great Clips Coupons…
[...]right here are a handful of web links to websites which I connect to since we believe they’re definitely worth browsing[...]…
Latest news from all around the world, you need to see this !…
[...]blow are some sites that we think it will be helpful and fun to read[...]…
Star Wars Fan Blog Mentions Your Website…
[...]the a chance tо read оr look at thе content or sites we've connected with bеlоw the[...]…
Sites we Like……
[...] Every once in a while we choose blogs that we read. Listed below are the latest sites that we choose [...]……
Online Article……
[...]The information mentioned in the article are some of the best available [...]……
Blogs ou should be reading…
[...]Here is a Great Blog You Might Find Interesting that we Encourage You[...]……
buy damansara house…
[...]you will find information that’s relevant to you if you want to buy damansara house[...]…
Websites we think you should visit……
[...]underneath are plenty of totally non-related sites to ours, however these are surely worth going over[...]……
Garmin1490t.com…
[...]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[...]…
Gems form the internet…
[...]very few websites that happen to be detailed below, from our point of view are undoubtedly well worth checking out[...]……
Found=> New Backlink…
[...]I аm looking both fоr blogs that offer unbiased, balanced commentary оn all issues or blogs who have a liberal or left-wing slant. Thank уоu.. [...]…
Angry Birds Online…
[...]the following are a few urls to web-sites which I connect to seeing as we think these are worth visiting[...]…
You should check this out…
[...] Wonderful story, reckoned we could combine a few unrelated data, nevertheless really worth taking a look, whoa did one learn about Mid East has got more problerms as well [...]……
best webhosting…
[...]This is a nice addition for our blog thanks[...]…
Related websites……
[...]check out this great blog which you might find quite interesting[...]……
Basketball Drills…
[...]the time to read or visit the content or sites we have linked to below the[...]…
Digg…
While checking out DIGG yesterday I found this…
Informative and precise…
Its difficult to find informative and precise information but here I noted…
Found=> Link To Your Post…
[...]I hаve accumulated a number of the more valuable blogs within mу list here[...]…
outer banks nc rentals…
[...]below you’ll find the link to some sites that we think you should visit[...]…
Ipad Stylus…
[...]below are a handful of listings to web sites we connect to seeing as we feel they’re truly worth checking out[...]…
New ideas ? Great Ideas ? Best Ideas ?…
[...]below you’ll find the link to some sites that we think you should visit[...]…
So Funny…
My false teeth just fell into the kitchen sink because I was laughing so much while reading this article….
News info…
I was reading the news and I saw this really interesting topic…
Coolest news on the planet, you have to see this !…
[...]the time to read or visit the content or sites we have linked to below the[...]…
Websites worth visiting…
[...]usually posts some very interesting stuff like this. If you’re new to this site[...]…
Websites worth visiting…
[...]here are some links to sites that we link to because we think they are worth visiting[...]……
Swell…
This is so swell….
Recommeneded websites…
[...]Here are some of the sites we recommend for our visitors[...]……
Sites we Like……
[...] Every once in a while we choose blogs that we read. Listed below are the latest sites that we choose [...]……
Nice Post…
[...]I saw this really great post today. I just linked back to it from my site. Thanks![...]…
[...] Names ranges can be very useful. Some people argue that … [...]
Title…
Function alexa rank for the website…