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

In a Horizontal table, with years along the columns, I have the
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 UK


--
Hywel
------------------------------------------------------------------------
Hywel's Profile: http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426

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" wrote in
message ...

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

In a Horizontal table, with years along the columns, I have the
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 UK


--
Hywel
------------------------------------------------------------------------
Hywel's Profile:
http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426

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


--
Hywel
------------------------------------------------------------------------
Hywel's Profile: http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426

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 wrote:

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

In a Horizontal table, with years along the columns, I have the
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 UK

--
Hywel
------------------------------------------------------------------------
Hywel's Profile: http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426

--

Dave Peterson

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


--
Hywel
------------------------------------------------------------------------
Hywel's Profile: http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426

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

Did you put the formula in B1?



Hywel wrote:

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

--
Hywel
------------------------------------------------------------------------
Hywel's Profile: http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426

--

Dave Peterson

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


--
Hywel
------------------------------------------------------------------------
Hywel's Profile: http://www.excelforum.com/member.php...o&userid=14824
View this thread: http://www.excelforum.com/showthread...hreadid=483426