Hello all,

I have a question for the Excel gurus which read these groups. I have had
a look through the archives and as far as I can tell, there is no way to
do what I want so I thought I'd ask here.

Take the simple case of calculating forecast sales; if the market for widgets
is x million per year and your forecast market share in that year is y%,
then forecast sales for a given year = x * y

Obviously it is possible to set up, for example, a named cell "market_share"
and another "market_size" and then write the formula in a third cell for
sales which has the formula
"=market_share*market_size" to calculate sales. This is clearly a very
readable formula which someone else looking at the worksheet would easily
understand. The drawback of this is that it would only work for one year.
Changing the names (i.e. to add the year,so that market_share for 2005 would
be market_share_2005) would be unworkable for any large sheet (and you could
not drag the formulas across into a new column to add another year).

Is there some way to make named ranges behave like arrays in programming
languages? It would be great to have a series of named columns (i.e. 2004,
2005, 2006) and then the formula could look like:
sales(year) = market_share(year) * market_size(year). The formulas would
be human readable and the formulas should still be able to be copied to new
columns.

As far as I know this is impossible but I'd be happy to hear any suggestions
on how this could be done.

Cheers,
Shane