Tricky Referencing Problem

[Robby87]

Hi there,

I have a spreadsheet with many rows and columns with lines that look like this:
.........A...............B...............C...............D...............E
.=Data!D28..=Data!E28..=Data!F28..=Data!R28..=Data!Q28
.=Data!D32..=Data!E32..=Data!F32..=Data!R32..=Data!Q32
=Data!D186.=Data!E186.=Data!F186.=Data!R186.=Data!Q186


Goal:
I want to avoid typing out =Data![cellreference] each time.

Problems:
-The column references are constant. They will always go D, E, F, R, Q (plus more in the actual spreadsheet.
-The Row references are not organized by any pattern. For example, if row 1 references Data!D28, row 2 won't reference D29, but rather, a specific row that I want to enter in.

Does anyone know a quick way to do this?

Thanks for your help in advance.
Jamie

 

[Andrew Poulsom]

In A1:E1 enter the letters D, E, F, R and Q. In F2:F4 enter the number 28, 32 and 186. In A2 enter:

=INDIRECT("Data!"&A$1&$F2)

and copy down and across.

If you used column numbers instead of column letters you could use INDEX instead of the volatile INDIRECT.

 

[PA HS Teacher]

I would Name a large Range A1:IV2000 or however big you need on the data sheet and Name it Dat, (You could even get away with Naming it a single letter if you want).

Then I'd use Index

In colunn A, pasted down:
=Index(Dat,28,4)

In Column B, pasted down:
=Index(Dat,28,5)

Then replace just the row number for each cell.

I don't know how many cells you are taking about, but you could go one step further and store just the row numbers on a separate sheet. (it's easier to enter.) then

=Index(Dat,28,4) could be:

=Index(Dat,Sheet1!A1,4) pasted down. Where Sheet1 contains the row numbers