Using VLOOKUP to Copy a Row of Formulas

[AnnR]

Hi Ninjas,

I have set up a series of 25 timeline template formulas, and used vlookup functions to design a way for users to answer a couple questions to determine which template they should use. The templates are designed to backfill the timeline based on the deadline date. (It's used to help the team determine when projects need to be started.)

Now, I need a way to automatically copy the timeline template formulas once the vlookup value appears, and allow the user to input the desired deadline date.

Due to company policy I'm unable to post an example file, but the file is set up like this ... lets say rows 3, 7, 9, and 12 contain timeline template formulas that use the value in column J (which the user can input) to calculate the timeline. (the formula I have in row 3, column K, looks like this =WORKDAY($J3,-($K$1-4),Holidays2013)

Row 20 contains the input cells that determine which template should be used. The vlookup value provides a row number ID to let me know which template to choose. I need an automated way to pull in the formulas so my users can set up their timelines without having to manually copy and paste, as some of them are challenged by that. (Yes, seriously.) The same set-up in row 20 will need to be copied for each task in the project so a template may be used multiple times, with different deadline dates.


I can get the formulas to display using an EvalFormula VB command, but they reference the original template deadline date cell (e.g. Template in Row 3 references J3, Row 12 references J12, etc.) ... I need it to somehow update each formula to reference the deadline value in column J of the row in which they are pasted. Anyone have any ideas on how to make that happen?

Thanks in advance for your help!

Ann

 

[NARAYANK991]

Hi Ann ,

More than 12 hours have passed without any response ; probably you know why.

A problem specification should first and foremost be concerned with What and Where ; Why is a secondary aspect , which can be explained in case others have issues. When I try to answer a question , I am not really interested in why you have designed your worksheet in a particular way , unless that really impacts on the solution.

If you can say exactly how your data is laid out , where you want the formulae , and how the formulae should work , that should get you a solution in the shortest possible time.

As far as I can see , the words with the most information are :

lets say rows 3, 7, 9, and 12 contain timeline template formulas that use the value in column J (which the user can input) to calculate the timeline. (the formula I have in row 3, column K, looks like this =WORKDAY($J3,-($K$1-4),Holidays2013)

Can you give more information on what J3 , K1 contain ? Where else are such similar formulae placed ?

What are the variables in these formulae ?

Narayan

 

[AnnR]

My apologies Narayan ... here are the additional details.

Column J is always a date - the desired deadline for the project task.

I mocked up the row numbers in my earlier example, so I'll change those now to represent what's in my actual spreadsheet ... Rows 1-3 are actually header lines ... Row 4 contains a number of days - the duration for each step in the timeline.

There are 30 "steps" for each timeline, represented in columns K-AN. Rows 3-25 contain duration templates, which manipulate the number of days alloted for each step, and whether or not each step applies in the project process. The Duration templates use the following formula: =K$4+L5 - Row 4 is always referenced, the second row reference changes with each template; e.g., for Row 17, the formula would be =K$4+L17 ... this formula is carried across columns until it reaches column AM - the formula in that cell is =AL$4+AM17. Column AN is entered as a digit, representing the number of days required for the last step of the process. In some cases it is 10 days, in others, it's just 1.

Starting at Row 26 the timeline templates are calculated. These rows contain date values rather than numbers. The principle is that a deadline date is entered in column J, and the Duration templates above are referenced and used to calculate the actual business date that each step begins. The formula used in cells K:AM in these rows (Column K in this example) is: =WORKDAY($J26,-$K$6,Holidays2013) In this case - $J26 contains the deadline date, $K$6 represents the # of days from the deadline, excluding the list of Holidays I set up to remove them from the workday calculations. This formula is carried through to column AN - again referencing the appropriate Duration template from rows 3-25 - in the example, the formula in row 6 is referenced.

The timeline templates vary on a series of variables indicating which steps are and are not required for each type of project. I've used VLOOKUP to design formulas to enable select the appropriate template from a series of drop down menus. The value that the VLOOKUP returns is the row number (26:126) of the appropriate timeline template. This is the formula - the ranges represent lists of template options I added in the far right columns of the spreadsheet:

=IF($A134=$AW$27,(IF($B134=$AW$33,VLOOKUP($C134,$BF$26:$BG$33,2,FALSE),IF($B134=$AW$32,VLOOKUP($C134,$BD$26:$BE$33,2,FALSE),VLOOKUP($C134,$AZ$26:$BA$29,2,FALSE)))),(IF($B134=$AW$33,VLOOKUP($C134,$BH$26:$BI$27,2,FALSE),IF($B134=$AW$33,VLOOKUP($C134,$BB$26:$BC$29,2,FALSE),VLOOKUP($C134,$AX$26:$AY$29,2,FALSE)))))

I tried continuing to use the VLOOKUP to return each cell of the timeline template, but it returns the values from the template rather than the formulas, therefore, the user is unable to manipulate the deadline date to calculate the appropriate timeline.

SO ... my goal is to find a way to use the value returned by the VLOOKUP formula to match the appropriate row # (which does have an ID in column B to match) and then COPY AND PASTE the appropriate formulas from columns K:AN in the cells next to the VLOOKUP value, enabling the user to enter the deadline date and activate the formulas to calculate the timelines. Is this possible?

Many thanks for your assistance!

Ann

 

[NARAYANK991]

Hi Ann ,

That's a lot of information ! Thanks. Will go through it and get back to you if I have any more doubts.

Narayan

 

[AnnR]

Hi Ninjas ... any chance someone could give this another look now that there is more detail? My brain hurts from trying to figure this out!

Many thanks!

 

[SirJB7]

Hi, AnnR!

I assume you've yet gone thru this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Had done so you should have found here:
http://chandoo.org/forum/threads/new-users-please-read.294/
this:
"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."
among a lot of other guidelines.

So there's only one day of difference between NARAYANK991's post and your last one, hence you might have skipped this from the same last source:
"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

Now regarding your corporate policies about uploading sample files, you might proceed as described here:
http://chandoo.org/forum/threads/posting-a-sample-workbook.451
as follows:
"Important
When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."

So (finally reaching the point) you should consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. In this way you would be relieving possible contributors to perform the task of building a model (that they don't know a priori but you do it well) to perhaps understand the environment and to surely analyze proper solutions. Thank you.

Regards!

 

[NARAYANK991]

Hi Ann ,

Sorry I could not work on your problem yesterday.

Going through it now , and trying to put in the formulae you have posted , I think there are some mistakes.

Suppose I take your first formula : =K$4+L5.

Is this supposed to be entered in K5 ? I assume so , because when I copy this down till K25 , in K17 , I have : =K$4+L17 , which matches what you have posted. Now , I copy this across till column AM ; when I check AM17 , I have : =AM$4+AN17 , which does not match what you have posted viz. =AL$4+AM17.

In which case , I have to assume that the formula is posted in J5. Is this correct ?

While you clarify this , I will go through the remaining part of your post and see if I have any other doubts.

Narayan

 

[AnnR]

Thanks, Narayan ... I found a workaround - and my apologies to SirJB7. I ended up using VBA to create a macro ... this is what worked to ultimately create the result I needed. Hope it's helpful to anyone else who may be having the same kind of struggle:

Sub TimelineMaster()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value
If TimelineMatch = 26 Then
Range("E26:AQ28").Copy
ElseIf TimelineMatch = 30 Then
Range("E30:AQ32").Copy
ElseIf TimelineMatch = 46 Then
Range("E46:AQ48").Copy
ElseIf TimelineMatch = 42 Then
Range("E42:AQ44").Copy
ElseIf TimelineMatch = 58 Then
Range("E58:AQ60").Copy
ElseIf TimelineMatch = 74 Then
Range("E74:AQ76").Copy
ElseIf TimelineMatch = 62 Then
Range("E62:AQ64").Copy
ElseIf TimelineMatch = 78 Then
Range("E78:AQ80").Copy
ElseIf TimelineMatch = 38 Then
Range("E38:AQ40").Copy
ElseIf TimelineMatch = 54 Then
Range("E54:AQ56").Copy
ElseIf TimelineMatch = 106 Then
Range("E106:AQ108").Copy
ElseIf TimelineMatch = 34 Then
Range("E34:AQ36").Copy
ElseIf TimelineMatch = 50 Then
Range("E50:AQ52").Copy
ElseIf TimelineMatch = 66 Then
Range("E66:AQ68").Copy
ElseIf TimelineMatch = 82 Then
Range("E82:AQ84").Copy
ElseIf TimelineMatch = 70 Then
Range("E70:AQ72").Copy
ElseIf TimelineMatch = 86 Then
Range("E86:AQ88").Copy
ElseIf TimelineMatch = 110 Then
Range("E110:AQ112").Copy
ElseIf TimelineMatch = 118 Then
Range("E118:AQ120").Copy
ElseIf TimelineMatch = 122 Then
Range("E122:AQ124").Copy
ElseIf TimelineMatch = 126 Then
Range("E126:AQ128").Copy
ElseIf TimelineMatch = 90 Then
Range("E90:AQ92").Copy
ElseIf TimelineMatch = 114 Then
Range("E114:AQ116").Copy
ElseIf TimelineMatch = 94 Then
Range("E94:AQ96").Copy
ElseIf TimelineMatch = 98 Then
Range("E98:AQ100").Copy
ElseIf TimelineMatch = 102 Then
Range("E102:AQ104").Copy
End If
ActiveCell.PasteSpecial Paste:=xlPasteAll
'clear the clip board
Application.CutCopyMode = False
End Sub

Thanks again for your time, Narayan.

 

[SirJB7]

Hi, AnnR!

Since you haven't uploaded any sample file so making a bit difficult to understand your problem, could you please confirm if there's always the same relation between the start and ending rows for each ranged copied in the code posted?

If yes, you could reduce it to something like this:

Option Explicit
 
Sub TimelineMaster()
    ' constants
    '  in 3 digits (or as required) to avoid partial matches
    Const ksTimeline = "026 030 034 038 042 046 050 054 058 062 066 070 074 078 082 086 090 094 098 102 106 110 114 118 122 126"
    ' declarations
    Dim TimelineMatch As Integer, ProjectPlan As Object
    ' start
    TimelineMatch = ActiveCell.Offset(0, -1).Value
    ' process
    If InStr(ksTimeline, TimelineMatch) > 0 Then Range("E" & TimelineMatch & ":AQ" & TimelineMatch+2).Copy
    ActiveCell.PasteSpecial Paste:=xlPasteAll
    ' end
    '  clear the clip board
    Application.CutCopyMode = False
End Sub

Just advise if any issue.

Regards!