collating data

[wildauk]

Hi, I have 26 work books named week 1-week 26 each one has 7 sheets named
Sun- Sat and week, each sheet has a list of names in the first column and
data for each name in the next columns and it is all collated in the sheet
called week.
All 26 work books are paste linked into a worksheet called (six months)each
week having its own sheet.
My problem is that every now and then the list of names change and therefore
change the order they are in, what I need to do is type in a name in A1 or A2
ect and the data associated along side that name in each week is collated
into b1,c1,d1,e1 ect, on one sheet.
a tall order I know but could any onegive me some ideas?.

 

[ShaneDevenshire]

Hi,

If the links to the sheets are in workbooks that are open you can use the
INDIRECT function. For example if a sheet is named Week1 and you have a
formula like =Week1!A1 you can replace it with =INDIRECT(D1&"!A1") where you
type the sheet name into cell D1. If the reference is to another open file
it might look like this: [Book2]Sheet1!$A$2 so the replacement formula
would be
=INDIRECT("[Book2]"&D1&"1!$A$2")

The INDIRECT function does not work when the target file is closed, to
handle that you would need to write a VBA function.

 

[wildauk]

Cheers Shane, I'll give it a go.

Hi,

If the links to the sheets are in workbooks that are open you can use the
INDIRECT function. For example if a sheet is named Week1 and you have a
formula like =Week1!A1 you can replace it with =INDIRECT(D1&"!A1") where you
type the sheet name into cell D1. If the reference is to another open file
it might look like this: [Book2]Sheet1!$A$2 so the replacement formula
would be
=INDIRECT("[Book2]"&D1&"1!$A$2")

The INDIRECT function does not work when the target file is closed, to
handle that you would need to write a VBA function.

--
Cheers,
Shane Devenshire

Hi, I have 26 work books named week 1-week 26 each one has 7 sheets named
Sun- Sat and week, each sheet has a list of names in the first column and
data for each name in the next columns and it is all collated in the sheet
called week.
All 26 work books are paste linked into a worksheet called (six months)each
week having its own sheet.
My problem is that every now and then the list of names change and therefore
change the order they are in, what I need to do is type in a name in A1 or A2
ect and the data associated along side that name in each week is collated
into b1,c1,d1,e1 ect, on one sheet.
a tall order I know but could any onegive me some ideas?.