Questions on copying from one sheet to the other and coping every other cell.

[KatyLady]

First question answered on another thread. SORRY!!

Second, the columns in first row in sheet 2 is full, so I also need to continue same formula on the next sheet, sheet 3 but have it continue copying to same column on Sheet 2. How do i make this possible?

Formula is now =OFFSET($B$1,0,(ROW()-3)*2)

Thanks!

TKL

[Biff]

Hi!

Try this:

=OFFSET(B$1,,(ROW(1:1)-1)*2)

Copied down will return:

=B1
=D1
=F1
=H1
=J1
=L1
etc
etc

> Second, first time ever but the columns in first row in sheet 2 is
> full, so I also need to continue same formula on the next sheet, sheet
> 3 but have it continue copying to same column on Sheet 2. How do i make
> this possible?

Hmmm.....

Not following that!

Biff

"KatyLady" <[email protected]> wrote in
message news:[email protected]...
>
> I need to change a formula on sheet two reading...
>
> =OFFSET($B$1,0,ROW()-2)
>
> which I drag down the column and it copies text like this
>
>
> =B1
> =C1
> =D1
> etc.
>
> I need to make it every other cell in the row instead of every cell.
>
> For instance now when I drag it, it will go down the column and copy
> text from
>
> =B1
> =D1
> =F1
> +H1
> Etc....
>
> Second, first time ever but the columns in first row in sheet 2 is
> full, so I also need to continue same formula on the next sheet, sheet
> 3 but have it continue copying to same column on Sheet 2. How do i make
> this possible?
>
> Thanks!
>
> TKL
>
>
> --
> KatyLady
> ------------------------------------------------------------------------
> KatyLady's Profile:
> http://www.excelforum.com/member.php...o&userid=23841
> View this thread: http://www.excelforum.com/showthread...hreadid=376672
>

[Max]

One way to try ..

Assuming data is to be extracted from the sheets below in sequence, and only
from alternate cells in row1 within each sheet starting from B1, i.e. from
B1, D1, F1, H1, J1, ... :

Sheet2's B1:IV1
Sheet3's B1:IV1
Sheet4's B1:IV1
Sheet5's B1:IV1
etc

Put in any starting cell in Sheet2* (other than within row1):

=OFFSET(INDIRECT("Sheet"&INT((ROWS($A$1:A1)-1)/128)+2&"!B1"),,MOD(ROWS($A$1:
A1)-1,128)*2)

*starting cell can actually be in any sheet, not neccessarily Sheet2

Copy down as desired

Outputs will be returned as desired, in 128 consecutive rows per sheet, in
the sheetname sequence as above, i.e.: Sheet2, Sheet3, Sheet4, etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"KatyLady" <[email protected]> wrote in
message news:[email protected]...
>
> I need to change a formula on sheet two reading...
>
> =OFFSET($B$1,0,ROW()-2)
>
> which I drag down the column and it copies text like this
>
>
> =B1
> =C1
> =D1
> etc.
>
> I need to make it every other cell in the row instead of every cell.
>
> For instance now when I drag it, it will go down the column and copy
> text from
>
> =B1
> =D1
> =F1
> +H1
> Etc....
>
> Second, first time ever but the columns in first row in sheet 2 is
> full, so I also need to continue same formula on the next sheet, sheet
> 3 but have it continue copying to same column on Sheet 2. How do i make
> this possible?
>
> Thanks!
>
> TKL
>
>
> --
> KatyLady
> ------------------------------------------------------------------------
> KatyLady's Profile:
http://www.excelforum.com/member.php...o&userid=23841
> View this thread: http://www.excelforum.com/showthread...hreadid=376672
>

[Max]

"Biff" wrote:
....
> > Second, first time ever but the columns in first row in sheet 2 is
> > full, so I also need to continue same formula on the next sheet, sheet
> > 3 but have it continue copying to same column on Sheet 2. How do i make
> > this possible?

> Not following that!

Think the OP wants the formula to continue extracting from the first cell in
next sheet, i.e. from Sheet3's B1:IV1, once the last, righmost cell within
Sheet2's B1:IV1 is exhausted. And to continue thereof in this fashion from
Sheet4's B1:IV1, Sheet5's B1:IV1, etc ...

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Biff]

Hmmm....

I don't know!

If that's what they want, then why are they essentially transposing row 1
every othe cell?

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> "Biff" wrote:
> ...
>> > Second, first time ever but the columns in first row in sheet 2 is
>> > full, so I also need to continue same formula on the next sheet, sheet
>> > 3 but have it continue copying to same column on Sheet 2. How do i make
>> > this possible?
>
>> Not following that!
>
> Think the OP wants the formula to continue extracting from the first cell
> in
> next sheet, i.e. from Sheet3's B1:IV1, once the last, righmost cell within
> Sheet2's B1:IV1 is exhausted. And to continue thereof in this fashion from
> Sheet4's B1:IV1, Sheet5's B1:IV1, etc ...
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

