how do i copy vertical formulas to horizonal and keep same cell r.

[Guest]

when i copy formulas in a row, and transpose the copy, it changes the cell
references to other cells which needs to be the same as the source cells

 

[Earl Kiosterud]

Opie,

The relative cell reference adjustment is useful when the referred cell is
in the table you're transposing. Unfortunately, the transpose command
adjusts it when the referred cell is external to the table, where you most
likely want the reference to remain to the same cell. You can get around
this with absolute references:

=......$A$3...... The F4 key is handy for changing to absolute. Do
this while you're editing the cell.

It will not change absolute references to referred cells outside the table,
but will change them for cells internal to the table. Probably the best for
most situations. If the cells are internal (unlikely, I think), probably
the only way is to use:

=.......INDIRECT("A3")......

 

[Dave O]

1. Highlight the vertical region that contains your formulas.
2. Do a search and replace: search for the equal sign, and replace it
with your initials. This converts all your formulas to text.
3. Copy the vertical region, and >Paste special >Transpoze. This
converts the vertical region to horizontal.
4. Search and replace again, but this time search for your initials
and replace with the equal sign.

When you converted the formulas to text the formulas became static. By
replacing your initials with the = sign in step 4 you converted them
back to dynamic formulas.

 

[Dave O]

1. Highlight the vertical region that contains your formulas.
2. Do a search and replace: search for the equal sign, and replace it
with your initials. This converts all your formulas to text.
3. Copy the vertical region, and >Paste special >Transpoze. This
converts the vertical region to horizontal.
4. Search and replace again, but this time search for your initials
and replace with the equal sign.

When you converted the formulas to text the formulas became static. By
replacing your initials with the = sign in step 4 you converted them
back to dynamic formulas.