R1C!

  • Thread starter Thread starter nathan
  • Start date Start date
N

nathan

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.
 
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.
 
-----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.



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.
Click to expand...


.
hi
Click to expand...

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
 
n said:
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
Click to expand...



ummmm - change the reference to "Pivot YTD" to another sheet
then delete the pivot sheet. When you restart you can recreate the
pivoted sheet and change the references back.

OR

replace all the "=" on the sheet where you are getting the #REF errors
with something like "xxxxxx". After you recreate the pivot you can change
all "xxxxxx" to "="

OR

have the sheet with the #REFs on it in a different workbook to the
pivot table sheet. You close the sheet where you get the #REFF errors
first. Delete the pivot table - save that workbook. When you want to
start up again you open the pivot table workbook and recreate it then,
after the pivot table is created, you open the other workbook. The
links will work cos they do not care what you do when the workbook
which contains them is closed.

You can even open the workbook with the references to the pivot table,
after the pivot table is deleted, and see the results AS THEY WERE when
you last had a pivot table saved AS LONG AS YOU DO NOT refresh
the links to the closed workbook. If you accidentally do refresh the links
then you will get your #REF errors but that is ok cos you can just close the
workbook with them in it and not save it.

Thinking about it further - the two workbooks approach is probably the
best as it means you do not have to do extra work than is absolutely
necessary on a regular basks. You only need to right click the tab of
that worksheet and move it to a new workbook. You can either open
both workbooks and view things like they were in one workbook or
you can open either by itself - the workbook with the pivot table will
need to be opened on its own when you want to change the pivot table
(recreate it) - the workbook with the links to the pivot table will need to
be opened on its own when you want to view the results of the pivot
table in-between when you want it changed.

Chrissy.
 
Back
Top