Hello

I have a workbook with 95 sheets containing information for different
customers and I would like to extract information from 3 cells in each
sheet to a 96th sheet to use this for a mailmerge document.

Basically I want the information in B1 of all the sheets to copy to
A2-A96 of the new sheet, i.e.

Sheet 1 B1 Baker
Sheet 2 B1 Toms
Sheet 3 B1 Peters

and what I would like on sheet 96 is

A2 Baker
A3 Toms
A4 Peters
etc

I have tried to use indirect formulae but I cannot get it to work.

Please can anyone point me in the right direction.

TIA

You haven't told us what you were using in your indirect formulae so we
can't help you to debug them.
Next time you ask a question, give us details of the formula used, and tell
us what incorrect result it returns, rather than just "I can't get it to
work".

For now, why not try
=INDIRECT("'Sheet "&ROW()-1&"'!B1")
--
David Biddulph

wrote in message
...
Hello

I have a workbook with 95 sheets containing information for different
customers and I would like to extract information from 3 cells in each
sheet to a 96th sheet to use this for a mailmerge document.

Basically I want the information in B1 of all the sheets to copy to
A2-A96 of the new sheet, i.e.

Sheet 1 B1 Baker
Sheet 2 B1 Toms
Sheet 3 B1 Peters

and what I would like on sheet 96 is

A2 Baker
A3 Toms
A4 Peters
etc

I have tried to use indirect formulae but I cannot get it to work.

Please can anyone point me in the right direction.

TIA

Thank you for replying I have now managed to get this formula working

=INDIRECT("Sheet"&$K1&"!$b$1")

I left the sheets as sheet1, sheet2, sheet3 etc

but now I would like to rename the sheetswith the customers name i.e.
Baker, Toms, Peters and so on, I thought that my link would
automatically update but it doesn't.

Can this be done?

Thanks again




On Mon, 21 Jan 2008 18:55:52 +0000, wrote:

Hello

I have a workbook with 95 sheets containing information for different
customers and I would like to extract information from 3 cells in each
sheet to a 96th sheet to use this for a mailmerge document.

Basically I want the information in B1 of all the sheets to copy to
A2-A96 of the new sheet, i.e.

Sheet 1 B1 Baker
Sheet 2 B1 Toms
Sheet 3 B1 Peters

and what I would like on sheet 96 is

A2 Baker
A3 Toms
A4 Peters
etc

I have tried to use indirect formulae but I cannot get it to work.

Please can anyone point me in the right direction.

TIA

See responses to your other posting

--

Regards
Roger Govier

wrote in message
...
Thank you for replying I have now managed to get this formula working

=INDIRECT("Sheet"&$K1&"!$b$1")

I left the sheets as sheet1, sheet2, sheet3 etc

but now I would like to rename the sheetswith the customers name i.e.
Baker, Toms, Peters and so on, I thought that my link would
automatically update but it doesn't.

Can this be done?

Thanks again




On Mon, 21 Jan 2008 18:55:52 +0000, wrote:

Hello

I have a workbook with 95 sheets containing information for different
customers and I would like to extract information from 3 cells in each
sheet to a 96th sheet to use this for a mailmerge document.

Basically I want the information in B1 of all the sheets to copy to
A2-A96 of the new sheet, i.e.

Sheet 1 B1 Baker
Sheet 2 B1 Toms
Sheet 3 B1 Peters

and what I would like on sheet 96 is

A2 Baker
A3 Toms
A4 Peters
etc

I have tried to use indirect formulae but I cannot get it to work.

Please can anyone point me in the right direction.

TIA