PDA

View Full Version : Solved: Create dynamic range to use in formula

Marcster
07-16-2012, 08:56 AM
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
07-16-2012, 01:01 PM
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
07-16-2012, 01:37 PM
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
07-16-2012, 01:42 PM
Ya, you might want to use CountA instead of count in my formula, but that does the same kind of thing...

Marcster
07-16-2012, 01:43 PM
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
07-16-2012, 01:44 PM
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))))