How do I "paste link", and "transpose" at the same time?

[Zhi Sheng]

I'm using Excel 2003.
In "sheet 1", I have:
1
2
3
I want to paste link of these 3 cells into "sheet 2", and display them like:
1 2 3

I know I can use "transpose" array formula, but it's not "safe" when other
users of the spreadsheet are not familiar with array formula.

 

[Dave Peterson]

Select the range to copy on sheet1.
edit|copy

Select the topleft corner of the range to paste.
Edit|paste special|click the transpose button.

But this won't give you a link back to the original data. It's a copy of that
data.

 

[Héctor Miguel]

hi, Zhi Sheng !

I'm using Excel 2003.
In "sheet 1", I have:
1
2
3
I want to paste link of these 3 cells into "sheet 2", and display them like:
1 2 3

I know I can use "transpose" array formula, but it's not "safe" when other
users of the spreadsheet are not familiar with array formula.

assuming your sheet1 data in range [A1:A3] and you need to "transpose-link" into
your sheet2 in range [A11] (row for source data IS column in destination range)...
you could use indirect (worksheet function) and/or "put" the formula by code

(i.e.) within immediate code window (ctrl + G) from vba editor (alt + F11)
(sheet2 shall be the active sheet while...)
copy/paste/type the following and press enter to perform:

op1:
[a1:c1] = "=indirect(""'sheet1'!a""&column())"

op2:
[a1:c1] = "=indirect(address(column(),1,4,2,""sheet1""))"

(adapt/modify/... as needed)
hth,
hector.

 

[Zhi Sheng]

THanks Hector.
I guess there just isn't any easy way to do that in 2003...
Never thought about using "indirect" before. worth a try, but again it's
not safe for "inexperienced users" as my spreadsheets are shared by many
other people in the company as well...
====================
ask and you shall be answered

hi, Zhi Sheng !

I'm using Excel 2003.
In "sheet 1", I have:
1
2
3
I want to paste link of these 3 cells into "sheet 2", and display them like:
1 2 3

I know I can use "transpose" array formula, but it's not "safe" when other
users of the spreadsheet are not familiar with array formula.

assuming your sheet1 data in range [A1:A3] and you need to "transpose-link" into
your sheet2 in range [A11] (row for source data IS column in destination range)...
you could use indirect (worksheet function) and/or "put" the formula by code

(i.e.) within immediate code window (ctrl + G) from vba editor (alt + F11)
(sheet2 shall be the active sheet while...)
copy/paste/type the following and press enter to perform:

op1:
[a1:c1] = "=indirect(""'sheet1'!a""&column())"

op2:
[a1:c1] = "=indirect(address(column(),1,4,2,""sheet1""))"

(adapt/modify/... as needed)
hth,
hector.