Formula Help!

[prhrmk]

I hope someone can help me with this? I need to know if there is a way when copying a formula it will copy horizontal instead of verticle (cell wise). I hope this makes sense?

I have a formula =T('Detail Results'!B20) and when copied it changes to B21 and I would like it to change to C20, D20, E20...etc. Otherwise, I have to manually change the cell and I have over 1000 to do.

Any ideas?

Thanks,
Paul

[swatsp0p]

I hope someone can help me with this? I need to know if there is a way when copying a formula it will copy horizontal instead of verticle (cell wise). I hope this makes sense?

I have a formula =T('Detail Results'!B20) and when copied it changes to B21 and I would like it to change to C20, D20, E20...etc. Otherwise, I have to manually change the cell and I have over 1000 to do.

Any ideas?

Thanks,
Paul

Paul: Your question sort of makes sense. We can assume you want to copy the formula DOWN while having the reference move horizontal (to the right). However, you indicate you have 1,000 to do, yet there are only 256 columns on an Excel sheet.

Use of the OFFSET function can allow you to accomplish your task. OFFSET adjusts rows and columns from a target cell. In your case B20 is the target, 0 is the row offset and the column offset increases by one. There may be easier ways, but this is how I do it:

In a blank column (e.g. B3:B259, inserted if needed) I number it 0-256 (the max amount of columns).

In your first cell (A1), use =OFFSET(Detail Results!$B$20,0,B3) and copy down (up to 256 rows):

when copied, A2 will contain:

A2=OFFSET(Detail Results!$B$20,0,B4) which offsets by one column because B4 contains '1'.
A3=OFFSET(Detail Results!$B$20,0,B5).... etc.

Does this help?

[Roger Govier]

Hi Paul

Try
=T(INDIRECT("'Detail Results'!"&CHAR(COLUMN(A:A)+65)&"20"))

If you want the row reference to change from 20 as well, if you copy the
formula down, then change to

=T(INDIRECT("'Detail Results'!"&CHAR(COLUMN(A:A)+65)&Row(20:20)))

Regards

Roger Govier


prhrmk wrote:
> I hope someone can help me with this? I need to know if there is a way
> when copying a formula it will copy horizontal instead of verticle
> (cell wise). I hope this makes sense?
>
> I have a formula *=T('Detail Results'!B20)* and when copied it changes
> to *B21* and I would like it to change to *C20, D20, E20...etc.*
> Otherwise, I have to manually change the cell and I have over 1000 to
> do.
>
> Any ideas?
>
> Thanks,
> Paul
>
>

[damorrison]

This may be a band aid solution:
if you would have the formula start accros the row,
it would end up like :
=A1,=B1,=C1, ect
next highlight that row and go to Edit-Replace, replace the = to + now
they look like this:
+A1,+B1,+C1, ect
Highlite that row and select copy,
move the curser to the beginning of the range you want the formula,
select paste special and then transpose, this will send the copied
range down the column,
highlite that range again and go to edit replace, replace + with = and
you have your formula going down now
Dave

[damorrison]

This may be a band aid solution:
if you would have the formula start accros the row,
it would end up like :
=A1,=B1,=C1, ect
next highlight that row and go to Edit-Replace, replace the = to + now
they look like this:
+A1,+B1,+C1, ect
Highlite that row and select copy,
move the curser to the beginning of the range you want the formula,
select paste special and then transpose, this will send the copied
range down the column,
highlite that range again and go to edit replace, replace + with = and
you have your formula going down now
Dave

[Peo Sjoblom]

One way

=T(OFFSET('Detail Results'!$B$20,,ROW(1:1)-1)

copied down will pull values from B20, C20 etc
note the dollars signs to make it absolute, else you'll get
C21, D22 and so on

Regards,

Peo Sjoblom



"prhrmk" <[email protected]> wrote in
message news:[email protected]...
>
> I hope someone can help me with this? I need to know if there is a way
> when copying a formula it will copy horizontal instead of verticle
> (cell wise). I hope this makes sense?
>
> I have a formula *=T('Detail Results'!B20)* and when copied it changes
> to *B21* and I would like it to change to *C20, D20, E20...etc.*
> Otherwise, I have to manually change the cell and I have over 1000 to
> do.
>
> Any ideas?
>
> Thanks,
> Paul
>
>
> --
> prhrmk
> ------------------------------------------------------------------------
> prhrmk's Profile:
http://www.excelforum.com/member.php...o&userid=23934
> View this thread: http://www.excelforum.com/showthread...hreadid=476796
>

[damorrison]

how did I do this?

[Tom Ogilvy]

Use some nonsensical character string like ZYZ rather than plus.

select the row
do Edit =>Replace
what: =
with: ZYZ

do your copy then paste special transpose

Now select the pasted cells and do

Edit=>Replace
What: ZYZ
with: =

--
Regards,
Tom Ogilvy

"damorrison" <[email protected]> wrote in message
news:[email protected]...
> how did I do this?
>

[prhrmk]

Hello All,

I appreciate the formulas you provided - however the one that acutally worked best was the one submitted by Peo Sjoblom...

Regards,
Paul