Extend series using ref cell formula transposed

You have lots of options:

=INDIRECT(ADDRESS(1,ROW(A1)))
=INDEX($1:$1,ROW(A1))

Either of these copied down will work.

HTH,
Bernie
MS Excel MVP


"Gnomie" <[email protected]> wrote in message
news:[email protected]...
> I need to extend a fill series using ref's from sheet 2.
>
> Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
> is "across the columns"
>
> How do I extend the fill series to run : A1
> B1
> C1 etc.. ??
> Losing all my hair over this and wha'ts left of it is going Grey!!..
>
> If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
> master of info for 120 indiviual sheets...
>
> Any help would be appreciated
>
> Thanks
>
>

The formula you posted is not a working formula, so I assume you didn't
*copy* it into the post.
There are some typos?!?!

A comment on the formula:

Remember ... when you use Row() or Column() for incrementing purposes, their
use is strictly as a *numeric* value, *not* as a location reference!
Therefore eliminate any address (Sheet) reference.

Your formula could be written as:

=INDEX(Sheet2!$C:$C,COLUMN(D:D))

The Column(D:D) equates to 4 (which is all that you want), no matter what
sheet it's in.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Gnomie" <[email protected]> wrote in message
news:[email protected]...
"RagDyer" wrote:

> What do you say instead of giving you a fish, we try to teach you how to
> fish?

Oh yes Please!!! I really do prefer to actually understand what it is I'm
doing..

I played around with the 'suggestions' you made over and over.. tears of
frustration welling before I finally "got it"!!
I needed pencil and paper to really have it sink in properly in the end.

I guess 11pm isn't such a good time to try and learn stuff!!

Thank you so much.. the formula i ended up with was:
=INDEX(sheet2$C:$C,COLUMN(sheet2D:D)) dragging accros my page for the 60+
ref's, basic copy/paste and retype for Sheet2$D:$D then $E:$E etc..
Simple "find and replace" for sheet2, sheet 3 etc for teh 120 stores.

I have another master sheet to link to yet but I think I might be able to
solve that problem on my own now.

Thanks again.. so much

Gnomie

"RagDyer" wrote:

> What do you say instead of giving you a fish, we try to teach you how to
> fish?

Oh yes Please!!! I really do prefer to actually understand what it is I'm
doing..

I played around with the 'suggestions' you made over and over.. tears of
frustration welling before I finally "got it"!!
I needed pencil and paper to really have it sink in properly in the end.

I guess 11pm isn't such a good time to try and learn stuff!!

Thank you so much.. the formula i ended up with was:
=INDEX(sheet2$C:$C,COLUMN(sheet2D:D)) dragging accros my page for the 60+
ref's, basic copy/paste and retype for Sheet2$D:$D then $E:$E etc..
Simple "find and replace" for sheet2, sheet 3 etc for teh 120 stores.

I have another master sheet to link to yet but I think I might be able to
solve that problem on my own now.

Thanks again.. so much

Gnomie

Put this in the cell of your master sheet where you want the references to
start and copy it down

=OFFSET(Sheet2!$A$1,0,ROW(A1)-1)


"Gnomie" wrote:

> I need to extend a fill series using ref's from sheet 2.
>
> Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
> is "across the columns"
>
> How do I extend the fill series to run : A1
> B1
> C1 etc.. ??
> Losing all my hair over this and wha'ts left of it is going Grey!!..
>
> If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
> master of info for 120 indiviual sheets...
>
> Any help would be appreciated
>
> Thanks
>
>

What do you say instead of giving you a fish, we try to teach you how to
fish?

Index, being mostly non-volatile, should be the primary function of choice.

So, to start, we index where the data is coming *from*.

