Hardware

Next Office challenge: Can you create a link with transposed data?

In this week's Office challenge, I share my favorite shortcut for entering accented characters. You can test your Excel skills with an Excel transposing challenge.

In the recent blog, Quickly transpose Excel 2010 data using Paste, I showed you how to use the Paste command to transpose data. For better or worse, this method doesn't create a link between the source and transposed data. How would you transpose data and create a link between the source and transposed range, so that updating values in one range updated the respective values in the other?

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!
Last week we asked you to… List one keyboard shortcut for entering an accented character. Using the Insert menu and tab is certainly easy, but there's more than one way. You might find another way more efficient. For instance, to insert é, you can press [Ctrl] + ' + e before you can find t é in the Symbol dialog box! In actual keystrokes, that's

  1. [Ctrl]
  2. ' (the apostrophe character)
  3. e

No menus. No tabs. In a nutshell, you type a key combo and then type the character you want to accent. The following table should help you memorize these keystroke shortcuts. You don't have to worry about accenting a character incorrectly—if the accent isn't valid, Word will ignore the shortcut.

Keyboard Shortcut

Example

[Ctrl]+' é
[Ctrl]+` (apostrophe with tilde) À
[Ctrl]+[Shift]+~ ñ
[Ctrl]+[Shift]+^ î
[Ctrl]+[Shift]+: ë

Your responses to last week's challenge were helpful as always. Spitfire_Sysop was the first to mention the ASCII table and using the [Alt] key with the numeric keypad. That method's been around for a long time and supports many special symbols. SirWizard was the first to mention my favorite technique, the one I just described. Thanks for all the great responses—it was another great challenge!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

11 comments
Lifebringr
Lifebringr

That way does not work for me, but this does: Alt Gr + e = ?? Alt Gr + a = ?? etc

dhays
dhays

Sam@ Use the equation editor it is available there.

sam
sam

Hi there, thanks for the reminder about Ctrl & the diacritical mark. Also the reminder about the ASCII tables was good... but reminded me of an ASCII diacritical I have tried to find and failed; the not-equals-to sign - the wiggly equals symbol. Does anyone know the ASCII keys for that?

d3d4E4
d3d4E4

This stupid thing does not accept accented letters!

d3d4E4
d3d4E4

This is limited to very few accents, so it is hardly a correct answer. How do you enter ??, ?? etc?

Vineet369
Vineet369

Assume we have data in Column A. Follow these steps: 1. We would insert one column to the right of A. 2. Now in column B, we would insert a formula with a dollar sign to the column B Eg: "=$B4" 3. Drag this formula such that it's beside every value of Col A. 4. Now, copy the data of Col B. 5. Right click, where would you desire to paste, and select "Paste Special" 6. Now, select the "Formulas" Radio Button, and "Transpose" check box. Done. You can now delete Col B. After this, any change to the values in Col A, would reflect the values where you had pasted the data. :) Explanation(If you still want it): Due to the decision to keep Col B fixed by Mix Referencing, we have made sure that when the formulas are pasted after transposing, the Col name is not auto adjusted!

ppg
ppg

To clarify both methods described above are one way links i.e. in my example if you update the values in column A the change will show up in row 1. However if you update the value in row 1 the link will be broken. To get a two way link you would have to set up a macro which monitors change events on the row and column and if it finds a change in one updates the value in the other. If anyone is interested I can give a basic macro that does it.

ppg
ppg

There is another way of doing it which is probably more work but avoids use of the Transpose array function. Assuming the original data is in A2:A10. In cell B2 enter the formula -A2 and convert it to mixed reference (=$A2). Copy the formula in B2 to B2:B10. Copy the range B2:B10 and Paste Special -> Transpose it to Cell B1:J1 (as Susan described last week). You can then clear range B2:B10 if you wish.

hic
hic

1) Select the target cells, where you want the transposed array to go 2) Type =TRANSPOSE( 3) Select the source cells 4) Type the ), then enter the array formula using Ctrl-Shift-Enter --Howard

RU7
RU7

That IS how the question was asked, "...updating values in _one_ range updated the respective values in _the other_...". I agree that this is the only way to answer the whole question as it was asked.

RU7
RU7

With the transpose array function entered in an array of cells, a single cell in the array cannot be changed. So those cells cannot be updated, eliminating the need for the back link.