References to outside sheets

[daniroy]

Hello there ! and thanks to everybody providing help!
I have spreadsheets in folders call November06, December06,
January07...
Files names structure is always the same 1-11-06, 2-11-06 ... 12-11-06
and so on
I want to do list on a single brand new sheet the value of cell F14 on
every daily sheet
Obviously I can open every file and refer the new sheet the daily
sheets and the link structure would sounds as '[1-11-06] Sheet1'!$F
$14
but as the daily files names always have the same structure, I may be
able to enter "1-11-06" in lets say Brand New Sheet Cell A1 and create
an expression in cell B1 to get reference to this sheet, can I ?

Many many thanks for this!
Kind regards
Daniel

 

[Lori]

You could try Data > Consolidate:

1. Browse for the folder containing November,December,January then
click cancel so it's current.

2. In the references dialog enter all files for each folder:

'November06\[*.xls]Sheet1'!F4
'December06\[*.xls]Sheet1'!F4
'January07\[*.xls]Sheet1'!F4

3. Select count and check the create links box then OK.

The output is in summarised form but you can choose Data > Group and
Outline > Clear outline. Settings are stored so you can easily delete
existing rows and repeat if necessary.

4. You can add a column of dates for the values from the formulas.
make a copy of the column and choose Edit>Replace Find: *[ and leave
replace blank and then repeat for Find: ]*.

If you want to find the value of another cell copy the column and
replace F4 by say A1.

 

[daniroy]

THIS IS GREAT, REALLY GREAT!
Thanks a lot!!!
Daniel

You could try Data > Consolidate:

1. Browse for the folder containing November,December,January then
click cancel so it's current.

2. In the references dialog enter all files for each folder:

'November06\[*.xls]Sheet1'!F4
'December06\[*.xls]Sheet1'!F4
'January07\[*.xls]Sheet1'!F4

3. Select count and check the create links box then OK.

The output is in summarised form but you can choose Data > Group and
Outline > Clear outline. Settings are stored so you can easily delete
existing rows and repeat if necessary.

4. You can add a column of dates for the values from the formulas.
make a copy of the column and choose Edit>Replace Find: *[ and leave
replace blank and then repeat for Find: ]*.

If you want to find the value of another cell copy the column and
replace F4 by say A1.

Hello there ! and thanks to everybody providing help!
I have spreadsheets in folders call November06, December06,
January07...
Files names structure is always the same 1-11-06, 2-11-06 ... 12-11-06
and so on
I want to do list on a single brand new sheet the value of cell F14 on
every daily sheet
Obviously I can open every file and refer the new sheet the daily
sheets and the link structure would sounds as '[1-11-06] Sheet1'!$F
$14
but as the daily files names always have the same structure, I may be
able to enter "1-11-06" in lets say Brand New Sheet Cell A1 and create
an expression in cell B1 to get reference to this sheet, can I ?

Many many thanks for this!
Kind regards
Daniel

 

[Lori]

Glad it worked for you, thanks for the reply!

THIS IS GREAT, REALLY GREAT!
Thanks a lot!!!
Daniel

You could try Data > Consolidate:

1. Browse for the folder containing November,December,January then
click cancel so it's current.

2. In the references dialog enter all files for each folder:

'November06\[*.xls]Sheet1'!F4
'December06\[*.xls]Sheet1'!F4
'January07\[*.xls]Sheet1'!F4

3. Select count and check the create links box then OK.

The output is in summarised form but you can choose Data > Group and
Outline > Clear outline. Settings are stored so you can easily delete
existing rows and repeat if necessary.

4. You can add a column of dates for the values from the formulas.
make a copy of the column and choose Edit>Replace Find: *[ and leave
replace blank and then repeat for Find: ]*.

If you want to find the value of another cell copy the column and
replace F4 by say A1.

On Feb 8, 9:58 am, (e-mail address removed) wrote:

Hello there ! and thanks to everybody providing help!
I have spreadsheets in folders call November06, December06,
January07...
Files names structure is always the same 1-11-06, 2-11-06 ... 12-11-06
and so on
I want to do list on a single brand new sheet the value of cell F14 on
every daily sheet
Obviously I can open every file and refer the new sheet the daily
sheets and the link structure would sounds as '[1-11-06] Sheet1'!$F
$14
but as the daily files names always have the same structure, I may be
able to enter "1-11-06" in lets say Brand New Sheet Cell A1 and create
an expression in cell B1 to get reference to this sheet, can I ?
Many many thanks for this!
Kind regards
Daniel