Using data drom one cell as part of a formula in another.

[WightRob]

I am trying to use the data from one cell as part of a formula i
another.

For example:
I have a three sheet workbook.
The first sheet contains lists of data to be used in the followin
sheets.
i.e. Column 1 is List 1, Column 2 is List 2 etc
The rows contain values.
i.e. Row I contains Value 1, Row 2 contains Value 2 etc.

The following sheets, named ListData1, ListData2 etc must contain dat
from these lists laid out differently.
I can achieve this by entering the formulae to refer back to Sheet1.
e.g. For ListData1 ... =Sheet1!A1, =Sheet1!A2 etc
For ListData2 ... =Sheet1!B1, =Sheet1!B2 etc

For each new list on Sheet1, I create new a new ListData worksheet b
copying the last existing one.
The problem is for each new ListData sheet I create, I must re-enter o
edit the formulae to reference the next list from Sheet 1.

What I would like to do is enter the letter reference in a separat
cell on the ListData worksheets,
i.e. the letter "A" on ListData1 in Cell A1
This would then be read into the formulae,
i.e.
=Sheet1!(contents of cell A1)1, =Sheet1!(contents of cell A1)2 etc

Then I need only edit one cell on each new ListData worksheet.

This seems to be such a useful feature that I'm sure it must b
possible to do but I haven't been able to find it yet.
Any help would be appreciated.

Regards
Ro

Attachment filename: usingcelldatainformulae.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66343

 

[Frank Kabel]

Hi
try
=INDIRECT("'sheet1'!" & A1 & ROW(1:1))
and copy this down