ExcelBanter - How to create a fill down that increments based on the workbook name

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - How to create a fill down that increments based on the workbook name (https://www.excelbanter.com/excel-worksheet-functions/97175-how-create-fill-down-increments-based-workbook-name.html)

How to create a fill down that increments based on the workbook name

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

How to create a fill down that increments based on the workbook na

Create an index on a new sheet. TechRepublic.com has the code for an index
already set up for that you can use. Once you copy the code, name the firs
sheet index, right clck the tab, select "view code" and paste into the VB
window. From here, as you add sheets and hyperlinks, the index will update
dynamically. If, in the event, the idex doesn't update dynamically, go back
to "view code" and hit the F5 button. This will force the index code to run
and update itself. Don't forget to add the hyperlinks manually.

Hope this helps.

" 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

How to create a fill down that increments based on the workbook name