Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
collating data
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?. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
collating data
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 "wildauk" wrote: 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?. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
collating data
Cheers Shane, I'll give it a go.
"ShaneDevenshire" wrote: 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 "wildauk" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collating/ Summarising worksheets | Excel Discussion (Misc queries) | |||
Help collating data from multiple worksheets | Excel Discussion (Misc queries) | |||
collating Data | Excel Discussion (Misc queries) | |||
collating info from index column | Excel Discussion (Misc queries) | |||
Collating information in a different location | Excel Discussion (Misc queries) |