Dynamic Links across several Worksheets and Workbooks

I posted these spreadsheets once before, but I have come a bit farther and would like further advice. There are now four spreadsheets and I re-worked to have one master data entry worksheet. I am still wondering if that was the correct call. Before my question dealt with copying the room names and numbers and somehow ensuring that the data from two spreadsheets would stay associated to the correct room as revisions are made. The four spreadsheet are as follows:

1. Programming Worksheets ( for data entry )
2. Programming Reports ( for printing the finished data )
3. Space Requirement Data Sheets ( pulls relevant data using VLOOKUP from the Programming Worksheets using a simple drop-down )
4. Equipment Lists ( also pulls relevant data after selecting from a simple drop-down )

My struggle is between the Programming Worksheets and Programming Reports. The reports should automatically generate; it should copy, filter, and sort the data as it is entered. VLOOKUP or INDEX/MATCH doesn't seem to be the right fit; there is no data present to search against. I would have to manually enter the room list twice ( once in Programming Worksheets and once in Programming Reports ), I think. Please tell me I am wrong. An array copying the data almost works, but despite the named range dynamically updating from the Programming Worksheets, the cells selected in Programming Reports do not. This is how the file is currently set up. Thus, the rooms at the bottom get cut off as new rows/rooms are entered in Programming Worksheets. Lastly, I just spent hours testing Get and Transform. It certainly pulls and copies my data, but if this file is to stand as a template ( it will be empty in the beginning ), it will always have duplicate blank entries and cause errors. Therefore, anybody using this so-called template would have to re-apply the complicated Get and Transform process. I think.

Since the last time I posted, my firm just updated to Offfice 365, so Get and Transform is now an option. My gut tells me I need to learn VBA at this point. The goal is automatic, simple, and dummy-proof.

Please ask me questions and point me in the right direction. Please find all four files ... though the first two are really where the problem lies. This should be a fun one for those who like a challenge.

Thanks for taking a look!

I am not familiar with a few of these commands ( SMALL and ROW ). Could you explain exactly what it is attempting to do?

Basically, the formula returns an array of either a row number (relative to start of the range selected) if it finds a match or blank if not. ROW('[Programming Worksheets.xlsx]Building Program Worksheet.'!$A$13:$E$1000),"")

the ROWS( ) formula simple returns a numerical value 1,2,3,4 ....as the formula is dragged down a column and this is the index to SMALL.

So as the formula is dragged down we find the smallest value of the row, then the 2nd smallest and so on. the INDEX ( ) is the result.

To follow this , select first cell with the formula then select FORMULAS ...EVALUATE FORMULA .. then click the Evaluate button as it steps through the formula.

For ease, first change the formula to select a small range ...say 10 rows as per formula below ...

=IFERROR(INDEX('[Programming Worksheets.xlsx]Building Program Worksheet.'!D:AF,SMALL(IF('[Programming Worksheets.xlsx]Building Program Worksheet.'!$A$13:$A$22<>"",ROW('[Programming Worksheets.xlsx]Building Program Worksheet.'!$A$13:$E$22),""),ROWS($B$13:B13)),MATCH(A$9,'[Programming Worksheets.xlsx]Building Program Worksheet.'!$D$9:$AF$9,0)),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then use the Evaluate Formula function

I hate to ask, but it seems like you may have applied this formula to my spreadsheet. Could you upload it? I have been tearing apart the formula attempting to replicate the results you are perhaps seeing, but I have had no luck. I really do appreciate it. If there is a formula way to accomplish the dynamic link, that is where I want to be. Thanks in advance. Even if you just do a small example with the formula, that would still mean much.

See attached files:

in "Reports" A13

=IFERROR(INDEX('[Programming Worksheets.xlsx]Building Program Worksheet.'!$D:$AF,SMALL(IF('[Programming Worksheets.xlsx]Building Program Worksheet.'!$A$13:$A$1000<>"",ROW('[Programming Worksheets.xlsx]Building Program Worksheet.'!$A$13:$E$1000),""),ROWS($B$13:$B13)),1),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

now use the column number relative to range $D:$AF so column D is 1, column E is 2 etc

I added numbers in Row 7 of "Worksheets" to identify the column numbers.

Hope this helps.

Thank you for taking the time. I will dig in learn it!