For clarity, let's assume you have the sheetnames in A1 down,
the actual cell refs in B1 down, eg

in A1: X, in B1: A1
in A2: Y, in B2: E5

You could place this in C1:
=INDIRECT("'"&A1&"'!"&B1)
and copy down to retrieve
in C1, the equivalent of: =X!A1
in C2, the equivalent of: =Y!E5
and so on
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
Max,
A few questions.
1. I gave you the wrong info, worksheet info is in cells A1 and B1. A1
being the sheet and B1 the row number.
2. In C1 I need to have the info in from the Cell A1 on the original
sheet.
This works ok, but when I drag the cell C1 to C2 the ref in the
orignal formula still keeps the first A$1$ and B$1$ the same and I
have to re reference the cell to A$2$ and B$2$

First question is how can I get the cell values to increment so that
different cells are picked up from different sheets.

Second question is when I drag the cell the values in the rows and
column part of the formula increment but I require it to stay at A$:A
or 1:1

The answer at present is to do it manually but any help in get a
working formula would be great recieved.
Thanks
Stephen