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

M

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
 
M

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.
 
G

Gord Dibben

In A3 enter this formula.

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


Gord Dibben MS Excel MVP
 
G

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
 
M

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?

Gord said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top