Excel Challenge: Populating Templates by Appending Specific Data

Thank you in advance to whoever my be reading this. I have attached a copy of my workbook for your refrence. I know what needs to be done I just need an efficient way to do it.

Business Goal: I want to compile a list of properties that are applible for each project. Each project has its own tab and table on that tab. The table says if the property is included or not included as apart of that associated project. If it is associated I want it to populate my template with the property name under the specific project. This will be used to facilitate a datas load into another system.


(What I think would be) The Technical Soution: One of these tabs is a template called 'DRs Template' which I am trying to populate. 'DRs Template' has every tab name transposed across the top row with the names of properties going down (it is the same list of names on each sheet). My code needs to perform the following:

1. Look up at the sheet/tab name transposed across the top of the 'DRs Template' (starting in B2 and moving across) and go to that specific tab.

2. Once on this tab I will Look at a list of names in a table below and if the name has 'Yes' next to it in column D, I will copy the name and paste it on the at the coordinates of the Row(name) and the Column(tab name) it was pulled from onto the 'DRs Template' sheet.

3. From there I wish to repeat the process untuill I run through the entire table pulling only those names with 'Yes' next to it, at which time I would like to go to the next tab (as indicated by the next transposed value on the Template in E1) and do the same.


Good luck,

Sam

I'm not sure what you mean by comment 2, but try this forumula in B3 of "DRs Template", copied down and across:

=IFERROR(IF(VLOOKUP($A3,INDIRECT("'" &B$2&"'!A:D"),4,FALSE)="Yes",$A3,""),"")

That may get you going in the right direction without using VBA.

You can absolutely do this with formulas given your sample. A simple VLOOKUP or an IF(COUNTIFS would likely do exactly what you need. If its just for sheets you can just reference each sheet in each column of your template sheet. Should be relatively simple.