Big problem with references in formulas

[kayard]

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

 

[Barb Reinhardt]

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.

 

[KL]

Hi Paolo,

Try this:

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

Regards,
KL

 

[Bob Phillips]

Another way

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

 

[agarcia]

I have big problem understanding the formulas

 

[Bob Phillips]

Which one, and which bit do you not understand?