Drag and fill from other sheets
 

here is my situation,

I have some information on two sheets...sheet 1 and sheet 2...in sheet 1,
colum A i would like to the cell to show the number for sheet 2 colum
B...Here is the thing, i would like to drag and use the fill fuction but i
want it to display the following column not the next cell on the row...

this is what i have

Ex
Sheet 1, cell A1, (=Sheet2!B1)
if i drag and use the fill fuction the following cell give me
(=Sheet2!B2)(=Sheet2!B3) etc...i would like it to read
(=Sheet2!C2)(=Sheet1!D2) etc...

any suggestions...

thanks for the help in advance

LJ

Drag and fill from other sheets
 

On Jul 27, 7:26 am, LJ wrote:
here is my situation,

I have some information on two sheets...sheet 1 and sheet 2...in sheet 1,
colum A i would like to the cell to show the number for sheet 2 colum
B...Here is the thing, i would like to drag and use the fill fuction but i
want it to display the following column not the next cell on the row...

this is what i have

Ex
Sheet 1, cell A1, (=Sheet2!B1)
if i drag and use the fill fuction the following cell give me
(=Sheet2!B2)(=Sheet2!B3) etc...i would like it to read
(=Sheet2!C2)(=Sheet1!D2) etc...

any suggestions...

thanks for the help in advance

LJ

Did you mean...

(=Sheet2!C2)(=Sheet2!D2) etc...?

If so then one way is...

=Sheet2!B1
(in Sheet1!A1) then...

=OFFSET(Sheet2!$B$1,0,ROWS($1:1),1,1)
(in Sheet1!A2) filled down as far as needed.

Or, if you don't want to see the zeros that result when the Sheet2!
cell is blank then use...

=IF(OFFSET(Sheet2!$B$1,0,ROWS($1:1),1,1)<"",OFFSE T(Sheet2!$B
$1,0,ROWS($1:1),1,1),"")

Ken Johnson

Drag and fill from other sheets
 

Is there a way to make the cell come up blank when you insert the formula
instead of the N/A ....

Thank you in advance
Lj

"Ken Johnson" wrote:

On Jul 27, 7:26 am, LJ wrote:
here is my situation,

I have some information on two sheets...sheet 1 and sheet 2...in sheet 1,
colum A i would like to the cell to show the number for sheet 2 colum
B...Here is the thing, i would like to drag and use the fill fuction but i
want it to display the following column not the next cell on the row...

this is what i have

Ex
Sheet 1, cell A1, (=Sheet2!B1)
if i drag and use the fill fuction the following cell give me
(=Sheet2!B2)(=Sheet2!B3) etc...i would like it to read
(=Sheet2!C2)(=Sheet1!D2) etc...

any suggestions...

thanks for the help in advance

LJ

Did you mean...

(=Sheet2!C2)(=Sheet2!D2) etc...?

If so then one way is...

=Sheet2!B1
(in Sheet1!A1) then...

=OFFSET(Sheet2!$B$1,0,ROWS($1:1),1,1)
(in Sheet1!A2) filled down as far as needed.

Or, if you don't want to see the zeros that result when the Sheet2!
cell is blank then use...

=IF(OFFSET(Sheet2!$B$1,0,ROWS($1:1),1,1)<"",OFFSE T(Sheet2!$B
$1,0,ROWS($1:1),1,1),"")

Ken Johnson

Drag and fill from other sheets
 

On Sep 4, 8:26*am, LJ wrote:
Is there a way to make the cell come up blank when you insert the formula
instead of the N/A ....

Thank you in advance
Lj

"Ken Johnson" wrote:
On Jul 27, 7:26 am, LJ wrote:
here is my situation,

I have some information on two sheets...sheet 1 and sheet 2...in sheet 1,
colum A i would like to the cell to show the number for sheet 2 colum
B...Here is the thing, i would like to drag and use the fill fuction but i
want it to display the following column not the next cell on the row....

this is what i have

Ex
Sheet 1, cell A1, (=Sheet2!B1)
if i drag and use the fill fuction the following cell give me
(=Sheet2!B2)(=Sheet2!B3) etc...i would like it to read
(=Sheet2!C2)(=Sheet1!D2) etc...

any suggestions...

thanks for the help in advance

LJ

Did you mean...

(=Sheet2!C2)(=Sheet2!D2) etc...?

If so then one way is...

=Sheet2!B1
(in Sheet1!A1) then...

=OFFSET(Sheet2!$B$1,0,ROWS($1:1),1,1)
(in Sheet1!A2) filled down as far as needed.

Or, if you don't want to see the zeros that result when the Sheet2!
cell is blank then use...

=IF(OFFSET(Sheet2!$B$1,0,ROWS($1:1),1,1)<"",OFFSE T(Sheet2!$B
$1,0,ROWS($1:1),1,1),"")

Ken Johnson

Try...

=IF(ISNA(OFFSET(Sheet2!$B$1,0,ROWS($1:2),1,1)),"", IF(OFFSET(Sheet2!$B
$1,0,ROWS($1:2),1,1)="","",OFFSET(Sheet2!$B$1,0,RO WS($1:2),1,1)))

Ken Johnson