SUMPRODUCT and automatically adjust the height value

[ebeyert]

Facts: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Excel 2007<o></o>
The following worksheet:<o></o>
1. HR Planning<o></o>
2. WBS&Action&Planning<o></o>
<o></o>
<o></o>
In worksheet HR Planning I have the following formula: =SUMPRODUCT(N($F10='WBS&Action&Planning'!$TW$19:$TY$389),OFFSET('WBS&Action&Planning'!$BP$19,0,INDEX((COLUMN(A1)-1)*3,1),381,3))<o></o>
<o></o>
This formula works fine except when I add or remove row(s) in worksheet: WBS&Action&Planning between row 19 and 389, I get a #VALUE! , returned as the height in the OFFSET function doesn’t match anymore. <o></o>
<o></o>
Question: Is there a way that if I adjust row(s) in worksheet: WBS&Action&Planning (between row 19 and 389) that the height value in the OFFSET formula automatically will adjust as well?<o></o>
<o></o>
Thanks in advantage,<o></o>
Best regards<o></o>
Ellerd <o></o>

 

[MrKowz]

What type of data do you have in the columns? Numeric or text?

Also, what is the purpose of this formula? I don't recall ever seeing N() being used in a SUMPRODUCT.

 

[Jonmo1]

Try using this in the hieght argument of Offset

ROWS('WBS&Action&Planning'!$TW$19:$TY$389)

 

[Jonmo1]

What type of data do you have in the columns? Numeric or text?

Also, what is the purpose of this formula? I don't recall ever seeing N() being used in a SUMPRODUCT.

N() serves the same purpose as --() or ()+0 or ()*1

 

[MrKowz]

N() serves the same purpose as --() or ()+0 or ()*1

Interesting - does it hold any advantage over any of those other methods?

 

[ebeyert]

Thanks all for your quick reply. Jonmo1; your suggestion works

=SUMPRODUCT(N($F10='WBS&Action&Planning'!$TW$19:$TY$399),OFFSET('WBS&Action&Planning'!$BP$19,0,INDEX((COLUMN(A1)-1)*3,1),ROWS('WBS&Action&Planning'!$TW$19:$TY$399),3))

Thanks
Ellerd

 

[Jonmo1]

You can also just NOT specify the height and width of the offset.
If you want the offset to match the hieght and width of the other range, then use the whole range in the offset..

In worksheet HR Planning I have the following formula: =SUMPRODUCT(N($F10='WBS&Action&Planning'!$TW$19:$TY$389),OFFSET('WBS&Action&Planning'!$BP$19:$BR$389,0,INDEX((COLUMN(A1)-1)*3,1)))

Offset will move the specified number of rows/collumns.
If Hieght/Width are omitted, it defaults to use the same hieght/width of the original offset range.

 

[Jonmo1]

Interesting - does it hold any advantage over any of those other methods?

I don't see any advantage to it...
If anything, the other methods are probably more efficient by not calling another function...

Sounds like a question for Barry or Aladin.