Match Index and Concatenate Formula

Hi!
I have been working on many ways to create a Match Index formula (and soon will add a Concatenate formula for the pathway as the workbook and folder numbers will change).

The function of this formula will create a process where anyone can copy, paste and insert rows to the bottom of a list without touching any formulas.

The objective is to have a main workbook (here I will call it Example_2) where it will accumulate all of the hours for each employee from a bunch of closed job workbooks (and I will call this XX2825_Document Schedule.xlsm). -These workbooks are attached to this post with some comments in the Example_2 workbook.

I don't know if this will make it more complicated though right now in the job workbook, XX2825_Document Schedule, there will be a column with the employee's initials and the next column will be the hours hey worked per day. There can be a little over a year worth of workdays total per job. I need the XX2825_Document Schedule workbook's date and employee's initials match up to the Example_2 workbook initials and dates. Though, I am still not pulling any data and not getting any error messages. Can someone please suggest where I am going wrong. Attached are the 2 workbooks I am trying these codes out on.

code #1:

Please Login or Register  to view this content.

or code #2:

Please Login or Register  to view this content.

If there are any suggestions I am all ears.
Thank You!!!
Kirsten

Have not looked at your formula yet, but unless I misunderstand you, this part wont work like that...

I have been working on many ways to create a Match Index formula (and soon will add a Concatenate formula for the pathway as the workbook and folder numbers will change).

To add a varialble file name/path, you will need to use INDIRECT, as excel does not accept text in forlulas like that - and for INDIRECT to work, the source file needs to be OPEN
(just something to consider)

Also, you can't use MATCH with an array like this: MATCH($D53,$B$556:$UW$577,0), only with a single column.
The way the hour tables are structured makes them a bit hard to work with. It would be better if they were laid out in a traditional data table way, put the date + names as headers. Using the Excel Table feature the table range will expand automatically as data is added.
I may look into this more later.

Though for the indirect vs concatenate issue, is there another formula or method I can use?

This is not a "this way or that way" situation. If you are referencing a cell that contains text to be used in a formula, you need to use INDIRECT, that is pretty much the only way excel will let you do that.

So, if you have a list of files (or sheet names), and you want a formula tjhat will reference those, you have to use INDIRECT to be able to inclue that in the formula.