Linking Cells from Multiple Sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pza@btinternet.com - 21 Jan 2008 18:55 GMT

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

Reply to this Message

David Biddulph - 21 Jan 2008 19:08 GMT

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")

Signature

David Biddulph

> Hello
>
[quoted text clipped - 21 lines]
>
> TIA

Reply to this Message

pza@btinternet.com - 21 Jan 2008 19:17 GMT

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

>Hello
>
[quoted text clipped - 21 lines]
>
>TIA

Reply to this Message

Roger Govier - 22 Jan 2008 00:48 GMT

See responses to your other posting

Signature

Regards
Roger Govier

> Thank you for replying I have now managed to get this formula working
>
[quoted text clipped - 35 lines]
>>
>>TIA

Reply to this Message