If it's coming from a row, we index *that* row, say Row10:
=INDEX(10:10,
This reference is good enough if we're going to drag (copy) this formula
*along* a row, across columns.
However, if we're going to copy this *down* a column, we must prevent the
row reference from automatically incrementing, so we must make the reference
absolute:
=INDEX($10:$10,

Let's start by saying that this formula will be copied down a column.
Next, we need the location (address) of *exactly* where in that row the data
will start to come from.
Say we want it to start at Column D (4th column).
We could write the formula:
=INDEX($10:$10,4)
Which will work fine ... for a *single* return of data.
To return Column E's data, write:
=INDEX($10:$10,5)
And so on ... and so on.
Tedious, right?
So we replace the "hard coded" column reference with something that will
increment automatically as it's copied down the column.
One example of such a reference would be Row().
So, we could now write the formula:
=INDEX($10:$10,ROW(4))
And copy this down a column, and get data returns from Row10 for each
succeeding column for each row it's copied down.

Now let's say we're going to copy this formula along a row, across columns,
to access the same data.
Although we could leave the references absolute, for the sake of this
discussion, lets do only what's necessary.
=INDEX(10:10,
If we use ROW(4) for the column reference, it *will not* increment as it's
dragged along a row.
COLUMN() *will* however.
So we write the formula:
=INDEX(10:10,COLUMN(D:D))
And copy across columns, along a row for as far as we need.

It's the exact same premise when dealing with copying columns.

Say we're copying Column D along a row, starting at Row4:
=INDEX($D:$D,COLUMN(D:D))

And if we're copying Column D down a column:
=INDEX(D:D,ROW(4))

Needless to say, the above could also be just as easily accomplished simply
by:
=D4
and dragging down, as could copying row to row.

Do you think you can answer your own question now?
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Gnomie" <[email protected]> wrote in message
news:[email protected]...
> Thanks.. that helps me understnad a little more about the formula I'm
using.
>
> I can now get the fill series to run down, but I can't get it to run
> across.. I only need 4 fills down for each of the 120 pages, yet over 60
> across and I've fiddled with the formula a fair bit yet do'nt quite
> understand what I'm supposed to change.
>
> I'd hoped that once I was given a push in the right direction( so to
speak)
> I could fathom the rest out.. to no avail.
>
> I've been using =OFFSET(Sheet2!$A$1,0,ROW(A1)-1)
>
> If someone could help I'd be grateful.
>
> Ta
>

Thank you both, Duke and Bernie..
Both work equally well and my grey hairs (what's left of them) thank you too!!

:-)

Gnomie

Bernie Deitrick wrote...
>You have lots of options:
>
>=INDIRECT(ADDRESS(1,ROW(A1)))

Better to skip the ADDRESS call and just use

=INDIRECT("R1C"&ROW(A1),0)

>=INDEX($1:$1,ROW(A1))

Much better since there are no volatile function calls. However, it's
easy to generalize this so the formulas could start in any row. If the
topmost formula were in cell X99 and should refer to cell A1, with X100
referring to B1, etc.

X99:
=INDEX($1:$1,ROWS(X$99:X99))

Thanks.. that helps me understnad a little more about the formula I'm using.

I can now get the fill series to run down, but I can't get it to run
across.. I only need 4 fills down for each of the 120 pages, yet over 60
across and I've fiddled with the formula a fair bit yet do'nt quite
understand what I'm supposed to change.

I'd hoped that once I was given a push in the right direction( so to speak)
I could fathom the rest out.. to no avail.

I've been using =OFFSET(Sheet2!$A$1,0,ROW(A1)-1)

If someone could help I'd be grateful.

Ta

Bernie Deitrick wrote...
>You have lots of options:
>
>=INDIRECT(ADDRESS(1,ROW(A1)))

Better to skip the ADDRESS call and just use

=INDIRECT("R1C"&ROW(A1),0)

>=INDEX($1:$1,ROW(A1))

Much better since there are no volatile function calls. However, it's
easy to generalize this so the formulas could start in any row. If the
topmost formula were in cell X99 and should refer to cell A1, with X100
referring to B1, etc.

X99:
=INDEX($1:$1,ROWS(X$99:X99))

The formula you posted is not a working formula, so I assume you didn't
*copy* it into the post.
There are some typos?!?!

A comment on the formula:

Remember ... when you use Row() or Column() for incrementing purposes, their
use is strictly as a *numeric* value, *not* as a location reference!
Therefore eliminate any address (Sheet) reference.

Your formula could be written as:

=INDEX(Sheet2!$C:$C,COLUMN(D:D))

The Column(D:D) equates to 4 (which is all that you want), no matter what
sheet it's in.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Gnomie" <[email protected]> wrote in message
news:[email protected]...
"RagDyer" wrote:

> What do you say instead of giving you a fish, we try to teach you how to
> fish?

Oh yes Please!!! I really do prefer to actually understand what it is I'm
doing..

I played around with the 'suggestions' you made over and over.. tears of
frustration welling before I finally "got it"!!
I needed pencil and paper to really have it sink in properly in the end.

I guess 11pm isn't such a good time to try and learn stuff!!

Thank you so much.. the formula i ended up with was:
=INDEX(sheet2$C:$C,COLUMN(sheet2D:D)) dragging accros my page for the 60+
ref's, basic copy/paste and retype for Sheet2$D:$D then $E:$E etc..
Simple "find and replace" for sheet2, sheet 3 etc for teh 120 stores.

I have another master sheet to link to yet but I think I might be able to
solve that problem on my own now.

Thanks again.. so much

Gnomie

"RagDyer" wrote:

> What do you say instead of giving you a fish, we try to teach you how to
> fish?

Oh yes Please!!! I really do prefer to actually understand what it is I'm
doing..

I played around with the 'suggestions' you made over and over.. tears of
frustration welling before I finally "got it"!!
I needed pencil and paper to really have it sink in properly in the end.

I guess 11pm isn't such a good time to try and learn stuff!!

Thank you so much.. the formula i ended up with was:
=INDEX(sheet2$C:$C,COLUMN(sheet2D:D)) dragging accros my page for the 60+
ref's, basic copy/paste and retype for Sheet2$D:$D then $E:$E etc..
Simple "find and replace" for sheet2, sheet 3 etc for teh 120 stores.

I have another master sheet to link to yet but I think I might be able to
solve that problem on my own now.

Thanks again.. so much

Gnomie

What do you say instead of giving you a fish, we try to teach you how to
fish?

Index, being mostly non-volatile, should be the primary function of choice.

So, to start, we index where the data is coming *from*.

If it's coming from a row, we index *that* row, say Row10:
=INDEX(10:10,
This reference is good enough if we're going to drag (copy) this formula
*along* a row, across columns.
However, if we're going to copy this *down* a column, we must prevent the
row reference from automatically incrementing, so we must make the reference
absolute:
=INDEX($10:$10,

Let's start by saying that this formula will be copied down a column.
Next, we need the location (address) of *exactly* where in that row the data
will start to come from.
Say we want it to start at Column D (4th column).
We could write the formula:
=INDEX($10:$10,4)
Which will work fine ... for a *single* return of data.
To return Column E's data, write:
=INDEX($10:$10,5)
And so on ... and so on.
Tedious, right?
So we replace the "hard coded" column reference with something that will
increment automatically as it's copied down the column.
One example of such a reference would be Row().
So, we could now write the formula:
=INDEX($10:$10,ROW(4))
And copy this down a column, and get data returns from Row10 for each
succeeding column for each row it's copied down.

Now let's say we're going to copy this formula along a row, across columns,
to access the same data.
Although we could leave the references absolute, for the sake of this
discussion, lets do only what's necessary.
=INDEX(10:10,
If we use ROW(4) for the column reference, it *will not* increment as it's
dragged along a row.
COLUMN() *will* however.
So we write the formula:
=INDEX(10:10,COLUMN(D:D))
And copy across columns, along a row for as far as we need.

It's the exact same premise when dealing with copying columns.

Say we're copying Column D along a row, starting at Row4:
=INDEX($D:$D,COLUMN(D:D))

And if we're copying Column D down a column:
=INDEX(D:D,ROW(4))

Needless to say, the above could also be just as easily accomplished simply
by:
=D4
and dragging down, as could copying row to row.

Do you think you can answer your own question now?
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Gnomie" <[email protected]> wrote in message
news:[email protected]...
> Thanks.. that helps me understnad a little more about the formula I'm
using.
>
> I can now get the fill series to run down, but I can't get it to run
> across.. I only need 4 fills down for each of the 120 pages, yet over 60
> across and I've fiddled with the formula a fair bit yet do'nt quite
> understand what I'm supposed to change.
>
> I'd hoped that once I was given a push in the right direction( so to
speak)
> I could fathom the rest out.. to no avail.
>
> I've been using =OFFSET(Sheet2!$A$1,0,ROW(A1)-1)
>
> If someone could help I'd be grateful.
>
> Ta
>

Thanks.. that helps me understnad a little more about the formula I'm using.

I can now get the fill series to run down, but I can't get it to run
across.. I only need 4 fills down for each of the 120 pages, yet over 60
across and I've fiddled with the formula a fair bit yet do'nt quite
understand what I'm supposed to change.

I'd hoped that once I was given a push in the right direction( so to speak)
I could fathom the rest out.. to no avail.

I've been using =OFFSET(Sheet2!$A$1,0,ROW(A1)-1)

If someone could help I'd be grateful.

Ta

Thank you both, Duke and Bernie..
Both work equally well and my grey hairs (what's left of them) thank you too!!

:-)

Gnomie

Put this in the cell of your master sheet where you want the references to
start and copy it down

=OFFSET(Sheet2!$A$1,0,ROW(A1)-1)


"Gnomie" wrote:

> I need to extend a fill series using ref's from sheet 2.
>
> Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
> is "across the columns"
>
> How do I extend the fill series to run : A1
> B1
> C1 etc.. ??
> Losing all my hair over this and wha'ts left of it is going Grey!!..
>
> If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
> master of info for 120 indiviual sheets...
>
> Any help would be appreciated
>
> Thanks
>
>

You have lots of options:

=INDIRECT(ADDRESS(1,ROW(A1)))
=INDEX($1:$1,ROW(A1))

Either of these copied down will work.

HTH,
Bernie
MS Excel MVP


"Gnomie" <[email protected]> wrote in message
news:[email protected]...
> I need to extend a fill series using ref's from sheet 2.
>
> Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
> is "across the columns"
>
> How do I extend the fill series to run : A1
> B1
> C1 etc.. ??
> Losing all my hair over this and wha'ts left of it is going Grey!!..
>
> If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
> master of info for 120 indiviual sheets...
>
> Any help would be appreciated
>
> Thanks
>
>

I need to extend a fill series using ref's from sheet 2.

Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
is "across the columns"

How do I extend the fill series to run : A1
B1
C1 etc.. ??
Losing all my hair over this and wha'ts left of it is going Grey!!..

If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
master of info for 120 indiviual sheets...

Any help would be appreciated

Thanks

Thanks.. that helps me understnad a little more about the formula I'm using.

I can now get the fill series to run down, but I can't get it to run
across.. I only need 4 fills down for each of the 120 pages, yet over 60
across and I've fiddled with the formula a fair bit yet do'nt quite
understand what I'm supposed to change.

I'd hoped that once I was given a push in the right direction( so to speak)
I could fathom the rest out.. to no avail.

I've been using =OFFSET(Sheet2!$A$1,0,ROW(A1)-1)

If someone could help I'd be grateful.

Ta

Bernie Deitrick wrote...
>You have lots of options:
>
>=INDIRECT(ADDRESS(1,ROW(A1)))

Better to skip the ADDRESS call and just use

=INDIRECT("R1C"&ROW(A1),0)

>=INDEX($1:$1,ROW(A1))

Much better since there are no volatile function calls. However, it's
easy to generalize this so the formulas could start in any row. If the
topmost formula were in cell X99 and should refer to cell A1, with X100
referring to B1, etc.

X99:
=INDEX($1:$1,ROWS(X$99:X99))

What do you say instead of giving you a fish, we try to teach you how to
fish?

Index, being mostly non-volatile, should be the primary function of choice.

So, to start, we index where the data is coming *from*.

If it's coming from a row, we index *that* row, say Row10:
=INDEX(10:10,
This reference is good enough if we're going to drag (copy) this formula
*along* a row, across columns.
However, if we're going to copy this *down* a column, we must prevent the
row reference from automatically incrementing, so we must make the reference
absolute:
=INDEX($10:$10,

Let's start by saying that this formula will be copied down a column.
Next, we need the location (address) of *exactly* where in that row the data
will start to come from.
Say we want it to start at Column D (4th column).
We could write the formula:
=INDEX($10:$10,4)
Which will work fine ... for a *single* return of data.
To return Column E's data, write:
=INDEX($10:$10,5)
And so on ... and so on.
Tedious, right?
So we replace the "hard coded" column reference with something that will
increment automatically as it's copied down the column.
One example of such a reference would be Row().
So, we could now write the formula:
=INDEX($10:$10,ROW(4))
And copy this down a column, and get data returns from Row10 for each
succeeding column for each row it's copied down.

Now let's say we're going to copy this formula along a row, across columns,
to access the same data.
Although we could leave the references absolute, for the sake of this
discussion, lets do only what's necessary.
=INDEX(10:10,
If we use ROW(4) for the column reference, it *will not* increment as it's
dragged along a row.
COLUMN() *will* however.
So we write the formula:
=INDEX(10:10,COLUMN(D:D))
And copy across columns, along a row for as far as we need.

It's the exact same premise when dealing with copying columns.

Say we're copying Column D along a row, starting at Row4:
=INDEX($D:$D,COLUMN(D:D))

And if we're copying Column D down a column:
=INDEX(D:D,ROW(4))

Needless to say, the above could also be just as easily accomplished simply
by:
=D4
and dragging down, as could copying row to row.

Do you think you can answer your own question now?
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Gnomie" <[email protected]> wrote in message
news:[email protected]...
> Thanks.. that helps me understnad a little more about the formula I'm
using.
>
> I can now get the fill series to run down, but I can't get it to run
> across.. I only need 4 fills down for each of the 120 pages, yet over 60
> across and I've fiddled with the formula a fair bit yet do'nt quite
> understand what I'm supposed to change.
>
> I'd hoped that once I was given a push in the right direction( so to
speak)
> I could fathom the rest out.. to no avail.
>
> I've been using =OFFSET(Sheet2!$A$1,0,ROW(A1)-1)
>
> If someone could help I'd be grateful.
>
> Ta
>

Thank you both, Duke and Bernie..
Both work equally well and my grey hairs (what's left of them) thank you too!!

:-)

Gnomie

"RagDyer" wrote:

> What do you say instead of giving you a fish, we try to teach you how to
> fish?

Oh yes Please!!! I really do prefer to actually understand what it is I'm
doing..

I played around with the 'suggestions' you made over and over.. tears of
frustration welling before I finally "got it"!!
I needed pencil and paper to really have it sink in properly in the end.

I guess 11pm isn't such a good time to try and learn stuff!!

Thank you so much.. the formula i ended up with was:
=INDEX(sheet2$C:$C,COLUMN(sheet2D:D)) dragging accros my page for the 60+
ref's, basic copy/paste and retype for Sheet2$D:$D then $E:$E etc..
Simple "find and replace" for sheet2, sheet 3 etc for teh 120 stores.

I have another master sheet to link to yet but I think I might be able to
solve that problem on my own now.

Thanks again.. so much

Gnomie

Put this in the cell of your master sheet where you want the references to
start and copy it down

=OFFSET(Sheet2!$A$1,0,ROW(A1)-1)


"Gnomie" wrote:

> I need to extend a fill series using ref's from sheet 2.
>
> Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
> is "across the columns"
>
> How do I extend the fill series to run : A1
> B1
> C1 etc.. ??
> Losing all my hair over this and wha'ts left of it is going Grey!!..
>
> If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
> master of info for 120 indiviual sheets...
>
> Any help would be appreciated
>
> Thanks
>
>

The formula you posted is not a working formula, so I assume you didn't
*copy* it into the post.
There are some typos?!?!

A comment on the formula:

Remember ... when you use Row() or Column() for incrementing purposes, their
use is strictly as a *numeric* value, *not* as a location reference!
Therefore eliminate any address (Sheet) reference.

Your formula could be written as:

=INDEX(Sheet2!$C:$C,COLUMN(D:D))

The Column(D:D) equates to 4 (which is all that you want), no matter what
sheet it's in.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Gnomie" <[email protected]> wrote in message
news:[email protected]...
"RagDyer" wrote:

> What do you say instead of giving you a fish, we try to teach you how to
> fish?

Oh yes Please!!! I really do prefer to actually understand what it is I'm
doing..

I played around with the 'suggestions' you made over and over.. tears of
frustration welling before I finally "got it"!!
I needed pencil and paper to really have it sink in properly in the end.

I guess 11pm isn't such a good time to try and learn stuff!!

Thank you so much.. the formula i ended up with was:
=INDEX(sheet2$C:$C,COLUMN(sheet2D:D)) dragging accros my page for the 60+
ref's, basic copy/paste and retype for Sheet2$D:$D then $E:$E etc..
Simple "find and replace" for sheet2, sheet 3 etc for teh 120 stores.

I have another master sheet to link to yet but I think I might be able to
solve that problem on my own now.

Thanks again.. so much

Gnomie

You have lots of options:

=INDIRECT(ADDRESS(1,ROW(A1)))
=INDEX($1:$1,ROW(A1))

Either of these copied down will work.

HTH,
Bernie
MS Excel MVP


"Gnomie" <[email protected]> wrote in message
news:[email protected]...
> I need to extend a fill series using ref's from sheet 2.
>
> Sheet 1 info needs to fill "down" the rows.. and the info it's referencing
> is "across the columns"
>
> How do I extend the fill series to run : A1
> B1
> C1 etc.. ??
> Losing all my hair over this and wha'ts left of it is going Grey!!..
>
> If it was only 1 worksheet I wouldn't bother to fill.. yet sheet 1 will be a
> master of info for 120 indiviual sheets...
>
> Any help would be appreciated
>
> Thanks
>
>