"Biff" wrote:
....
> I don't know!
> If that's what they want, then
> why are they essentially transposing row 1 every othe cell?

I don't know why the OP wants it this way either <g>,
but that's the way I interp'ed that part of the post/request literally ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

> Outputs will be returned as desired, in 128 consecutive rows per sheet, in
> the sheetname sequence as above, i.e.: Sheet2, Sheet3, Sheet4, etc

Just some clarifications: What the above means is that if we put the formula
in say, Sheet2's B3, and then copy down, we'll be extracting the same
returns as having:

In B3: =Sheet2!B1
In B4: =Sheet2!D1
In B5: =Sheet2!F1
....
In B129: =Sheet2!IT1
In B130: =Sheet2!IV1
In B131: =Sheet3!B1 (< auto-transition to Sheet3)
In B132: =Sheet3!D1
....
In B257: =Sheet3!IT1
In B258: =Sheet3!IV1
In B259: =Sheet4!B1 (< auto-transition to Sheet4)
In B260: =Sheet4!D1
and so on ..

Should you get #REF! when you copy down, that probably means either the
sheetname(s) doesn't exist yet, or the actual sheetname(s) doesn't match
with ones evaluated by the INDIRECT (perhaps a stray space in the actual
sheetname ?). The assumed sheetnames are: Sheet2, Sheet3, Sheet4, etc (Note
that there's no space between the word "Sheet" and the number. Case is not
important.)

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[KatyLady]

Biff,

It's going to be something like this

=OFFSET(PRODUCTS2!$B$1,0,(ROW()-3)*2)

but it is returning
0
in the cells
when i have text in

B1
D1
F1


It's an order form, going down first column is all the categories, going down the first row is the categories, underneath is the description, and in between each category is the sales price of description....Example: (SP=sales price & C = Column ) I left out the descriptions and the $ amount. See how I want the cat to follow going down 1st column but not have the $SP going down the first column.

C-A1 C-B1 C-C1 C-D1 C-E1 C-F1 C-G1 C-H1
Cat ALBUSSON $SP ALBERTSON $SP ALEXANDRA $SP BLOCK
ALBUSSON
Albertson
Alexandra
Block

Hope that helps and doesn't confuse it more. Thanks!

KL

"Biff" wrote:
....
> I don't know!
> If that's what they want, then
> why are they essentially transposing row 1 every othe cell?

I don't know why the OP wants it this way either <g>,
but that's the way I interp'ed that part of the post/request literally ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

If this is in Sheet2, A1:H1
> Cat ALBUSSON $SP ALBERTSON $SP ALEXANDRA $SP BLOCK

and you want this extracted from A2 down:
> ALBUSSON
> Albertson
> Alexandra
> Block

Put in A2 (the earlier suggested formula in the other branch):

=OFFSET(INDIRECT("Sheet"&INT((ROWS($A$1:A1)-1)/128)+2&"!B1"),,MOD(ROWS($A$1:
A1)-1,128)*2)

Copy down

The formula will work all when copied all the way down as explained in the
other branch of the thread, extracting from (once Sheet2's B1:IV1 is
exhausted): Sheet3's B1:IV1, then from Sheet4's B1:IV1, etc as per your
original post's 2nd part request.

(I'm not sure why you didn't try it earlier or why it didn't work for you if
you did)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Typo: Line
> The formula will work all when copied all the way down

should read as:
> The formula will work when copied all the way down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

> =OFFSET(PRODUCTS2!$B$1,0,(ROW()-3)*2)

The above which uses "ROW()" is sensitive to the cell you're putting the
formula in, and copying down from (assuming it is the starting cell's
formula)

Maybe try this as the starting cell formula instead, and copy down:
=OFFSET(Products2!$B$1,0,(ROWS($A$1:A1)-1)*2)

The above will extract properly when you copy down, but only until the last,
rightmost cell in Products2!$B$1:IV1 (after copying down 128 rows, you'll
exhaust the range in the sheet and you'll get only #REF!)

If you want it to continue beyond to extract from the next sheet in turn,
i.e. Products3!$B$1:IV1, then from Products4!$B$1:IV1, and so on ...
try this formula instead in the starting cell, and copy down:

=OFFSET(INDIRECT("Products"&INT((ROWS($A$1:A1)-1)/128)+2&"!B1"),,MOD(ROWS($A
$1:A1)-1,128)*2)

(Formula is the same as the earlier one, but amended to suit your actual?
sheetnames: Products2, Products3, Products4 instead of the assumed: Sheet2,
Sheet3, Sheet4, etc
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----