Indirect function

[PeterW]

Hi

In cell A30, I have inserted the following formula, =sum(A20:G29).

If I change the formula in cell A30, I would like the formula in cell
B30, C30 & D30, to update as well, but with their relative colum
references. ie I would like cell B30 to sum B20..B29.

I know that I could simply copy the formula across into cells B30 t
D30, but I want to avoid this method.

By way of example, I have tried the following formula in cell B30,
=INDIRECT(SUBSTITUTE(A30,COLUMN(A30),COLUMN(B30)),FALSE)

It doesn't work because I don't think the Indirect formula is able t
interpret a formula.

I would appreciate any assistance.

Regards
Pete

 

[Debra Dalgleish]

You could enter the range reference in a cell, e.g. H1, and refer to
that in all the formulas.

For example, in cell H1, enter: A20:A29

In cell A30, enter: =SUM(OFFSET(INDIRECT($H$1),0,COLUMN()-1))
Copy the formula across to D30

If you change the reference in cell H1, all the results will change.

 

[PeterW]

Hi again

I have just realised that my approach to this problem is flawed becaus
I'm mixing R1C1 references with A1 style.

However, I do still need to achieve a solution to the overall problem
I have attempted to write a User Defined Function, but still no luck.

All help is appreciated

Regards
Pete

 

[Ragdyer]

Will this do?

Enter row references in A31 and A32, and use these formulas in

A30
=SUM(INDIRECT("A"&A31&":G"&A32))

B30
=SUM(INDIRECT("B"&A31&":"&"B"&A32))

C30
=SUM(INDIRECT("C"&A31&":"&"C"&A32))
....etc.

 

[PeterW]

Thanks Debra
That certainly does work, however if I insert a new row into th
formula range, I would like the formula to change dynamically. ie. i
should become A22:A30

Is it possible to have this change dynamically?



Thanks Ragdyer
I have considered your solution, however as I will also be using othe
formulas to the one I included in my question this won't be workable
For example, I want to include formulas such as =sum(a2,a4,a5,a8) and
don't want to insert multiple columns to store this referenc
information

 

[Frank Kabel]

Hi
no this is nearly impossible with this approach. As you store the range
information as Text Excel won't change this automatically. This is a
drawback of INDIRECT.

 

[PeterW]

Thanks Frank

You say 'nearly' impossible.. does that mean there is a work around i
I use another approach??

I am basically trying to use one cell to store the source formul
(which changes dynamically) that can be referenced by other cells.

Should I be considering some type of User Defined Function??

Regards
Pete

 

[Frank Kabel]

Hi
maybe you can explain how you set the range. That is how do you decide
whcih range to use. Maybe there're other ways than using INDIRECT

 

[PeterW]

Thanks again Frank, I will attempt to provide further clarity to m
previous posts below.

Background:-

I have a worksheet that produces a set of financial statements fro
another worksheet that contains the raw 'trial balance' of numbers.

The financial statements present the general ledger in a format that i
more understandable to the reader.

What I am trying to achieve is as follows:-

I would like to have one formula that can be copied to every relevan
cell on the Financial Statements worksheet. The part of the formul
used will depend on the categorisation of the row as either a 'blank'
an 'account' or a 'sub-total'.

My approach:-

On the Financial Statements, I am separately identifying each row a
either i) a 'blank'; ii) an 'account' - a sumif formula (that looks u
the data on the trial balance worksheet) or iii) a subtotal 'formula'.

If the row is identified as a subtotal formula, I would like to be abl
to insert the formula into a cell, so that all the cells on that row t
the right use this formula to calculate the subtotal.

The formula is only likely to be a sum function. However the su
function MAY INCLUDE NON ADJACENT CELLS. For example, the formula coul
be sum(G10:G20) OR sum(G25,G30,G33,G35)

As an example of the formula that would be copied to every cell of th
Financial Staements worksheet is as follows:
=if(A10="blank","",if(A10="account",sumif('Tria
Balance'!A10:A1000,B10,'Trial Balance'!B10:B1000),if(A10="formula"
xxxxxxx)))

The xxxxxxx is the bit I am struggling with.

Hope this explaination is a little clearer

Pete