Thank you for the interpretation! I was confused with the 2. Now I
understand.

Another question I have.

How would you list the alphabets vertically along with the row number
so that I would know row 15 equals to O2 (2 is insignificant), 16 is
P2... row 28 equals to AB, row 29 = AC and so on?

So, it would be
row number alphabetical
1 A
2 B
3 C
4 D
etc...

Thanks again!!

Roger Govier wrote:
Hi
Also, if you don't mind.. :) for education purposes, how do you
interprete the formula you just provide?

=INDIRECT("'Data'!G"&COLUMN(B2))
Column() returns the column number of the column in which you enter the
expression =COLUMN() so you get 1, for A, 2 for B etc.
=COLUMN(B:B) will return an answer of 2, but I just used B2, rather than
B:B. The 2 is insignificant, it could just as easily have been B1 or B23

Because the B2 is relative (not $B2), then it will adjust as the formula
is copied across the sheet and become C2, D2 etc., which will return a
number 3 and 4 etc in response to the COLUMN() part.

The whole term inside the brackets of the formula posted, therefore
becomes Data!G2, Data!G3 etc.

For your sets of data, just make the formula in the first cell the
column letter of the first row from G that you want to return so for G15
it would be COLUMN(O2) for G28 it would be COLUMN(AB2) and so on.

--
Regards

Roger Govier


wrote in message
ups.com...
I see.. so, how would you modify the formula or any other type formula
that could make this work?

I have multiple sheets in a workbook that refers to the same data
sheet
in the workbook.

Column G is the data row I need. Each sheet refers to different set of
data (about 12) rows from the data sheet.

Example - Sheet 1

Cell A1 - H1
will be refering to data sheet G15 - G26;

Sheet 2
cell A1 - H1
will be refering to data sheet G28 - G39

Sheet 3
cell A1 - H1
will be refering to data sheet G41 - G52

and so on...

Also, if you don't mind.. :) for education purposes, how do you
interprete the formula you just provide?

Thank you!

Roger Govier wrote:
Hi

Amend formula to
=INDIRECT("'Data'!G"&COLUMN(ET2))

The formula, in this form, will only work as long as the cell to be
referenced doesn't get any higher than row 256, as 256 is currently
the
largest number of columns available
--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Quick question..

How would you modify the formula if you have another sheet in the
same
workbook
that but refer to the same sheet but different cell?

Previously, SHEET1 has these for A1 - H1
=data!$G2 for A1
=data!$G3 for B1
=data!$G4 for C1
=data!$G5 for D1

How about SHEET2 that refers to

=data!$G150 for A1
=data!$G151 for B1
=data!$G152 for C1
=data!$G153 for D1

Thank you!!!

Roger Govier wrote:
Hi

In A1 enter
=INDIRECT("'Data'!G"&COLUMN(B2))
drag across through B1:H1
--
Regards

Roger Govier


wrote in message
ps.com...
Hi,

I tried to drag the reference below for cell A1 through H1 but
it
doesn't work. It only repeats the range G2 - G5. No increment
across
to
the right.

=data!$G2 for A1
=data!$G3 for B1
=data!$G4 for C1
=data!$G5 for D1

it turns out... which i do not wish.
=data!$G2 for E1
=data!$G3 for F1
etc

What is the trick to fix it? Thanks!!!