Formula rref

[brettvba]

Ive got a formula with a range
eg =sum(a2:a5)

what im wanting to do is have the end row value in a cell eg C5 with a value of 6.

and reference that in the formula eg
=sum(a2:a&C5)
but that doesn't seem to work any Ideas?

Brett

 

[Mark W.]

On 2002-11-06 18:08, brettvba wrote:
Ive got a formula with a range
eg =sum(a2:a5)

what im wanting to do is have the end row value in a cell eg C5 with a value of 6.

and reference that in the formula eg
=sum(a2:a&C5)
but that doesn't seem to work any Ideas?

Brett

=SUM(INDIRECT("A2:A"&C5))

 

[PaddyD]

Hi brett:

=SUM(INDIRECT("a2:a"&C5))

or

=SUM(OFFSET(A2,0,0,C5-1))

would both do it.

paddy

 

[Aladin Akyurek]

A fast, non-volatile option...

=SUM(A2:INDEX(A:A,C5,1))

Aladin

 

[Mark W.]

Or...

=SUM(A2:INDEX(A:A,C5))

 

[brettvba]

ok thanks Mark I will give you the formula im trying to get this to work on

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((Christchurch!$B$2:$B$18880=$A3)+0,(Christchurch!$F$2:$F$18880/Christchurch!$G$2:$G$18880))

where im trying to replace the 18880 with a cell value?

Brett

 

[Aladin Akyurek]

On 2002-11-06 18:22, brettvba wrote:
[...]the formula im trying to get this to work on

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((Christchurch!$B$2:$B$18880=$A3)+0,(Christchurch!$F$2:$F$18880/Christchurch!$G$2:$G$18880))

where im trying to replace the 18880 with a cell value?[...]

Unless you want to make selections at will from your data range, try to define a single dynamic named range instead, say DTable, spanning column B to G and use

=SUMIF(INDEX(DTable,0,1),$A3,INDEX(DTable,0,4)+SUMPRODUCT((INDEX(DTable,0,1)=$A3)+0,(INDEX(DTable,0,5)/INDEX(DTable,0,6)))

A reference among many on how to create DTable is...

http://www.mrexcel.com/board/viewtopic.php?topic=16207&forum=2&7

 

[Mark W.]

On 2002-11-06 18:22, brettvba wrote:
ok thanks Mark I will give you the formula im trying to get this to work on

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((Christchurch!$B$2:$B$18880=$A3)+0,(Christchurch!$F$2:$F$18880/Christchurch!$G$2:$G$18880))

where im trying to replace the 18880 with a cell value?

Brett

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((INDIRECT("Christchurch!B2:B"&C5)=$A3)+0,INDIRECT("Christchurch!F2:F"&C5)/INDIRECT("Christchurch!G2:G"&C5))

 

[brettvba]

Thanks Guys for all your help much appreciated.

BRett