collating data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wildauk - 25 May 2008 15:53 GMT

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

Reply to this Message

ShaneDevenshire - 25 May 2008 22:17 GMT

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.

Signature

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
[quoted text clipped - 7 lines]
> into b1,c1,d1,e1 ect, on one sheet.
> a tall order I know but could any onegive me some ideas?.

Reply to this Message

wildauk - 25 May 2008 23:50 GMT

Cheers Shane, I'll give it a go.

> Hi,
>
[quoted text clipped - 20 lines]
> > into b1,c1,d1,e1 ect, on one sheet.
> > a tall order I know but could any onegive me some ideas?.

Reply to this Message