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

[mdms515]

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

 

[MCDST070-271]

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.

 

[Gord Dibben]

In A3 enter this formula.

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


Gord Dibben MS Excel MVP

 

[Gord Dibben]

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

In A3 enter this formula.

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


Gord Dibben MS Excel MVP

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

 

[mdms515]

Thank you very much!
It works as long as I have the job sheets open, but get a REF! error
when closed. Most of them will be closed though in my
scenario.........
Any other thoughts?

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

In A3 enter this formula.

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


Gord Dibben MS Excel MVP

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