Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C!
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C!
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 ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C!
-----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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C!
"n" <n@N wrote in message ...
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|