Fill Query

[Hywel]

I have a fill right problem, where I want excel to give me the result o
the cell below.

In a Horizontal table, with years along the columns, I have th
following in B1
'Sheet 2'!A1 + 'Sheet 2'!A100 + 'Sheet 2'!A200

Is there a way to fill right, so I get the result in B2
'Sheet 2'!A2 + 'Sheet 2'!A101 + 'Sheet 2'!A201

instead of B1,B100 and B200

Any help appreciated.
Hywel
Manchester U

 

[Jim May]

Save your work before trying, but
maybe:
in B1 alter content to:
'Sheet 2'!$A1 + 'Sheet 2'!$A100 + 'Sheet 2'!$A200
And try Right Fill again.
HTH

 

[Hywel]

I've tried that already Jim,
It keeps both the column and row reference the same - effectively th
same as using double dollar symbols.
Hywe

 

[Dave Peterson]

You say your formula is in B1 and want to drag right to B2. I'm guessing that
B2 was a typo and you meant C1.

If that's the case, maybe you could use a formula like this in B1:

=INDIRECT("'Sheet 2'!A"&COLUMN()-2+1)
+INDIRECT("'Sheet 2'!A"&100+COLUMN()-2)
+INDIRECT("'Sheet 2'!A"&200+COLUMN()-2)

(all one cell)

and drag right

 

[Hywel]

Thanks Dave, you spotted my error. However, I am getting a #REF in the
cell.

 

[Dave Peterson]

Do you really have a sheet named 'Sheet 2' (with the space)?

Did you put the formula in B1?

 

[Hywel]

Ah. It was the space in the 'Sheet 2' that was missing,
Thanks for the help,