Lookup possibly?

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
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-Nov35 StartBaseBaseBaseBase
4Job219-Nov243StartBaseBaseBase
5Job328-Nov164StartBaseBaseBaseBaseBase
6Job410-Nov431StartBaseBase
7
Job Dates
 

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
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?
 

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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
 

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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?????
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
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.
 

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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/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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
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-1000 14284979109139140
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
Conditional Formatting
CellConditionFormat
C61. / Formula is =C6=B6Abc
 
Last edited:

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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?
 

Forum statistics

Threads
1,081,770
Messages
5,361,181
Members
400,617
Latest member
barron1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top