Fill Query

H

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
 
J

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
 
H

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
 
D

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
 
D

Dave Peterson

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

Did you put the formula in B1?
 
Top