Formula Help!

P

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
 
S

swatsp0p

prhrmk said:
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
Click to expand...

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?
 
R

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
 
D

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
 
D

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
 
P

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
 
T

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: =
 
P

prhrmk

Hello All,

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

Regards,
Pau
 
Top