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

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

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

"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 <atyahoo<dotcom
----

Hmmm....

I don't know!

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

Biff

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

"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 <atyahoo<dotcom
----

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




Max Wrote:
"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 <atyahoo<dotcom
----


--
KatyLady
------------------------------------------------------------------------
KatyLady's Profile: http://www.excelforum.com/member.php...o&userid=23841
View this thread: http://www.excelforum.com/showthread...hreadid=376672

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

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

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 a 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 <atyahoo<dotcom
----