Help.. Drag reference to the right does not work...

[lysignup]

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

 

[Roger Govier]

Hi

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

 

[lysignup]

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

Hi

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

 

[lysignup]

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]

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

 

[lysignup]

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]

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.

 

[lysignup]

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]

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.

 

[lysignup]

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]

Hi

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

 

[Roger Govier]

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