Tim asked a question about writing a formula that he could copy across a row which would automatically grab successive values from down a column. (Basically doing a Transpose that is always updating). In Episode 835, I will show you my formula method and also discuss Kathy's easier suggestion that will work in some cases.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, alright, welcome back to MrExcel netcast, I'm Bill Jelen.
It's Friday; good day to remind you to go to facebook.com, and then search here for MrExcel.com, sign up as a fan of our Facebook page.
On Saturday we'll be giving away a Podcast DVD-- 600 Episodes on one single DVD-- so you want to be entered for that.
I just got back from Atlanta-- the Southeastern Accounting show.
Boy, I had a couple of great seminars there with a few hundred people each.
In the one seminar, before the seminar starts, I walk around to see if anyone has any Excel questions, and Tim said, "You know, hey, I know how to transpose data-- I know how to take this data, Copy, and then come up here and do Edit, Paste Special, and Transpose to turn the data on its side.
But I want to do something completely different-- I want to put a formula in here that basically is going to grab January and be able to drag that across." Well, you know, that's not going to work.
So my typical solution to this is to use a couple of functions together.
The first thing is the INDEX function.
The Index function says, "Hey, look, we're going to be returning values from this range." And I press the F4 key to make sure that range is locked, and then basically, here I need to say I want the first value; and then for February, I want the second value; and then the third value; and the fourth value.
If you've been watching the podcast for a while, you know that I always say that the geekiest way to enter the number 1, is to use the function COLUMN and ask for the column of A1.
COLUMN tells you what column number that cell is in.
So, of course Column A is 1, that's going to return a 1.
Well, why would we go to all that hassle to type that function instead of just using the number 1?
Because that reference changes as we copy across.
So column of A1, when I copied to February, is going to ask for the column of B1, which is 2; and then when I get to December, it's going to ask for the column of L1, which is 12.
We'll try it out.
As I copy this across you'll see that sure enough we do get formulas that point to each successive row in the original data.
And if I would change something here-- I put a thousand-- you'll see that it does successfully change in the new formula.
Now, in that seminar in Atlanta, Kathy raised her hand and said, "You know, sometimes if your original data set is completely based on formulas--" so, for example, here I have a data set (Month, Product Sales) and then we built some formulas here that are using SUMIF functions-- she says, "--it's actually possible to take those formulas and transpose the formulas," which I had never tried-- I never would have expected it to work.
But it's pretty amazing how it works.
So we do Ctrl+C here, and then come up to our new range, Edit, Paste Special, Formulas, Transpose, click OK, and Excel actually rewrites the formulas.
Like here, for example, March-- it still goes back to the Column A, but now it's looking at the value in J instead of looking at E7, which I thought was pretty amazing.
So thanks to Kathy for passing that one along, and thanks to you for stopping by.
Don't forget to go out to Facebook, sign up to be a fan of MrExcel.com, and we'll see you next week for another netcast from MrExcel.
[ music ]
It's Friday; good day to remind you to go to facebook.com, and then search here for MrExcel.com, sign up as a fan of our Facebook page.
On Saturday we'll be giving away a Podcast DVD-- 600 Episodes on one single DVD-- so you want to be entered for that.
I just got back from Atlanta-- the Southeastern Accounting show.
Boy, I had a couple of great seminars there with a few hundred people each.
In the one seminar, before the seminar starts, I walk around to see if anyone has any Excel questions, and Tim said, "You know, hey, I know how to transpose data-- I know how to take this data, Copy, and then come up here and do Edit, Paste Special, and Transpose to turn the data on its side.
But I want to do something completely different-- I want to put a formula in here that basically is going to grab January and be able to drag that across." Well, you know, that's not going to work.
So my typical solution to this is to use a couple of functions together.
The first thing is the INDEX function.
The Index function says, "Hey, look, we're going to be returning values from this range." And I press the F4 key to make sure that range is locked, and then basically, here I need to say I want the first value; and then for February, I want the second value; and then the third value; and the fourth value.
If you've been watching the podcast for a while, you know that I always say that the geekiest way to enter the number 1, is to use the function COLUMN and ask for the column of A1.
COLUMN tells you what column number that cell is in.
So, of course Column A is 1, that's going to return a 1.
Well, why would we go to all that hassle to type that function instead of just using the number 1?
Because that reference changes as we copy across.
So column of A1, when I copied to February, is going to ask for the column of B1, which is 2; and then when I get to December, it's going to ask for the column of L1, which is 12.
We'll try it out.
As I copy this across you'll see that sure enough we do get formulas that point to each successive row in the original data.
And if I would change something here-- I put a thousand-- you'll see that it does successfully change in the new formula.
Now, in that seminar in Atlanta, Kathy raised her hand and said, "You know, sometimes if your original data set is completely based on formulas--" so, for example, here I have a data set (Month, Product Sales) and then we built some formulas here that are using SUMIF functions-- she says, "--it's actually possible to take those formulas and transpose the formulas," which I had never tried-- I never would have expected it to work.
But it's pretty amazing how it works.
So we do Ctrl+C here, and then come up to our new range, Edit, Paste Special, Formulas, Transpose, click OK, and Excel actually rewrites the formulas.
Like here, for example, March-- it still goes back to the Column A, but now it's looking at the value in J instead of looking at E7, which I thought was pretty amazing.
So thanks to Kathy for passing that one along, and thanks to you for stopping by.
Don't forget to go out to Facebook, sign up to be a fan of MrExcel.com, and we'll see you next week for another netcast from MrExcel.
[ music ]
