Hello,

I have a problem while dragging the formula to the next cells.

The formula in A1 of sheet 1 is ='Sheet2'!C34
The formula in B1 of sheet 1 is ='Sheet2'!F34
The formula in C1 of sheet 1 is ='Sheet2'!I34

Here while dragging the formula, the next cell has to take the value of the
same row (34) of every third column C, F, I, L... so on. But while dragging
its not coming the same way.

Can anyone suggest me how to do that?

Thanks in advance
Sasikiran

Try this idea copied to the right
=INDIRECT(CHAR(COLUMN(A1)*3+64)& ROW())

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Sasikiran" <(E-Mail Removed)> wrote in message
news:9F6FC520-F0E0-4388-AA9A-(E-Mail Removed)...
> Hello,
>
> I have a problem while dragging the formula to the next cells.
>
> The formula in A1 of sheet 1 is ='Sheet2'!C34
> The formula in B1 of sheet 1 is ='Sheet2'!F34
> The formula in C1 of sheet 1 is ='Sheet2'!I34
>
> Here while dragging the formula, the next cell has to take the value of
> the
> same row (34) of every third column C, F, I, L... so on. But while
> dragging
> its not coming the same way.
>
> Can anyone suggest me how to do that?
>
> Thanks in advance
> Sasikiran
>

hi,
if i understand you....
you might try using absolute references instead of relative references.
example...
instead of ....sheet2!C34......
Use..............Sheet2!$C$34.....

Read up on absolute vs.relative references in xl help.

Regards
FSt1

"Sasikiran" wrote:

> Hello,
>
> I have a problem while dragging the formula to the next cells.
>
> The formula in A1 of sheet 1 is ='Sheet2'!C34
> The formula in B1 of sheet 1 is ='Sheet2'!F34
> The formula in C1 of sheet 1 is ='Sheet2'!I34
>
> Here while dragging the formula, the next cell has to take the value of the
> same row (34) of every third column C, F, I, L... so on. But while dragging
> its not coming the same way.
>
> Can anyone suggest me how to do that?
>
> Thanks in advance
> Sasikiran
>

Or for row 34
=INDIRECT(CHAR(COLUMN(A1)*2+64)&34)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:eyibwX3$(E-Mail Removed)...
> Try this idea copied to the right
> =INDIRECT(CHAR(COLUMN(A1)*3+64)& ROW())
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Sasikiran" <(E-Mail Removed)> wrote in message
> news:9F6FC520-F0E0-4388-AA9A-(E-Mail Removed)...
>> Hello,
>>
>> I have a problem while dragging the formula to the next cells.
>>
>> The formula in A1 of sheet 1 is ='Sheet2'!C34
>> The formula in B1 of sheet 1 is ='Sheet2'!F34
>> The formula in C1 of sheet 1 is ='Sheet2'!I34
>>
>> Here while dragging the formula, the next cell has to take the value of
>> the
>> same row (34) of every third column C, F, I, L... so on. But while
>> dragging
>> its not coming the same way.
>>
>> Can anyone suggest me how to do that?
>>
>> Thanks in advance
>> Sasikiran
>>
>

Hi,

a small correction:

=INDIRECT(CHAR(COLUMN()*3+64)&34)

enter this formula in the cell A1 and then copy drag it to B, C, D, .....

Thanks,
--
Farhad Hodjat


"Don Guillett" wrote:

> Or for row 34
> =INDIRECT(CHAR(COLUMN(A1)*2+64)&34)
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:eyibwX3$(E-Mail Removed)...
> > Try this idea copied to the right
> > =INDIRECT(CHAR(COLUMN(A1)*3+64)& ROW())
> >
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "Sasikiran" <(E-Mail Removed)> wrote in message
> > news:9F6FC520-F0E0-4388-AA9A-(E-Mail Removed)...
> >> Hello,
> >>
> >> I have a problem while dragging the formula to the next cells.
> >>
> >> The formula in A1 of sheet 1 is ='Sheet2'!C34
> >> The formula in B1 of sheet 1 is ='Sheet2'!F34
> >> The formula in C1 of sheet 1 is ='Sheet2'!I34
> >>
> >> Here while dragging the formula, the next cell has to take the value of
> >> the
> >> same row (34) of every third column C, F, I, L... so on. But while
> >> dragging
> >> its not coming the same way.
> >>
> >> Can anyone suggest me how to do that?
> >>
> >> Thanks in advance
> >> Sasikiran
> >>
> >
>
>

Hi Farhad,

Can you please help me more on this?

The data has to be copied thru a formula from a different sheet (sheet2) to
sheet 1.

