We use work order numbers at my company. Each work order has a file by the
name WO[6 digit number]-I.xls. I want to create a new workbook that on the
left column has the list of the 6-digit numbers and the next column calls up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!