Re: Retrieving a "Range" in Excel for storage in a Database
Re: Retrieving a "Range" in Excel for storage in a Database
- Subject: Re: Retrieving a "Range" in Excel for storage in a Database
- From: David Wignall <email@hidden>
- Date: Thu, 09 Jul 2009 16:03:04 +1200
- Thread-topic: Retrieving a "Range" in Excel for storage in a Database
on 9/07/2009 3:46 PM, Paul Berkowitz at email@hidden wrote:
> On 7/8/09 9:47 AM, "Stan Cleveland" <email@hidden> wrote:
>
>> One more tidbit--you can give the same cell or range more than one name, any
>> and all of which can be used to reference the data therein. Also, the name
>> property reflects the name function accessed in the GUI from the menu
>> command "Insert > Name > Define...".
>
> Just out of interest, why does anyone want to name a cell? If it always
> refers to a fixed cell, such as F243, what's hard about referring to it by
> address, as cell "F243"? There must be a reason, I guess, but I can't think
> what it might be. Easier to remember? You'd better remember it *exactly*
> then. I would find that easier to confuse than the address.
1) Human readable formulas
2) Names can refer to ranges thus =SUM(Benefits)
3) Names are absolute references by default - makes things easier for lower
level users
4) Names exist at workbook level by default. In the above example the named
range Benefits could exist on any sheet in the book. This again makes
formula creation easier for low level users.
5) Dynamic range names e.g Benefits is defined as
=OFFSET($A$1,0,1,COUNT($B:$B)). This means that the range encompassed by
Benefits is as many cells in height as there are cells in column B that
contain data. You can use this to control how many cells functions such as
SUM actually have to evaluate thus speeding calculation. Also useful with
Charts e.g. chart only the latest 6 months
Then you can get tricky. I haven't checked recent versions but it used to be
that the only way you could use the list option in Data Validation where the
list was on another sheet to where the validation rule was being applied was
to use a named range. Have a sniff around Chip Pearson's site
<http://www.cpearson.com/Excel/MainPage.aspx> for many useful things done
with Names.
Final comment about remembering Names. There is Paste Name in the menu.
Keyboard shortcut in Win Excel is Shift-F2 (I think - it's been a while).
Never found a Mac shortcut for the same dialog but I didn't actually look
too hard.
--
Dave
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden