|
Shortening references to external workbooks
I have a spreadsheet that needs to reference data for some of its functions
which is held in another spreadsheet on a shared server which is leading to very long functions eg: =IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6,"PMH",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AJ6,"LDV",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AP6,"SIM",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AX6,"HOM","")))) Is there some way I can 'define' something short to be egual to the bit - 'http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15)' and then replace that in my formulas to make them easier to manage & read? Thanks |
Shortening references to external workbooks
Gidders,
Put the cell references into individual cells, then refer to those cells in your formulas. Label them nicely with an explanation of what they are - much more traceable and usable. HTH, Bernie MS Excel MVP "Gidders" wrote in message ... I have a spreadsheet that needs to reference data for some of its functions which is held in another spreadsheet on a shared server which is leading to very long functions eg: =IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6,"PMH",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AJ6,"LDV",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AP6,"SIM",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AX6,"HOM","")))) Is there some way I can 'define' something short to be egual to the bit - 'http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15)' and then replace that in my formulas to make them easier to manage & read? Thanks |
Shortening references to external workbooks
Bernie
Thanks for your response but I don't understand how that would work. For example if cell A1 ='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15)' ie the path bit to the spreadsheet on the shared server then what's my if statement going to look like? How do I combine whats in A1 into the formula? Thanks "Bernie Deitrick" wrote: Gidders, Put the cell references into individual cells, then refer to those cells in your formulas. Label them nicely with an explanation of what they are - much more traceable and usable. HTH, Bernie MS Excel MVP "Gidders" wrote in message ... I have a spreadsheet that needs to reference data for some of its functions which is held in another spreadsheet on a shared server which is leading to very long functions eg: =IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6,"PMH",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AJ6,"LDV",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AP6,"SIM",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AX6,"HOM","")))) Is there some way I can 'define' something short to be egual to the bit - 'http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15)' and then replace that in my formulas to make them easier to manage & read? Thanks |
Shortening references to external workbooks
Gidders,
What I meant was use a link to the data. So, in cell A1, the link is ='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6 and then your formula changes from: =IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6,"PMH",IF(....... to =IF(K$2=A1,"PMH",IF(....... basically, removing the 'http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6 from the formula into a cell. However, I am not experienced in linking to a workbook stored on a website, so am unfamiliar with the specifics and possible limitations. Bernie "Gidders" wrote in message ... Bernie Thanks for your response but I don't understand how that would work. For example if cell A1 ='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15)' ie the path bit to the spreadsheet on the shared server then what's my if statement going to look like? How do I combine whats in A1 into the formula? Thanks "Bernie Deitrick" wrote: Gidders, Put the cell references into individual cells, then refer to those cells in your formulas. Label them nicely with an explanation of what they are - much more traceable and usable. HTH, Bernie MS Excel MVP "Gidders" wrote in message ... I have a spreadsheet that needs to reference data for some of its functions which is held in another spreadsheet on a shared server which is leading to very long functions eg: =IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AE6,"PMH",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AJ6,"LDV",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AP6,"SIM",IF(K$2='http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master Schedule'!$AX6,"HOM","")))) Is there some way I can 'define' something short to be egual to the bit - 'http://transfreespace.mycompany.co.uk/sites/ABC/2008 ABC Rollout Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15)' and then replace that in my formulas to make them easier to manage & read? Thanks |
| All times are GMT +1. The time now is 06:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com