| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I drag a hyperlink series to fill instead of copy? | Excel Worksheet Functions | |||
| Drag to fill a formula - problem! | Excel Worksheet Functions | |||
| Drag to fill row with column values | Excel Discussion (Misc queries) | |||
| if you drag the fill handle will it decrement the series | Excel Discussion (Misc queries) | |||
| if you drag the fill handle will it decrement the series | Excel Discussion (Misc queries) | |||
Linear Mode
