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

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.

"[email protected]" 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
>
>

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, [email protected] 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

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, [email protected] 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

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 Dibben wrote:
> 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, [email protected] 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