reference of PivotTable

[Hendry Del Fuego]

Hi all,

I am struggling with the following problem:

I want to use the following formula:
=OFFSET('Sheet1 (1)'!$C$2;1;0;COUNT('Sheet1 (1)'!$C$3:$C$2008);1)

very easy but this doesnt work. Why, because C2 is a field of a Pivottable
and apparently Excell doest see this as a reference cell.

any ideas how to solve this?

Best regards
Kenny

 

[Ed Ferrero]

Hi Kenny,

Works for me.

Try =OFFSET('Sheet1 (1)'!$C$2,1,0,1,1)
This should return the value in cell C3 in Sheet1 (1)

Your formula returns an array x rows high where
x = COUNT('Sheet1 (1)'!$C$3:$C$2008)

You need to enter the formula in one cell, then select a range of cells and
press {Ctrl}{Enter} to see more that the first entry in the array.

Also note that the separator for formula arguments is a comma, not a
semi-colon. (May differ depending on your language settings?)

Ed Ferrero
www.edferrero.com