Using a number in a cell to generate a cell reference

Question

What I want to do might be better achieved with a database, however I have very limited experience with them, and only have to change the information infrequently.

What I have is a sheet where each row has 8 cells of relevant data.

What I want to do in another sheet is to enter a number into 1 cell, and have a group of cells below use that number to reference data in the other sheet.

For example, in Sheet1 I could have the following (fig 1):

     |  A  |  B  |  C  |  D  |  E  |  F  |  G  |  H
-----+-----+-----+-----+-----+-----+-----+-----+-----
 101 | Dep | 700 | Sta | 100 | Sta | 300 | Dep | 900

What I want to achieve in sheet 2, by typing the row number into 1 cell, is to have the data in those 8 cells copied below, for example (fig 2):

     |  A  |  B  |  C  |  D  |
-----+-----+-----+-----+-----+
  1  | "Row Number"          |
-----+-----+-----+-----+-----+
  2  | =A# | =B# | =D# | =C# |
-----+-----+-----+-----+-----+
  3  | =E# | =F# | =H# | =G# |
-----+-----+-----+-----+-----+

And yes, I am aware those formulae above do not reference the other sheet - this was to save space.

Which, if using the example row above, should look like this (fig 3):

     |  A  |  B  |  C  |  D  |
-----+-----+-----+-----+-----+
  1  | 101                   |
-----+-----+-----+-----+-----+
  2  | Dep | 700 | 100 | Sta |
-----+-----+-----+-----+-----+
  3  | Sta | 300 | 900 | Dep |
-----+-----+-----+-----+-----+

So, in that example above (fig 3), what do I need to put in as a formula in cells A2-D2 & A3-D3 to automatically use the number in A1 as part of the cell reference to print the data from the other sheet.

Does that make sense? I hope so because I have over 300 lines to enter into my 1st sheet and another 70 lines x 7 blocks of columns on the second sheet.

Lastly I just want to say I want to avoid programming languages, like VBA, wherever possible.

Answer 1

Check out the INDIRECT() function.

For cell A2 in your example on the second sheet, enter:

=INDIRECT("Sheet1!"&"A"&$A$1)

Expand this formula to the apply to other target cells by changing the "&"A" portion to reference columns B, C, D, etc. from Sheet1 as needed in your grid per the following example:

=INDIRECT("Sheet1!"&"B"&$A$1)
=INDIRECT("Sheet1!"&"C"&$A$1)
=INDIRECT("Sheet1!"&"D"&$A$1)

These formulas will reference your selected "Row Number" in cell A1 and pull the related data from Sheet1 into Sheet2.

Answer 2

You can do this using the INDIRECT function

Returns the reference specified by a text string. 
References are immediately evaluated to display their contents. 
Use INDIRECT when you want to change the reference to a cell within a 
formula without changing the formula itself.

http://office.microsoft.com/en-gb/excel-help/indirect-HP005209139.aspx