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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lysignup@gmail.com - 30 Nov 2006 00:38 GMT

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

Reply to this Message

Roger Govier - 30 Nov 2006 00:48 GMT

Hi

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

Signature

Regards

Roger Govier

> Hi,
>
[quoted text clipped - 14 lines]
>
> What is the trick to fix it? Thanks!!!

Reply to this Message

lysignup@gmail.com - 30 Nov 2006 06:12 GMT

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

> Hi
>
[quoted text clipped - 24 lines]
> >
> > What is the trick to fix it? Thanks!!!

Reply to this Message

lysignup@gmail.com - 30 Nov 2006 21:00 GMT

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

> Hi
>
[quoted text clipped - 24 lines]
> >
> > What is the trick to fix it? Thanks!!!

Reply to this Message

Roger Govier - 30 Nov 2006 21:42 GMT

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

Signature

Regards

Roger Govier

> Hi Roger,
>
[quoted text clipped - 48 lines]
>> >
>> > What is the trick to fix it? Thanks!!!

Reply to this Message

lysignup@gmail.com - 30 Nov 2006 22:05 GMT

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!

> Hi
>
[quoted text clipped - 61 lines]
> >> >
> >> > What is the trick to fix it? Thanks!!!

Reply to this Message

Roger Govier - 30 Nov 2006 22:21 GMT

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

Signature

Regards

Roger Govier

>I see.. so, how would you modify the formula or any other type formula
> that could make this work?
[quoted text clipped - 94 lines]
>> >> >
>> >> > What is the trick to fix it? Thanks!!!

Reply to this Message

lysignup@gmail.com - 01 Dec 2006 16:27 GMT

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

> Hi
> > Also, if you don't mind.. :) for education purposes, how do you
[quoted text clipped - 120 lines]
> >> >> >
> >> >> > What is the trick to fix it? Thanks!!!

Reply to this Message

Roger Govier - 01 Dec 2006 21:40 GMT

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.

Signature

Regards

Roger Govier

> Thank you for the interpretation! I was confused with the 2. Now I
> understand.
[quoted text clipped - 151 lines]
>> >> >> >
>> >> >> > What is the trick to fix it? Thanks!!!

Reply to this Message

lysignup@gmail.com - 04 Dec 2006 22:35 GMT

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!

> Hi
>
[quoted text clipped - 171 lines]
> >> >> >> >
> >> >> >> > What is the trick to fix it? Thanks!!!

Reply to this Message

Roger Govier - 04 Dec 2006 23:29 GMT

Hi

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

Signature

Regards

Roger Govier

> Hi Roger,
>
[quoted text clipped - 195 lines]
>> >> >> >> >
>> >> >> >> > What is the trick to fix it? Thanks!!!

Reply to this Message

Roger Govier - 05 Dec 2006 10:25 GMT

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

Signature

Regards

Roger Govier

> Hi
>
[quoted text clipped - 205 lines]
>>> >> >> >> >
>>> >> >> >> > What is the trick to fix it? Thanks!!!

Reply to this Message