Formula too long

[Gidders]

I have a series of jobs to carry out on a number of properites that are being
managed by someone else. They have a Master Schedule which is on a shared
server that I have access to, which shows the properties in column A and the
jobs in row 1, with the date that the jobs need to be completed in the body
of the spreadsheet eg

Master Schedule
job 1 job 2 job 3 job 4
property 1 dates dates dates dates
property 2 dates dates dates dates
property 3 dates dates dates dates
property 4
property 5 etc

I want to represent this so that it looks like a calendar, on a separate
spreadsheet with Properties in column A, and weeks commencing in row 1, and
the jobs appearing against each property under the appropriate week eg

Calendar
09-Jun 16-Jun 23-Jun 30-Jun 07-Jul 14-Jul
property 1 job 1 job 2 job 3 job 4
property 2 job 1 job 2 job 3
property 3 job 1 job 2 job 3
property 4
property 5

I am using a formular something like

=IF(AND('http://servername/[hisfilename]Master
Schedule'!$M25>=Calendar!N$2,'http://servername/[hisfilename]Master
Schedule'!$M25<Calendar!N$2+7),"Job 1",
IF(AND('http://servername/[hisfilename]Master
Schedule'!$Z24>=Calendar!N$2,'http://servername/[hisfilename]Master
Schedule'!$Z24<Calendar!N$2+7),"Job 2",
IF(AND('http://servername/[hisfilename]Master
Schedule'!$AF24>=Calendar!N$2,'http://servername/[hisfilename]Master
Schedule'!$AF24<Calendar!N$2+7),"Job 3",""))) etc

which basically retrieves the date in the Job 1 column, and compares in the
the week commencing date and week commencing date + 7. If the date falls
between these then writes "Job 1" in the column, else retreieves the date in
column job 2 and repeats etc. This works with a small number of jobs and
when I have his spreadsheet open on my pc so the path to the server
disappears from my formula.

When either the number of jobs increases, or with a few jobs but his file
residing on his server I get a "formula too long". The problem is exacerbated
by the fact the the path to his server is more like.

'http://transfreespace.hiscompany.co.uk/sites/ABC/2008 ABC Rollout
Schedule/[Latest Rollout Schedule - 2008.05.20 (Version 15).xls]Master
Schedule'!$AE8

Is there any way to shorthen the path to his server etc by defining a
variable as "Serverpath"=.... to use in the function and/or a way to shorthen
the formula functions?

Thanks

 

[Gary''s Student]

Start with all cell unlocked and the worksheet unprotected and then:

1. Goto > Special > Formulas > OK
2. Format > Cells... > Protection >Locked
3. Tools > Protection > Protect Sheet

 

[Gary''s Student]

Sorry, wrong post.

 

[Joel]

You need a macro for this. Not usre if you want a macro solution?