Hi Peter
I know Biff has an absolute abhorrence of named Ranges <bg!!!
I would agree that it can be taken too far, with naming of lots of
individual cells, but a few like VAT, TAX etc are absolutely fine.
Again, putting VAT in a cell on a sheet, and then naming the cell, makes
it easily visible for audit, as opposed to placing the value in the
Refers to part of a Name, and saves having to go through lots of Names
to see the values.
But named ranges, especially Dynamic ranges, in my opinion are quite
different, and I use them extensively for many reasons.
If you make the range name, the same as the column heading, then nothing
can be clearer (in my mind) than
Invoice Date <= xxxx
Sum(Sales Amount)
Sum(Net Amount)
etc.
For any individual named values, like VAT and TAX etc., I put these all
on one sheet (hidden), with clear names alongside the named cells
containing the values.
On the same sheet, I do InsertNamePastePaste List so that all of the
named ranges can be clearly seen with their name and reference. This
list can be annotated where necessary.
I use the same convention for all ranges
I always have a named range called lr (Last Row), which is then used in
all ranges
lr=COUNTA($A:$A)
Column A, invariably is a column that will always be populated, hence
that is what I inevitably use, but it could be based upon any other
column if required.
Then,the following might be the case
Invoice date=$A$2:INDEX($A:$A,lr)
Sales Amount=$G$2:INDEX($G:$G,lr)
and so on
By using a common counter, you ensure that all ranges are of equal
length. The formulae, are short and easy and don't use the volatile
Offset function.
It takes but a minute to scan ones eye down the list to see that it is
both consistent and accurate, and from an audit point of view, having
satisfied yourself that one range is calculated correctly, then all of
the others will behave the same.
When it comes to VBA, then referring to named ranges, rather than hard
coding ranges, makes it much easier to maintain the code.
Even using Named formulae, can be very beneficial and can make use of
the valuable Evaluate function that is not otherwise available to
worksheet formulae, but is available in VBA. Provided that the name and
formula are in the list on the hidden sheet as mentioned earlier,along
with a description of what it does, then any auditor can see and test
the formula once, and know that every occurrence within the worksheet
will behave the same.
No, Peter, you stick with your use of named ranges and providing you
follow simple logical guidelines, any auditor "worth his salt" should
have a far easier time in auditing your work, not a more difficult
experience.
I have some simple code that will create a list of dynamic ranges very
easily. It can be viewed on Debra Dalgleish's site
http://www.contextures.com/excelfilesRoger.html
and choose
Names -- Create Dynamic Ranges With a Macro
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier
pbart wrote:
Biff, Bill
Something of a mixed vote on the value of named ranges then. The audit
issue is one that I had not really considered beyond 'clear to understand is
good'. Although I am reasonably well aware of software quality issues
applied to coding, I was under the impression that spreadsheets tend to 'slip
under the radar' as far as quality is concerned. Apparently this is not
always the case, otherwise your tongue in cheek reference to 'job security'
would not apply.
In answering one question others seem to be raised such as the idea of
'cluttering up your file with heaps of helper cells'. This too is something
I do routinely, taking the view that a series of steps one could explain to a
10 year old are better than commiting ones life's work to one 'master formula
of immense ingenuity'.
Then I would really mess it up as far as audit is concerned by hiding all
the helper cells along with any intermediate processing worksheets, removing
all gridlines and cell boundaries other than those inviting user input and
finally semi locking the whole thing down with protection - usually <blank
password though. Seems I would either be the perfect client or a complete
nightmare according to your work objectives on the day!
Once more, thanks for your comments .
Peter
"T. Valko" wrote:
One issue is the use of named ranges.
I observe that I rarely use expressions such as
= L9 * "Master sheet"!$F$15 / 100 ,
prefering instead to name all variable and arrays
= Sales * VATrate / 100.
This is just a personal preference...
If I have to audit your file it's going to take me twice as long (job
security!!!) because the first thing I have to do is look for all the named
ranges!
If I see a formula like this:
= L9 * "Master sheet"!$F$15 / 100
I instantly know where the referenced data is located.
If I see a formula like this:
= Sales * VATrate / 100
Well, I have to go look for it!
I rarely use named ranges. Ususally, only when the name refers to another
sheet and/or the range is referenced in a long complex formula.
Just my opinoin...
Array formulas are your friend!
In most forums like this one, too many people seem to discourage the use of
arrays by "scaring" the user. Some things can only be done with arrays
(unless you want to clutter up your file with heaps of helper cells). As a
general rule, it depends on the size of the file, the number of calculations
and how long it takes those calculations to execute as to how freely you
should use arrays.
Some folks may think that a file that takes 20 minutes to calculate is
outrageous. Well, it depends on what the file is doing!
--
Biff
Microsoft Excel MVP
"pbart" wrote in message
...
As a self taught Excel user I appear to have developed a personal style
that
differs from most examples of spreadsheets that I see.
One issue is the use of named ranges. I observe that I rarely use
expressions such as
= L9 * "Master sheet"!$F$15 / 100 ,
prefering instead to name all variable and arrays
= Sales * VATrate / 100.
Would professional users regard this as good practice or are there
drawbacks?
To take this further, I tend to use array formulae wherever appropriate.
Again I find
{=spectrum*displacement^2} ,
in every row, far more readable (and hence less prone to error) than
="standard spectra"!$B13 * $C15^2
with the usual variation row by row.
Here I can see a downside though. Whilst array formulae do discourage end
users from tampering with the workbook, they are a pain to resize to
incorporate more invoice records or increased frequency ranges.
Again I would be interested in the opinion of others.
.