Hi to everyone,

I have a formula which is a reference to another cell in anothe
worksheet.

EXAMPLE:

in A1 I have +'other_worksheet'!A1

When I copy the formula from A1 in A2 the formula changes to:

+'other_worksheet'!A2

The problem is that i would like it to change to B1. I would like t
copythe formula in one direction while the reference changes in anothe
direction.

Is there an easy way to do this ?

Thanks for your help

Paolo

Ital

--
kayar
-----------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...fo&userid=2471
View this thread: http://www.excelforum.com/showthread.php?threadid=38795

Let's say your data is in cells A1:H1
You want to use them in a formula on A1:A8 on another sheet.

One way is

=INDEX(Sheet1!A$1:H$1,1,ROW())

You may have to fiddle with the INDEX function a bit if the locations are
different.

There may be another more elegant way, but this is one option.


"kayard" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Hi to everyone,
>
> I have a formula which is a reference to another cell in another
> worksheet.
>
> EXAMPLE:
>
> in A1 I have +'other_worksheet'!A1
>
> When I copy the formula from A1 in A2 the formula changes to:
>
> +'other_worksheet'!A2
>
> The problem is that i would like it to change to B1. I would like to
> copythe formula in one direction while the reference changes in another
> direction.
>
> Is there an easy way to do this ?
>
> Thanks for your help
>
> Paolo
>
> Italy
>
>
> --
> kayard
> ------------------------------------------------------------------------
> kayard's Profile:
> http://www.excelforum.com/member.php...o&userid=24716
> View this thread: http://www.excelforum.com/showthread...hreadid=387957
>

Hi Paolo,

Try this:

=OFFSET('other_worksheet'!$A$1,,ROW()-ROW($A$1))

Regards,
KL


"kayard" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Hi to everyone,
>
> I have a formula which is a reference to another cell in another
> worksheet.
>
> EXAMPLE:
>
> in A1 I have +'other_worksheet'!A1
>
> When I copy the formula from A1 in A2 the formula changes to:
>
> +'other_worksheet'!A2
>
> The problem is that i would like it to change to B1. I would like to
> copythe formula in one direction while the reference changes in another
> direction.
>
> Is there an easy way to do this ?
>
> Thanks for your help
>
> Paolo
>
> Italy
>
>
> --
> kayard
> ------------------------------------------------------------------------
> kayard's Profile:
> http://www.excelforum.com/member.php...o&userid=24716
> View this thread: http://www.excelforum.com/showthread...hreadid=387957
>

Another way

=INDIRECT("'other worksheet'!"&(CHAR(ROW()+64))&"1")

--
HTH

Bob Phillips

"kayard" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Hi to everyone,
>
> I have a formula which is a reference to another cell in another
> worksheet.
>
> EXAMPLE:
>
> in A1 I have +'other_worksheet'!A1
>
> When I copy the formula from A1 in A2 the formula changes to:
>
> +'other_worksheet'!A2
>
> The problem is that i would like it to change to B1. I would like to
> copythe formula in one direction while the reference changes in another
> direction.
>
> Is there an easy way to do this ?
>
> Thanks for your help
>
> Paolo
>
> Italy
>
>
> --
> kayard
> ------------------------------------------------------------------------
> kayard's Profile:
http://www.excelforum.com/member.php...o&userid=24716
> View this thread: http://www.excelforum.com/showthread...hreadid=387957
>

I have big problem understanding the formulas


--
agarcia
------------------------------------------------------------------------
agarcia's Profile: http://www.excelforum.com/member.php...o&userid=25320
View this thread: http://www.excelforum.com/showthread...hreadid=387957

Which one, and which bit do you not understand?

--
HTH

Bob Phillips

"agarcia" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> I have big problem understanding the formulas
>
>
> --
> agarcia
> ------------------------------------------------------------------------
> agarcia's Profile:
http://www.excelforum.com/member.php...o&userid=25320
> View this thread: http://www.excelforum.com/showthread...hreadid=387957
>