Copying information from multiple worksheets into one
 

Hi,

I have a workbook containing 50+ worksheets in the same format, with the
same information calculated for each individual client.

What I need to do is copy specific information (which is always in the same
cells in each worksheet) into one definitive list. Is there an easy way of
doing this?

For E.g.
I have 4 worksheets labelled client A, B, C & D
in each worksheet, cell B1 contains client name
cell F29 contains fist line of address
cell L10 contains the charge rate

Is it possible to have one worksheet with the information in cells B1, F29
and L10 in 3 adjacent columns in a list of the 4 clients?

Hope this makes sense.

Many thanks,

HS

1) In cells A2:A5 type the sheet names EXACTLY as they appear on the tabs
2) in cells B1:D1 type in the cell addresses from which you want to get the
data (B1, F29, L10
3) In cells B2 enter the following formula
=INDIRECT("'"&$A2&"'!"&B$1)
4) Copy the formula to the range B2:D5


"ShahAFFS" wrote:

Hi,

I have a workbook containing 50+ worksheets in the same format, with the
same information calculated for each individual client.

What I need to do is copy specific information (which is always in the same
cells in each worksheet) into one definitive list. Is there an easy way of
doing this?

For E.g.
I have 4 worksheets labelled client A, B, C & D
in each worksheet, cell B1 contains client name
cell F29 contains fist line of address
cell L10 contains the charge rate

Is it possible to have one worksheet with the information in cells B1, F29
and L10 in 3 adjacent columns in a list of the 4 clients?

Hope this makes sense.

Many thanks,

HS

Thank you very much for that. Sadly, I have around 160 odd worksheets abd
each are lablled different client names, thus it would be impractible for me
to do this.
Fortunately, I found a link to a macro that a very helpful soul had posted
on someone elses query. I have since been able to solve this.

Many thanks

HS

"Duke Carey" wrote:

1) In cells A2:A5 type the sheet names EXACTLY as they appear on the tabs
2) in cells B1:D1 type in the cell addresses from which you want to get the
data (B1, F29, L10
3) In cells B2 enter the following formula
=INDIRECT("'"&$A2&"'!"&B$1)
4) Copy the formula to the range B2:D5


"ShahAFFS" wrote:

Hi,

I have a workbook containing 50+ worksheets in the same format, with the
same information calculated for each individual client.

What I need to do is copy specific information (which is always in the same
cells in each worksheet) into one definitive list. Is there an easy way of
doing this?

For E.g.
I have 4 worksheets labelled client A, B, C & D
in each worksheet, cell B1 contains client name
cell F29 contains fist line of address
cell L10 contains the charge rate

Is it possible to have one worksheet with the information in cells B1, F29
and L10 in 3 adjacent columns in a list of the 4 clients?

Hope this makes sense.

Many thanks,

HS