Lookup possibly?

CTMom

New Member
Joined
Jul 4, 2004
Messages
39
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with information about construction stages. See below for example:

Weekly schedule: Nov-5 Nov-12 Nov-19 Nov 26
Job Start Base
Job Start Base

I have a long list of jobs and want to automatically insert the next stage based on the Start date. For example the Base stage comes automatically 2 weeks after Start date. There are 5 stages and the duration between each stage is the same for each job.
Is there a way I can automate the filling in of the spreadsheet so that if the Start date changes it automatically updates the rest of the row with the other stages.

Any help would be greatly appreciated.
Thanks
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I'm a bit unsure about your layout and data and whether a job can start on a day other than one of your headings (see rows 5 & 6 below), but see if you could use something like this.

Formula in E3 is copied down.
Formula in F3 is copied across and down.

Excel Workbook
ABCDEFGHIJKLMNOPQRST
1123456789101112131415
2JobStart DateStage durationNo. StagesStart Col5-Nov12-Nov19-Nov26-Nov3-Dec10-Dec17-Dec24-Dec31-Dec7-Jan14-Jan21-Jan28-Jan4-Feb11-Feb
3Job112-Nov352 StartBaseBaseBaseBase
4Job219-Nov243StartBaseBaseBase
5Job328-Nov164StartBaseBaseBaseBaseBase
6Job410-Nov431StartBaseBase
7
Job Dates
 
Upvote 0
Hi thank you for your reply. Not sure if this will work because there are 5 construction stages for building the house and every house has the same number of stages and every stage has differing durations. Eg. the duration from start date to base is 21 days, from base to frame is 14 days, etc. So everything would probably work well with your formula except for the Stage Duration column as each of the 5 stages has different duration.

Any thoughts would be greatly appreciated.
 
Upvote 0
Could you give the stage durations of every stage and post some sample data (say 5 sets) and results (entered manually) so we can see just how it should turn out?
 
Upvote 0
Hi Peter, thanks for reply. I do not know how to paste excel file into this message so this is manual version:

6 Stages and the number of days from one stage to next:
Site - day 0
Base - 21 days from Site
Frame - 14 days from Base
Lockup - 30 days from Frame
Fix - 30 days from Lockup
PCI - 30 days from Fix

So the table would be like this:

week ending: 5 Nov 12 Nov 19 Nov 26 Nov 3 Dec 10 Dec
Job 1 Site Base Frame
Job 2 Site Base

I want to insert a column next to the Job number with the date that the job starts (ie. Site Start date) and then have the formula populate the rest of the row with the correct stage based on the number of days from the previous stage.

Is there anything that you think can achieve this? Would be very grateful for any help.

Thanks very much
 
Upvote 0
I just posted reply with a table but the spaces did not work so all the stages are bunched together instead of spread out across the dates. I hope this can still make sense?????
 
Upvote 0
I just posted reply with a table but the spaces did not work ..
Investigate these. I have used the first one in my earlier post and you can obviously see how much clearer it is where things are on the sheet and the data can also be copied from such a screen shot to a test file if required.

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste

Before investing too much more time, I would very much like to see just how the table will look. In particular, how you envisage having weekly headings across the top but stages (and presumably start dates) that don't necessarily align with the week start dates.
 
Upvote 0
Here is the sample table. The start dates just show the day that construction commences and I am only interested in the week that they occur in. I would like the rows across (row 6 for example) to be able to look up to the week for each column and then insert the correct Stage name (or it could be stage number) in the right week based on the start date.
I hope this makes it clearer.



Excel Workbook
ABCDEFGHIJK
1Stage**SiteBaseFrameLockupFixPCI**
2Days Duration From Previous Stage**01414213030**
3***********
4**5/11/201012/11/201019/11/201026/11/20103/12/201010/12/201017/12/201024/12/201031/12/2010
5*Site Start*********
6Job 17/11/10Site*Base*Frame**Lockup*
7Job 211/11/10*Site*Base*Frame**Lockup
8Job 320/11/10***Site*Base**Frame
9Job 426/11/10***Site*Base**Frame
10Job 58/11/10Site*Base*Frame**Lockup*
Sheet1
 
Upvote 0
I'm not exactly sure how you are calculating some of those stage dates. For example, your dates across the top are Friday dates so I would have thought they represented the end of the week.

So your Job1 which starts on 7 Nov to me starts after the end of the week so should be included as a start in the second column, not the first. It will certainly be easier if the top dates are clearly either the first day of each of your 'weeks' of the last day of each week. You seem to have used my logic for Job 3 but not Job 1 or Job 5.

Would this suffice?

Note the formulas in C1 and K1. (You could use a space character in these two cells instaed of that formula if you want, just don't leave them completely blank)

Note also the extra manual values in C3 and K2.

Formula in D3 is copied across.

Formula in C6 is copied right across your date columns and then right down your job rows.

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Stage SiteBaseFrameLockupFixPCIPCI 
2Duration01414213030301
3Cum Duration-1000014284979109139140
4
5JobStart Date05-Nov12-Nov19-Nov26-Nov03-Dec10-Dec17-Dec24-Dec31-Dec07-Jan14-Jan21-Jan28-Jan04-Feb11-Feb18-Feb25-Feb04-Mar11-Mar18-Mar25-Mar01-Apr08-Apr15-Apr
6Job 17/11/10 SiteSiteBaseBaseFrameFrameFrameLockupLockupLockupLockupFixFixFixFixPCIPCIPCIPCIPCI
7Job 211/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockupLockupLockupLockupFixFixFixFixPCIPCIPCIPCI
8Job 320/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockupLockupLockupFixFixFixFixPCIPCIPCIPCIPCI
9Job 426/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockupLockupLockupLockupFixFixFixFixPCIPCIPCIPCI
10Job 58/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockupLockupLockupFixFixFixFixPCIPCIPCIPCIPCI
Job Dates




Now if you really don't want the stages repeated like that, you could leave all the formulas as above then select from C6 to the bottom right of the data and apply the Conditional Formatting shown below. The format applied is white text.

Excel Workbook
ABCDEFGHIJKL
1StageSiteBaseFrameLockupFixPCIPCI
2Duration01414213030301
3Cum Duration-1000014284979109139140
4
5JobStart Date05-Nov12-Nov19-Nov26-Nov03-Dec10-Dec17-Dec24-Dec31-Dec07-Jan
6Job 17/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockup
7Job 211/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockup
8Job 320/11/10SiteSiteBaseBaseFrameFrameFrame
9Job 426/11/10SiteSiteBaseBaseFrameFrameFrame
10Job 58/11/10SiteSiteBaseBaseFrameFrameFrameLockupLockup
Job Dates
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C61. / Formula is =C6=B6Abc
 
Last edited:
Upvote 0
Thanks Peter. That works great. Any way of not repeating the stage once it has been used in previous cell. I pick up these stages on another spreadsheet for calculating payments and I only have one payment per stage so only need each stage occurring once. What is the best way to achieve this?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top