StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing a number in a cell to generate a cell reference
text
Body
copied!<p>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.</p> <p>What I have is a sheet where each row has 8 cells of relevant data.</p> <p>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.</p> <p>For example, in Sheet1 I could have the following (fig 1):</p> <pre><code> | A | B | C | D | E | F | G | H -----+-----+-----+-----+-----+-----+-----+-----+----- 101 | Dep | 700 | Sta | 100 | Sta | 300 | Dep | 900 </code></pre> <p>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):</p> <pre><code> | A | B | C | D | -----+-----+-----+-----+-----+ 1 | "Row Number" | -----+-----+-----+-----+-----+ 2 | =A# | =B# | =D# | =C# | -----+-----+-----+-----+-----+ 3 | =E# | =F# | =H# | =G# | -----+-----+-----+-----+-----+ </code></pre> <p>And yes, I am aware those formulae above do not reference the other sheet - this was to save space.</p> <p>Which, if using the example row above, should look like this (fig 3):</p> <pre><code> | A | B | C | D | -----+-----+-----+-----+-----+ 1 | 101 | -----+-----+-----+-----+-----+ 2 | Dep | 700 | 100 | Sta | -----+-----+-----+-----+-----+ 3 | Sta | 300 | 900 | Dep | -----+-----+-----+-----+-----+ </code></pre> <p>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.</p> <p>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.</p> <p>Lastly I just want to say I want to avoid programming languages, like VBA, wherever possible.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload