Solved: Create dynamic range to use in formula

[Marcster]

On Sheet2 I have some values/text in cells: D8:G1000
On Sheet1 I need a formula in cell E7 that sum ifs the above range:

=SUMIF(Sheet2$D$8:$D$1000,T(B7&"-00"),Sheet2!$G$8:$G$1000)

But Sheet2 values/text range will widen and shorten daily, so today it's D8:G1000, tomorrow
could be D8:G125, next day could be D8:G12566

How do I change the above formula to automatically get the last used row? :banghead: .
The D8 and G8 ref will stay the same.

Thanks,

[CodeNinja]

use offfset(count) to do this

=SUMIF(Sheet2!$D$8:OFFSET($D$8,0,COUNT(8:8)),T(B7&"-00"),Sheet2!$G$8:OFFSET($G$8,0,COUNT(8:8)))

[Marcster]

I'll try your formula, but so far have come up with this:

In cell E2 Sheet2: =ROW(OFFSET(Sheet2!D8,COUNTA(D:D)-1,0))
Which gives the last row number used in column D.

Sheet1:
=SUMIF(INDIRECT("Sheet2!$D$8:$D"&Sheet2!E2),T(B7&"-00"),INDIRECT("Sheet2!$G$8:$G"&Sheet2!E2))

[CodeNinja]

Ya, you might want to use CountA instead of count in my formula, but that does the same kind of thing...

[Marcster]

Sheet1:
=SUMIF(INDIRECT("Sheet2!$D$8:$D"&ROW(OFFSET(Sheet2!D8,COUNTA(Sheet2!D:D)-1,0))),T(B8&"-00"),INDIRECT("Sheet2!$G$8:$G"&ROW(OFFSET(Sheet2!D8,COUNTA(Sheet2!D:D)-1,0))))

[Marcster]

Thanks CodeNinja for sending me in the right direction :-).

Ha, smilies in my formula!.
Should of been:

=SUMIF(INDIRECT("Sheet2!$D$8:$D"&ROW(OFFSET(Sheet2!D8,COUNTA(Sheet2!D:D)-1,0))),T(B8&"-00"),INDIRECT("Sheet2!$G$8:$G"&ROW(OFFSET(Sheet2!D8,COUNTA(Sheet2!D:D)-1,0))))