A great "bad data" problem today. A massive spreadsheet from the corporate office has six years of forecast data for dozens of projects. Every time the forecast changes, you have to extract certain records and update a chart. Although I pondered a macro and some tricky (but not quite working) use of Find All, the final solution is something that I had never tried before: Filter, Copy, then Paste Links to create a chartable but live subset of the data.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1941.
Filter, then Paste Links.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Here's a real problem that I encountered yesterday during training.
This was Jody from Ohio.
Gets this big spreadsheet from corporate, much larger than this, with years and years of worth of data going across.
And you see, for each Project there's all of these different Types on the left hand side.
This data changes frequently and Jody needs to create a chart showing all of the man hours from here.
And so, right now, Jody is kind of having to go through and select the first data, and then hold down the Ctrl key, and select the next data.
But you can imagine, as you're doing 48 of these, you know, somewhere along the line you accidentally miss-click and then you have to start all over again.
So, instantly, the first thing I thought of, is, hey, you know, this would be a great little macro that I could knock out here during the training.
And that would copy just the Manhours to the other sheet.
And then you could change this macro, you know, to get the Budget or the Actuals.
And quickly make copies of the data, but then, you know, I heard Jody say that the data is frequently changing.
You know, they'll make some changes and hey, go up to eat those charts right away.
And I realized, that just doing a static copy from the macro might not be the best way to go.
I briefly thought about, let's select the whole thing, Ctrl+F, I'll look for Manhours, Find All, and then the Find All, press Ctrl+A, which selects all of those Manhours.
Hoping that then somehow I could either Shift+Spacebar or Ctrl+Shift+Right Arrow, but unfortunately all of those only work from the active cell.
They don't take all of these items and select them.
And Shift+Spacebar selects the entire row, but only from the active cell, not from the selection.
Then I said, all right, well, there's little chance that this is going to work, but what the heck, let me try it.
Let's turn on the Filter and, by the way, Jodie had already tried turning on the filter, which created a great chart for just Manhours.
But then she also had to create a chart for Budget and Actuals, and as, you know, every time that you changed the filter, the chart was changing.
I said, you know, let's take this data from here.
I'm going to do Ctrl+Shift+Down Arrow, Ctrl+Shift+Right Arrow and Ctrl+C to copy.
And then go back to our worksheet, where we're going to build the chart.
And just for the heck of it, see if HOME, Paste, Paste Links, if that would manage to create a data set pointing back into the original data set.
And to my surprise it worked perfectly.
So there's the first Project on C2 and then the next project on C14, and then the next project on C26.
Then as this data back here, we can undo the Filter.
And as this data back here changes, so I'll change that for 1336 to 20000, just to make sure it's really large.
You see, that those changes will be reflected, all right.
And then, I mean, it's an ugly looking chart in my opinion to have all of these Manhours, but that's the chart that her, you know, the higher-ups need, so we're not going to argue that point.
This is a great way to create a live data set, that's pointing into that big data set, this ugly data set, that came from corporate, you know.
Jody has to live with this, she didn't create this, it's just what was forced upon her.
But, Copy, Filter, Copy and then Paste Links saved the day.
I want to thank Jody for that question and I want to thank you for stopping by.
See you next time for another necast from MrExcel.
Learn Excel from MrExcel podcast, episode 1941.
Filter, then Paste Links.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Here's a real problem that I encountered yesterday during training.
This was Jody from Ohio.
Gets this big spreadsheet from corporate, much larger than this, with years and years of worth of data going across.
And you see, for each Project there's all of these different Types on the left hand side.
This data changes frequently and Jody needs to create a chart showing all of the man hours from here.
And so, right now, Jody is kind of having to go through and select the first data, and then hold down the Ctrl key, and select the next data.
But you can imagine, as you're doing 48 of these, you know, somewhere along the line you accidentally miss-click and then you have to start all over again.
So, instantly, the first thing I thought of, is, hey, you know, this would be a great little macro that I could knock out here during the training.
And that would copy just the Manhours to the other sheet.
And then you could change this macro, you know, to get the Budget or the Actuals.
And quickly make copies of the data, but then, you know, I heard Jody say that the data is frequently changing.
You know, they'll make some changes and hey, go up to eat those charts right away.
And I realized, that just doing a static copy from the macro might not be the best way to go.
I briefly thought about, let's select the whole thing, Ctrl+F, I'll look for Manhours, Find All, and then the Find All, press Ctrl+A, which selects all of those Manhours.
Hoping that then somehow I could either Shift+Spacebar or Ctrl+Shift+Right Arrow, but unfortunately all of those only work from the active cell.
They don't take all of these items and select them.
And Shift+Spacebar selects the entire row, but only from the active cell, not from the selection.
Then I said, all right, well, there's little chance that this is going to work, but what the heck, let me try it.
Let's turn on the Filter and, by the way, Jodie had already tried turning on the filter, which created a great chart for just Manhours.
But then she also had to create a chart for Budget and Actuals, and as, you know, every time that you changed the filter, the chart was changing.
I said, you know, let's take this data from here.
I'm going to do Ctrl+Shift+Down Arrow, Ctrl+Shift+Right Arrow and Ctrl+C to copy.
And then go back to our worksheet, where we're going to build the chart.
And just for the heck of it, see if HOME, Paste, Paste Links, if that would manage to create a data set pointing back into the original data set.
And to my surprise it worked perfectly.
So there's the first Project on C2 and then the next project on C14, and then the next project on C26.
Then as this data back here, we can undo the Filter.
And as this data back here changes, so I'll change that for 1336 to 20000, just to make sure it's really large.
You see, that those changes will be reflected, all right.
And then, I mean, it's an ugly looking chart in my opinion to have all of these Manhours, but that's the chart that her, you know, the higher-ups need, so we're not going to argue that point.
This is a great way to create a live data set, that's pointing into that big data set, this ugly data set, that came from corporate, you know.
Jody has to live with this, she didn't create this, it's just what was forced upon her.
But, Copy, Filter, Copy and then Paste Links saved the day.
I want to thank Jody for that question and I want to thank you for stopping by.
See you next time for another necast from MrExcel.
