substituting a lookup ref. within a SUMIF()

[keithkemble]

This may be obvious but my excuse is I am still getting over my food poisoning:-

I have a lookup that returns the value K in cell DT8. (This value will change weekly)

I wish to use this value in place of the K$ in the following formula:-

SUMIF(A15:A2000,DR15,K$15:K$2000).

I am still waiting for my 3 books to arrive so I can find the appropriate action therefore any help would be most gratefull.
kk

[Juan Pablo González]

On 2002-02-18 14:12, keithkemble wrote:
This may be obvious but my excuse is I am still getting over my food poisoning:-

I have a lookup that returns the value K in cell DT8. (This value will change weekly)

I wish to use this value in place of the K$ in the following formula:-

SUMIF(A15:A2000,DR15,K$15:K$2000).

I am still waiting for my 3 books to arrive so I can find the appropriate action therefore any help would be most gratefull.
kk

Try with

=SUMIF(A15:A2000,DR15,INDIRECT("K$15:K$" & DT8))

[Aladin Akyurek]

Do you mean by K$ where the range in K ends?

Aladin

[Mark W.]

On 2002-02-18 14:12, keithkemble wrote:
This may be obvious but my excuse is I am still getting over my food poisoning:-

I have a lookup that returns the value K in cell DT8. (This value will change weekly)

I wish to use this value in place of the K$ in the following formula:-

SUMIF(A15:A2000,DR15,K$15:K$2000).

I am still waiting for my 3 books to arrive so I can find the appropriate action therefore any help would be most gratefull.
kk

=SUMIF(A15:A2000,DR15,INDIRECT(DT8&"15:"&DT8&"2000"))

[ This Message was edited by: Mark W. on 2002-02-18 14:28 ]

[keithkemble]

No .

The letter K will change weekly.

i.e. next week it will chnage to L and so on.
Therefore the letter K almost become a variable.


To Juan P

I tried that reply but it throws up a #Ref

would there be 2
,Indirect("Dt8")&15:Indirect("DT8")&2000

This appears similar to the
Cell("contents",DT8) ref but that didn't work either.

[Mark W.]

On 2002-02-18 14:36, keithkemble wrote:
No .

The letter K will change weekly.

i.e. next week it will chnage to L and so on.
Therefore the letter K almost become a variable.


To Juan P

I tried that reply but it throws up a #Ref

would there be 2
,Indirect("Dt8")&15:Indirect("DT8")&2000

This appears similar to the
Cell("contents",DT8) ref but that didn't work either.

Keith, look at my suggestion above...

[Aladin Akyurek]

On 2002-02-18 14:36, keithkemble wrote:
No .

The letter K will change weekly.

i.e. next week it will chnage to L and so on.
Therefore the letter K almost become a variable.


To Juan P

I tried that reply but it throws up a #Ref

would there be 2
,Indirect("Dt8")&15:Indirect("DT8")&2000

This appears similar to the
Cell("contents",DT8) ref but that didn't work either.

I'll presume that DT8 holds some formula that computes where your dynamically changing range ends. If this assumption is right, I propose that you use in DT8 the following formula:

=MATCH(9.99999999999999E+307,K:K)

where I assume that K houses only the numbers of interest and nothing else.

You can rewrite the SUMIF formula as:


=SUMIF(OFFSET(A15,0,0,DT8,1),DR15,OFFSET(K15,0,0,DT8,1))

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-02-18 14:44 ]

[keithkemble]

Mark,

You are a genius/
It works.
Those damn quotes again.
Got me at the week end as well.

Thanks to Juan P and Aladin
Very prompt response.