This week, improve the example Excel spreadsheet and learn the solution to last week's transposing puzzle.
A reader recently sent me a worksheet that she's sure could be more efficient. Her solution works, but it does require a lot of setup. I agree, she's working harder than she needs to. For a bit of background:
- Column A contains dates.
- Column B is a descriptive text column. It could contain any kind of data really—it's a reference value and doesn't figure into the actual solution.
- Column C uses the Month() function to return the year value, referencing the date values in column A.
- Column D is another text column that contains a one-character code that also serves as the column heading in the table to the right.
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…
Can you create a link with transposed data? Hic (Howard) was the first to respond with the answer I expected. Use the Transpose() function. Find a spot in the sheet with plenty of room and enter the following function:
=Transpose(sourcerange)Enter the function as an array by pressing [Ctrl]+[Alt]+[Enter]. As mentioned, this creates a link between the source and the transposed data. If you update the source data, Excel updates the respective value in the transposed range. I apologize for the confusion. Some of you read "… create a link between the source and transposed range, so that updating values in one range updates the respective values in the other?" as meaning a two-way link. I can see why you thought that and I'll try to be more careful in the future. While writing, it was perfectly clear to me that "one range" referred to source and so on. But, I see now that I was unclear. I was worse than unclear, I did not say what I meant, but instead of taking me to task, you generously tried to comply! You guys always surprise and impress me! To Ppn and Vineet369, thank you for trying to meet my unrealistic condition. I could not, however, get your methods to work. Perhaps we can continue this discussion because I don't know of any way to create a two-way link that wouldn't be more trouble than it could possibly be worth!