Shortening references to external workbooks

  • Thread starter Thread starter Gidders
  • Start date Start date
G

Gidders

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula too long 3
Applications take about 3 minutes to start up in XP 6
Freeware from Snapfiles -- 2nd Try! - "freeware.html" (0/1) 36.4 kBytes yEnc 4
Need Solution To Use 2nd Hard Drive 1
History of Xbox 360 defects - Dean Takahashi has the inside story(long) 2
[PL] PL2005 Final Selection 40
WTD: Nvidia Videocard for 4x AGP Slot 0
FWT Newsletter - Weekly - September 13, 2004 1

Back
Top