Is there an Excel wizard out there that can help me?
I am trying to use the data from one cell as part of a formula in another.
For example:
I have a three sheet workbook.
The first sheet contains lists of data to be used in the following 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 data 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 by copying the last existing one.
The problem is for each new ListData sheet I create, I must re-enter or edit the formulae to reference the next list from Sheet 1.
What I would like to do is enter the letter reference in a separate 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 be possible to do but I haven't been able to find it yet.
Any help would be appreciated.
Regards
Rob