Is it possible to use functions this way but referring to another workbooks? I recently had to go through many different folders and files to collect data, it is working with VBA already, but I'm pretty sure my code is verbose with things I don't need. Columns are not in the same position so I was thinking of using Index(match()) to get the column address.
I was doing this:
Cells(StartRow, Counter).Formula = "=iferror(MATCH(""ColumnName"",'" & FullPath & "[" & File & "]SheetName " ,0),0)"
It works, but then I have to work with the number returned to figure out the column.
If you write
=INDEX(A1:D4,MATCH("Feb";A1:D1,0),MATCH("Tues",A1:A4,0))you will get "4". Hope it helps.