jocker_boy
Board Regular
- Joined
- Feb 5, 2015
- Messages
- 83
Hello,
I have this formula in cell L5 in Sheet1:
=SUM('WBS 1:WB3 2'!T5)
I also define this name: "_WBS" that have all this 3 sheets names:
WBS1;WBS2;WBS3
I would like to replace the formula for something like this:
=SUM(INDIRECT(INDIRECT("'"&_WBS&"'!T5"))
But don't work.
Then i try this:
=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T5");"<>0"))
And it works.
But i need to drag from cell L5 to cell L50 and i need to be dynamic:
L5=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T5");"<>0"))
L6=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T6");"<>0"))
L7=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T7");"<>0"))
....
L50=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T50");"<>0"))
How can i do this?
Thanks,
Gonçalo
I have this formula in cell L5 in Sheet1:
=SUM('WBS 1:WB3 2'!T5)
I also define this name: "_WBS" that have all this 3 sheets names:
WBS1;WBS2;WBS3
I would like to replace the formula for something like this:
=SUM(INDIRECT(INDIRECT("'"&_WBS&"'!T5"))
But don't work.
Then i try this:
=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T5");"<>0"))
And it works.
But i need to drag from cell L5 to cell L50 and i need to be dynamic:
L5=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T5");"<>0"))
L6=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T6");"<>0"))
L7=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T7");"<>0"))
....
L50=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T50");"<>0"))
How can i do this?
Thanks,
Gonçalo