| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula too long
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long
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 - gsnu200791 "Gidders" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long
Sorry, wrong post.
-- Gary''s Student - gsnu200791 "Gidders" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long
You need a macro for this. Not usre if you want a macro solution?
"Gidders" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Is Too Long | Excel Worksheet Functions | |||
| Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
| formula too long! | Excel Discussion (Misc queries) | |||
| "Formula is too long" | Excel Worksheet Functions | |||
| Formula too long | Excel Worksheet Functions | |||
Linear Mode
