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.

The table summarizes the data by date and code. For instance, there are two January entries, one for A and one for B; one February entry for C; two March entries, one for A and one for C; and so on. Each month, this reader enters a CountIf() function that counts the individual codes for just that month. I agree with her, she’s working harder than she needs to. This month’s challenge is to generate the summary table without all the handholding. It doesn’t have to be completely dynamic, but your solution should be easier than entering a unique CountIf() each month. You can use any feature you like—no restrictions. If you need more information, just ask!

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!