Forgot the "blanking" part.

=IF(INDIRECT("[job"&ROW()-1&".xls]" &
"Sheet1!$B$2")="","",INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2"))

All on one line


Gord


On Sat, 01 Jul 2006 10:02:30 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

In A3 enter this formula.

=INDIRECT("[job"&ROW()-1&".xls]" & "Sheet1!$B$2")


Gord Dibben MS Excel MVP

On 1 Jul 2006 08:57:07 -0700, wrote:

I would like to know if it is possible to fill down a formula, which
includes a workbook name.
I have many job workbook files (all closed) in same folder as
masterworkbook file. I only use sheet1 in each book. I'd like to link
or copy certain cells - but the same cells in each job workbook - to
the masterworkbook, with the data from each job workbook taking up a
row in the masterbook.

I have in cell A2 of the masterbook the following link:
=[job1.xls]Sheet1!$B$2
I would like to have:
=[job2.xls]Sheet1!$B$2 in cell A3, and
=[job3.xls]Sheet1!$B$2 in cell A4 and so on.

Over time, as new job files get created (job4.xls, job5.xls ....), I
would see data appreaing in the next row in the masterbook; ideally
nothing would be displayed when the next job file has not been created
yet (no errors).

I hope someone could assist with a simple solution. I greatly
appreciate your help!
Mike