Making dynamic formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Snoopy - 10 Nov 2006 10:16 GMT

Hey guys
I have this idea of making my summary useing dynamic range-reference.

EXAMPLE
Monthly amounts is placed i cell A4 to L4 (Jan...Dec)
My issue is to make the formula: =SUM(A4:?4) dynamic to respond to an
index from 1 to 12 and by this re-construct itself.
The months in indexed like this:
January (first month i the summary) is indexed as value 1 in cell A1
The last index in the summary is (ex) 10 representing October is
indexed in cell B1

Index 1 construct the formula =SUM(A4:A4)
Index 2 construct the formula =SUM(A4:B4)
Index 10 construct the formula =SUM(A4:J4)
and so on.

So far I have made a "formual" by using the =ADRESS formula to create
the expression of the cellreferences that marks the range.

LIKE THIS
="=SUM("&ADRESSE(A1;A1)&":"&ADRESSE(A1;B1)&")"
This expression is perfectly dynamic. A copy/paste special-value make
the cell content
=SUM($C$4:$J$4)

BUT
This appears as a text and NOT as a formula!

MY QUESTION IS
How do I get further making this expression work as a real dynamic
formula
I have tried to use replace-function in various way - without suksess.

Please give me a pleasant weekend by guiding me throug this problem
Regards Snoopy

Reply to this Message

Niek Otten - 10 Nov 2006 10:32 GMT

Hi Snoopy,

Look in HELP for the INDIRECT() function

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hey guys
| I have this idea of making my summary useing dynamic range-reference.
[quoted text clipped - 32 lines]
| Please give me a pleasant weekend by guiding me throug this problem
| Regards Snoopy

Reply to this Message

Snoopy - 10 Nov 2006 10:49 GMT

Thanks -
I'm trying to catch your point in using the INDIRECT-formula, but can't
see how this will effect on my problem...
May be you have seen something beyond my understanding? In that case
please tel me more about it.

Regards Snoopy

Niek Otten skrev:

> Hi Snoopy,
>
[quoted text clipped - 42 lines]
> | Please give me a pleasant weekend by guiding me throug this problem
> | Regards Snoopy

Reply to this Message

Arvi Laanemets - 10 Nov 2006 11:55 GMT

Hi

=SUM(OFFSET($A$4,,$A$1-1,,$B$1-$A$1+1)

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> Hey guys
> I have this idea of making my summary useing dynamic range-reference.
[quoted text clipped - 32 lines]
> Please give me a pleasant weekend by guiding me throug this problem
> Regards Snoopy

Reply to this Message

Bob Phillips - 10 Nov 2006 11:58 GMT

=SUM(INDEX(A4:L4,1,A1):INDEX(A4:L4,1,B1))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Reply to this Message

Lori - 10 Nov 2006 22:06 GMT

There are several ways to evaluate a collection of formulas like yours:

i) Select a column then Data > Text to columns > Finish (assuming
tab-delimited default)

ii) Choose Edit > Replace with the options Find: "=", Replace with: "="
to force a recalculation of all formulas

iii) Select columns or whole sheet and copy to office clip board
(Ctrl+C twice) then paste back using icon (for this method you do not
need to paste values before but it needs enough data to be copied as
text - about 2K)

Your approach does have the advantage that it does not make use of
volatile or dynamic ranges like some of the other suggestions which can
lead to the save changes prompt appearing by default.

Reply to this Message