The formula in A1 of sheet 1 is ='Sheet2'!C34
The formula in B1 of sheet 1 is ='Sheet2'!F34
The formula in C1 of sheet 1 is ='Sheet2'!I34

How do I incorporate in this formula

=INDIRECT(CHAR(COLUMN()*3+64)&34)

Im trying in this way..

=INDIRECT(CHAR('Sheet2'!C34*3+64)&34)

Please trying all the possible ways... please tell me where i'm going wrong

Thanks in advance
Sasikiran




"Farhad" wrote:

> Hi,
>
> a small correction:
>
> =INDIRECT(CHAR(COLUMN()*3+64)&34)
>
> enter this formula in the cell A1 and then copy drag it to B, C, D, .....
>

> > >> Hello,
> > >>
> > >> I have a problem while dragging the formula to the next cells.
> > >>
> > >> The formula in A1 of sheet 1 is ='Sheet2'!C34
> > >> The formula in B1 of sheet 1 is ='Sheet2'!F34
> > >> The formula in C1 of sheet 1 is ='Sheet2'!I34
> > >>
> > >> Here while dragging the formula, the next cell has to take the value of
> > >> the
> > >> same row (34) of every third column C, F, I, L... so on. But while
> > >> dragging
> > >> its not coming the same way.
> > >>
> > >> Can anyone suggest me how to do that?
> > >>
> > >> Thanks in advance
> > >> Sasikiran
> > >>
> > >
> >
> >

Hi,

The formula that i correct for you works just to column Z and if you have
more column so you have to expand the formula as below:

=INDIRECT(IF(64+COLUMN()*3>90,IF(64+COLUMN()*3-26>90,"'Sheet2'!B"&CHAR(64+COLUMN()*3-52),"'Sheet2'!A"&CHAR(64+COLUMN()*3-26)),"'Sheet2'!"&CHAR(64+COLUMN()*3))&34)

so this formula works till column BZ

Thanks,
--
Farhad Hodjat


"Sasikiran" wrote:

> Hi Farhad,
>
> Can you please help me more on this?
>
> The data has to be copied thru a formula from a different sheet (sheet2) to
> sheet 1.
>
> The formula in A1 of sheet 1 is ='Sheet2'!C34
> The formula in B1 of sheet 1 is ='Sheet2'!F34
> The formula in C1 of sheet 1 is ='Sheet2'!I34
>
> How do I incorporate in this formula
>
> =INDIRECT(CHAR(COLUMN()*3+64)&34)
>
> Im trying in this way..
>
> =INDIRECT(CHAR('Sheet2'!C34*3+64)&34)
>
> Please trying all the possible ways... please tell me where i'm going wrong
>
> Thanks in advance
> Sasikiran
>
>
>
>
> "Farhad" wrote:
>
> > Hi,
> >
> > a small correction:
> >
> > =INDIRECT(CHAR(COLUMN()*3+64)&34)
> >
> > enter this formula in the cell A1 and then copy drag it to B, C, D, .....
> >
>
> > > >> Hello,
> > > >>
> > > >> I have a problem while dragging the formula to the next cells.
> > > >>
> > > >> The formula in A1 of sheet 1 is ='Sheet2'!C34
> > > >> The formula in B1 of sheet 1 is ='Sheet2'!F34
> > > >> The formula in C1 of sheet 1 is ='Sheet2'!I34
> > > >>
> > > >> Here while dragging the formula, the next cell has to take the value of
> > > >> the
> > > >> same row (34) of every third column C, F, I, L... so on. But while
> > > >> dragging
> > > >> its not coming the same way.
> > > >>
> > > >> Can anyone suggest me how to do that?
> > > >>
> > > >> Thanks in advance
> > > >> Sasikiran
> > > >>
> > > >
> > >
> > >

Thanks a ton Farhad... its working fine now..

"Farhad" wrote:

