- Ideas and Suggestions
-
Ideas
Moving custom spreadsheet to latest version
Can anyone tell me how a customized sheet in an OSV Stock Analyzer workbook is transferred from a current OSV workbook to a new (ie, version update) workbook (example EC05 to EE09)
Please include all the steps - for instance -
I think you must be signed in to the workbook for it to 'open' but it seems you can only have 1 workbook opened at a time.
Updating links to older workbook pages to same links in new pages
Thanks for any help
Answer
@Dan,
This can easily become a very complex task.
- did you shift rows or columns of any of the original worksheets?
- or were your customizations done on a new blank worksheet and everything else was intact?
If you shifted rows or columns in the main sections, then there is a lot of work involved with having to match the cells from the old spreadsheet to the new spreadsheet.
If you used a blank worksheet, that's easier because you could do a "copy -> paste special -> paste formula only " which will cut down a lot of the time.
First thing is to send a copy of your spreadsheet to support@oldschoolvalue.freshdesk.com.
I can see how complex the work is and let you know.
Jae,
Thanks. Really haven't started yet - just experimenting and was having trouble with going from original spreadsheet to a new version. How to you get old (with an extra sheet and some original sheets having my extra rows-but no movement of any OSV items ) and new OSV Stock Analyzer open at the same time?
Use the INDIRECT function in every cell in the custom worksheet that links to a cell in a standard worksheet and concatenate a reference to a cell that contains the name of the workbook. As an example if the cell in the custom worksheet links to a cell in the standard "Statements" worksheet create a cell such as B1 to hold the name of the workbook OSV_Stock_Analyzer-vEE09_Official.xlsb
In the custom cell that links to say a Cash from Operations cell in the standard Statements worksheet replace =Statements!EC84 with
=INDIRECT("'[" & $B$1 & "]Statements'!C584")
Note the nomenclature in my version of excel requires the single apostrophe before the first [ and between the worksheet name & the !
If all your links are created this way all you need to do is open the new workbook along with the old, right click on the worksheet tab, choose copy to the new workbook. Once copied change the name of the workbook in cell B1 to the new workbook name. As long as the standard sheets in the new workbook maintains the same data in the same cells as the original workbook everything will be fine.
For example, if the new workbook is named EPD.xlsb the transferred worksheet will look like,
Thanks for those screenshots! I didn't think about the indirect function.
You are awesome.
Just to add, the workbook name doesn't have to be hardcoded (as in the example in $B$1). You can use a formula to retrieve the workbook name to that $B$1 cell so you don't have to worry if you decide to save the workbook under a new name or remember to change it every time you do so. It happened to me & I was horrified to see all my formula turn into #REF! This was remedied of course by typing in the new workbook name but using the formula makes everything dynamic. The formula (described at Get workbook name only ) is:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
More fully described at Get workbook name only
Customer support service by UserEcho
Use the INDIRECT function in every cell in the custom worksheet that links to a cell in a standard worksheet and concatenate a reference to a cell that contains the name of the workbook. As an example if the cell in the custom worksheet links to a cell in the standard "Statements" worksheet create a cell such as B1 to hold the name of the workbook OSV_Stock_Analyzer-vEE09_Official.xlsb
In the custom cell that links to say a Cash from Operations cell in the standard Statements worksheet replace =Statements!EC84 with
=INDIRECT("'[" & $B$1 & "]Statements'!C584")
Note the nomenclature in my version of excel requires the single apostrophe before the first [ and between the worksheet name & the !
If all your links are created this way all you need to do is open the new workbook along with the old, right click on the worksheet tab, choose copy to the new workbook. Once copied change the name of the workbook in cell B1 to the new workbook name. As long as the standard sheets in the new workbook maintains the same data in the same cells as the original workbook everything will be fine.
For example, if the new workbook is named EPD.xlsb the transferred worksheet will look like,