INDIRECT with H and VLookup from Dynamic list

[jessmoore78]

Hello,

I created a resource planning tool that allows the users to add projects and assign resources to each project. There is a total capacity tab that shows all of the resources with their allocated capacity. Each time a new project is created, the code in the total capacity is erased, then re-written based on the projects in the dynamic project list (Named: projects). Of course I didn't expect as many projects as have currently been added and I was wanting to use indirect with the named list but I can't figure out how to do this with the H and Vlookup which search for the name in the first column and the date in the 4 / 5th row. Here are the relevant code snippets.

This is an example of the current total capacity sum code in the first cell (this is written once and then copied to the rest of the table using vba).

=IFERROR(VLOOKUP($A6,Baseload!$A$6:$NF$101,HLOOKUP(DY$4,Baseload!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project1!$A$6:$NF$101,HLOOKUP(DY$4,Project1!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project2!$A$6:$NF$101,HLOOKUP(DY$4,Project2!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project3!$A$6:$NF$101,HLOOKUP(DY$4,Project3!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project4!$A$6:$NF$101,HLOOKUP(DY$4,Project4!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project5!$A$6:$NF$101,HLOOKUP(DY$4,Project5!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project6!$A$6:$NF$101,HLOOKUP(DY$4,Project6!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project7!$A$6:$NF$101,HLOOKUP(DY$4,Project7!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project8!$A$6:$NF$101,HLOOKUP(DY$4,Project8!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project9!$A$6:$NF$101,HLOOKUP(DY$4,Project9!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project10!$A$6:$NF$101,HLOOKUP(DY$4,Project10!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project11!$A$6:$NF$101,HLOOKUP(DY$4,Project11!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project12!$A$6:$NF$101,HLOOKUP(DY$4,Project12!$E$4:$NF$5,2,FALSE),FALSE),0)+IFERROR(VLOOKUP($A6,Project13!$A$6:$NF$101,HLOOKUP(DY$4,Project13!$E$4:$NF$5,2,FALSE),FALSE),0)

Of course you can tell that this is just getting out of hand.^^

Here is the vba code that creates the formula…

Dim cForm As String, Dim i As Integer

'Set the intial cForm by running through the project list and appending the project with every project listed
i = 2
cForm = "=IFERROR(VLOOKUP($A6,Baseload!$A$6:$NF$101,HLOOKUP(E$4,Baseload!$E$4:$NF$5,2,FALSE),FALSE),0)"
Do Until Sheets("Lists").Cells(i, 3).Value = ""
cForm = cForm & "+IFERROR(VLOOKUP($A6," & Sheets("Lists").Cells(i, 3).Value & "!$A$6:$NF$101,HLOOKUP(E$4," & Sheets("Lists").Cells(i, 3).Value & "!$E$4:$NF$5,2,FALSE),FALSE),0)"
i = i + 1
Loop

Any help I can get would be greatly appreciated.

 

[jessmoore78]

One thing I forgot to mention in my initial post, but can be understood from the VBA, the project names are assigned to the tabs as well. Each project has it's own sheet.

 

[jessmoore78]

It looks like there isn't much response to the proposed method. I would be open to another method if anyone can help.