> Hi,
>
> The formula that i correct for you works just to column Z and if you have
> more column so you have to expand the formula as below:
>
> =INDIRECT(IF(64+COLUMN()*3>90,IF(64+COLUMN()*3-26>90,"'Sheet2'!B"&CHAR(64+COLUMN()*3-52),"'Sheet2'!A"&CHAR(64+COLUMN()*3-26)),"'Sheet2'!"&CHAR(64+COLUMN()*3))&34)
>
> so this formula works till column BZ
>
> Thanks,
> --
> Farhad Hodjat
>
>
> "Sasikiran" wrote:
>
> > Hi Farhad,
> >
> > Can you please help me more on this?
> >
> > The data has to be copied thru a formula from a different sheet (sheet2) to
> > sheet 1.
> >
> > The formula in A1 of sheet 1 is ='Sheet2'!C34
> > The formula in B1 of sheet 1 is ='Sheet2'!F34
> > The formula in C1 of sheet 1 is ='Sheet2'!I34
> >
> > How do I incorporate in this formula
> >
> > =INDIRECT(CHAR(COLUMN()*3+64)&34)
> >
> > Im trying in this way..
> >
> > =INDIRECT(CHAR('Sheet2'!C34*3+64)&34)
> >
> > Please trying all the possible ways... please tell me where i'm going wrong
> >
> > Thanks in advance
> > Sasikiran
> >
> >
> >
> >
> > "Farhad" wrote:
> >
> > > Hi,
> > >
> > > a small correction:
> > >
> > > =INDIRECT(CHAR(COLUMN()*3+64)&34)
> > >
> > > enter this formula in the cell A1 and then copy drag it to B, C, D, .....
> > >
> >
> > > > >> Hello,
> > > > >>
> > > > >> I have a problem while dragging the formula to the next cells.
> > > > >>
> > > > >> The formula in A1 of sheet 1 is ='Sheet2'!C34
> > > > >> The formula in B1 of sheet 1 is ='Sheet2'!F34
> > > > >> The formula in C1 of sheet 1 is ='Sheet2'!I34
> > > > >>
> > > > >> Here while dragging the formula, the next cell has to take the value of
> > > > >> the
> > > > >> same row (34) of every third column C, F, I, L... so on. But while
> > > > >> dragging
> > > > >> its not coming the same way.
> > > > >>
> > > > >> Can anyone suggest me how to do that?
> > > > >>
> > > > >> Thanks in advance
> > > > >> Sasikiran
> > > > >>
> > > > >
> > > >
> > > >

Welcome! but the important point was 64+COLUMN()*3 which Don Guillett solved
it. Thank you Don for your solution!

Thanks,
--
Farhad Hodjat


"Sasikiran" wrote:

> Thanks a ton Farhad... its working fine now..
>
> "Farhad" wrote:
>
> > Hi,
> >
> > The formula that i correct for you works just to column Z and if you have
> > more column so you have to expand the formula as below:
> >
> > =INDIRECT(IF(64+COLUMN()*3>90,IF(64+COLUMN()*3-26>90,"'Sheet2'!B"&CHAR(64+COLUMN()*3-52),"'Sheet2'!A"&CHAR(64+COLUMN()*3-26)),"'Sheet2'!"&CHAR(64+COLUMN()*3))&34)
> >
> > so this formula works till column BZ
> >
> > Thanks,
> > --
> > Farhad Hodjat
> >
> >
> > "Sasikiran" wrote:
> >
> > > Hi Farhad,
> > >
> > > Can you please help me more on this?
> > >
> > > The data has to be copied thru a formula from a different sheet (sheet2) to
> > > sheet 1.
> > >
> > > The formula in A1 of sheet 1 is ='Sheet2'!C34
> > > The formula in B1 of sheet 1 is ='Sheet2'!F34
> > > The formula in C1 of sheet 1 is ='Sheet2'!I34
> > >
> > > How do I incorporate in this formula
> > >
> > > =INDIRECT(CHAR(COLUMN()*3+64)&34)
> > >
> > > Im trying in this way..
> > >
> > > =INDIRECT(CHAR('Sheet2'!C34*3+64)&34)
> > >
> > > Please trying all the possible ways... please tell me where i'm going wrong
> > >
> > > Thanks in advance
> > > Sasikiran
> > >
> > >
> > >
> > >
> > > "Farhad" wrote:
> > >
> > > > Hi,
> > > >
> > > > a small correction:
> > > >
> > > > =INDIRECT(CHAR(COLUMN()*3+64)&34)
> > > >
> > > > enter this formula in the cell A1 and then copy drag it to B, C, D, .....
> > > >
> > >
> > > > > >> Hello,
> > > > > >>
> > > > > >> I have a problem while dragging the formula to the next cells.
> > > > > >>
> > > > > >> The formula in A1 of sheet 1 is ='Sheet2'!C34
> > > > > >> The formula in B1 of sheet 1 is ='Sheet2'!F34
> > > > > >> The formula in C1 of sheet 1 is ='Sheet2'!I34
> > > > > >>
> > > > > >> Here while dragging the formula, the next cell has to take the value of
> > > > > >> the
> > > > > >> same row (34) of every third column C, F, I, L... so on. But while
> > > > > >> dragging
> > > > > >> its not coming the same way.
> > > > > >>
> > > > > >> Can anyone suggest me how to do that?
> > > > > >>
> > > > > >> Thanks in advance
> > > > > >> Sasikiran
> > > > > >>
> > > > > >
> > > > >
> > > > >