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!!!

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!!!

Thank you so much Roger!! It works amazingly!!

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!!!

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!!!

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!!!

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!!!

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!!!

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!!!

Hi

If you mean you wanted to increment the alphabet from A to Z as you
copied down the rows use
=CHAR(ROW(A1)+64)
Row(A1) is 1 - here the row is significant, not the column letter.
This would return CHAR(65) which is A. As you copy down, row would
increase through to row A26, and give CHAR(90) which is Z.

The alternative way of making your increment going across the columns,
rather than having to work out the column number first, would be
=COLUMN(A1)+N where N is your starting value - 1
i.e. to pick up G15, it would "G"&COLUMN(A1)+14. This would increase to
G16, G17 etc. as you move across the sheet.

--
Regards

Roger Govier


wrote in message
s.com...
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!!!

Hi Roger,

Thank you for these! Another question, after Z, if you want to do ...Z,
AA, AB, AC, AD... BA, BB.. how would you modify the formula copied down
the rows?

Thanks!

Roger Govier wrote:
Hi

If you mean you wanted to increment the alphabet from A to Z as you
copied down the rows use
=CHAR(ROW(A1)+64)
Row(A1) is 1 - here the row is significant, not the column letter.
This would return CHAR(65) which is A. As you copy down, row would
increase through to row A26, and give CHAR(90) which is Z.

The alternative way of making your increment going across the columns,
rather than having to work out the column number first, would be
=COLUMN(A1)+N where N is your starting value - 1
i.e. to pick up G15, it would "G"&COLUMN(A1)+14. This would increase to
G16, G17 etc. as you move across the sheet.

--
Regards

Roger Govier


wrote in message
s.com...
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!!!

Hi

Try
=IF(ROW()26,
CHAR(INT(ROW()/26)+64)&CHAR(MOD(ROW(),26)+64),
CHAR(ROW(A1)+64))
--
Regards

Roger Govier


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

Thank you for these! Another question, after Z, if you want to do
...Z,
AA, AB, AC, AD... BA, BB.. how would you modify the formula copied
down
the rows?

Thanks!

Roger Govier wrote:
Hi

If you mean you wanted to increment the alphabet from A to Z as you
copied down the rows use
=CHAR(ROW(A1)+64)
Row(A1) is 1 - here the row is significant, not the column letter.
This would return CHAR(65) which is A. As you copy down, row would
increase through to row A26, and give CHAR(90) which is Z.

The alternative way of making your increment going across the
columns,
rather than having to work out the column number first, would be
=COLUMN(A1)+N where N is your starting value - 1
i.e. to pick up G15, it would "G"&COLUMN(A1)+14. This would increase
to
G16, G17 etc. as you move across the sheet.

--
Regards

Roger Govier


wrote in message
s.com...
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!!!

For the letter A to appear in the first cell you enter the formula,
regardless of which row you begin, I should have made it ROW(A1) in
each case.

=IF(ROW(A1)26,
CHAR(INT(ROW(A1)/26)+64)&CHAR(MOD(ROW(A1),26)+64),
CHAR(ROW(A1)+64))

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

Try
=IF(ROW()26,
CHAR(INT(ROW()/26)+64)&CHAR(MOD(ROW(),26)+64),
CHAR(ROW(A1)+64))
--
Regards

Roger Govier


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

Thank you for these! Another question, after Z, if you want to do
...Z,
AA, AB, AC, AD... BA, BB.. how would you modify the formula copied
down
the rows?

Thanks!

Roger Govier wrote:
Hi

If you mean you wanted to increment the alphabet from A to Z as you
copied down the rows use
=CHAR(ROW(A1)+64)
Row(A1) is 1 - here the row is significant, not the column letter.
This would return CHAR(65) which is A. As you copy down, row would
increase through to row A26, and give CHAR(90) which is Z.

The alternative way of making your increment going across the
columns,
rather than having to work out the column number first, would be
=COLUMN(A1)+N where N is your starting value - 1
i.e. to pick up G15, it would "G"&COLUMN(A1)+14. This would increase
to
G16, G17 etc. as you move across the sheet.

--
Regards

Roger Govier


wrote in message
s.com...
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!!!