-----Original Message-----
You are deleting the cells to which your formula refer.
This results in the Excel not having anything to use as
a reference.
One solution is to not delete the cells on your Pivot YTD
sheet. You could just clear the contents of that sheet so
the cells remain there and the formulas still work.
Another solution would be to, as you suggest, redo the
formula with VBA code when you recreate the Pivot YTD
sheet.
Another is to use the INDIRECT function to create your
formula.
=INDIRECT("'Pivot YTD'!B$6="""""","""""",'Pivot YTD'!B$6")
would be the simplest way to do this but you would have
to create each formula individually. You can use this
assuming
that each formula is in the same column as your data on
Pivot YTD
=IF(INDIRECT("'Pivot YTD'!R6C[0]",FALSE)="","",INDIRECT
("'Pivot YTD'!R6C[0]",FALSE))
Another thing about your formula is that it says if the
value in C6 on
Pivot YTD nothing then display nothing otherwise display
that value.
IOW - display the value in C6 from Pivot YTD. Why are
you using
the IF function?
Maybe what you really want is
=INDIRECT("'Pivot YTD'!R6C[0]",FALSE)
Chrissy.
"nathan" <n@N wrote in message news:598601c3575f$11707c40
...
Hi
i am using the following formulae in my spreadsheet.
=IF('Pivot YTD'!B$6="","",'Pivot YTD'!B$6)
This is used in a number of cells, but the letter alters
sequentially
i.e
=IF('Pivot YTD'!c$6="","",'Pivot YTD'!c$6)
=IF('Pivot YTD'!d$6="","",'Pivot YTD'!d$6)
=IF('Pivot YTD'!e$6="","",'Pivot YTD'!e$6)
and so on
I have set up some code to create the pivot tables that
i
use, but when the spreadsheet closes, these pivots are
deleted to reduce size. When i open the report again,
the
above formulae contain REF! where the cell addresses
should be. Is there a way of :
a) stopping this
b) using vb to insert the formulae again using r1c1
TIA
Nathan.
.
hi
i have tried that after your advice, but the clearcontents
doesnt work, cause there are pivot tables there. I get
the message "Can not change this part of pivot table"
Any ideas
TIA
N