Making dynamic formulas

[Snoopy]

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

 

[Niek Otten]

Hi Snoopy,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|

 

[Snoopy]

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:

 

[Arvi Laanemets]

Hi

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Lori]

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.