There must be an easy way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following formulas in row 3 of a sheet:
A3: =Sheet1!A3
B3: =Sheet1!B3,
C3: =Sheet1!C3
and so on across to column O

On a seperate sheet, i want to transpose these formulas, so that I will
enter in A4: =Sheet1!A3
A5: =Sheet1!B3
A6: = Sheet1!C3,
and so on down. Any idea how I can achieve this other than having to retype
the formula in every row?
 
Select the entire range to transpose.
Edit|replace
what: = (equal sign_
with: $$$$$
replace all

Now your formulas are plain old text.

Select that range
edit|Copy

Off to the other sheet
Select the top left cell of the range to paste into
edit|paste special|click Transpose

Now change the plain old text back into formulas.

Select the range
edit|Replace
what: $$$$$
with: =
replace all

And remember to do it in the original location, too.
 
You can do it with a macro, OR:in a few extra steps
Select the entire row you want to transpose ( row3 of sheet1). Go to
EDIT>REPLACE
type in the exclamation mark !
Replace with type in : !$
then replace all.
Then do the same with the number 3 Replace what : 3 with $3 - replace all.
Now you have absolute reference of the formulae. Now copy the row ( row 3 )
with the formulas you need, go to the sheet you want to transpose them to and
then: Edit>Paste Special>All>Transpose>ok.
 
I am awfully sorry that I did not reply to either posts!!!!!!!!
Suppose I can thank Microsoft for that. Recently, the Microsoft website no
longer registers me for notifications to threads, and as a result, I never
even saw your answers. Try and talk to Microsoft about this? Forget it, it
is a nightmare, and nobody is interested in listening to you.

In the meantime, I went the macro route, and that solved the problem.
Thanks for your responses in any case. Just goes to show that Microsoft
product users care more for each other than Microsoft cares for their
customers!
 
Kassie

Start using a "real" news reader like OE or Forte Agent or similar.

Stay away from the CDO interface and you will have better control over your
usenet life.


Gord Dibben MS Excel MVP
 
Hi Gord,

I tried OE a while back, but did not like the idea ! I much more enjoy
going directly to the usergroups. This way I can read at my leisure, and
until Microsoft stopped looking after their website, I really enjoyed it.
However, I suppose I'll have to go back to OE, to prevent this kind of
situation!

Thanks for your response!
 
Back
Top