INDIRECT with H and VLookup from Dynamic list

jessmoore78

New Member
Joined
Sep 13, 2015
Messages
8
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).

Code:
=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…

VBA Code:
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jessmoore78

New Member
Joined
Sep 13, 2015
Messages
8
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

New Member
Joined
Sep 13, 2015
Messages
8
It looks like there isn't much response to the proposed method. I would be open to another method if anyone can help.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,071
Messages
5,545,818
Members
410,707
Latest member
SanTrapGamer
Top