Extend series using ref cell formula transposed
 

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" wrote in message
...
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

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

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

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" wrote in message
...
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

"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

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" wrote in message
...
"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

Extend series using ref cell formula transposed
 

Duke,
Thanks for your answer. I need to alter the formula so that I can take a
range from sub document rows and pull them into master doc rows. From your
response I understand how to fill down in such a case, but how do I fill
across? (sorry...beginner here).

To be more specific...I need to take range A1:E1 from subs, and take it into
master doc range A1:E1. The subs will be updated every week.

Thanks in advance.
Brett

"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

Extend series using ref cell formula transposed
 

I am trying to fill rows down based on data in a worksheet that is across
like in this example. I understand how to use the offset function, but I need
to add this function to a logical if/then function. My if/then function
references a certain cell in worksheet 1 and if the argument is true it
returns a certain value in a cell in worksheet 2. Worksheet two has data in 5
columns across and several rows down. I need to be able to drag the if/then
formula down in worksheet 1 and have it reference across (column a, b, c, d,
e) from worksheet 2.

Here is my if then function:

=IF(($A$6="BVCOR"),'[2007 PAYMENTS.XLS]bvcor'!$K8,IF((A6="bvci"),'[2007
PAYMENTS.XLS]BVCI'!$K$8,0))

Can you show me how to add the offset function to this so it will reference
cells in a row down?

Thanks



"Duke Carey" wrote:

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