Richard sends in todays question: How do you copy a formula horizontally when its reading from a vertical list? Episode 518 shows the easy, but unintuitive solution.
This video is the podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of 277 tips from the book!
This video is the podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, we have a question that was sending by Richard. I actually met Richard.
He came to one of my live seminars last week and then mailed this question in the next day.
He says how do you copy a formula horizontally when it's reading from a vertical list?
Thought that was a great question, it's kind of tricky the way that we're gonna do that.
We have a vertical list in column A from A3 to A12 and I've now entered a formula that grabs item 1, but when I copy that across ofcourse it's not gonna work because it will point to the wrong cells.
The approach to this is kind of amazing, and I'm going to take you kind of down a strange path here.
I want to talk about the COLUMN function. The COLUMN function.
We'll use =COLUMN and I always want to point to something in column A. Let's just say A1=COLUMN(A1) is a very fancy way of writing the number 1.
=COLUMN(A1) says what column is column A. Its the first column.
Now the beautiful thing about this though is when I copy it across, column of A1 changes to column of B1 which is a fancy way of saying 2.
Then I get 3 4 5 6 So the column is going to be the solution here. We're going to use =INDEX The index says we have either a vertical or rectangular range.
I'm going to hit the F4 key to make sure that that is an absolute reference and then which item do I want which row number do I want to go to.
Well I want to go to column A1.
In other words the first item in the list because this only has one column I don't have to put a , 1 at the end.
Normally you'd have to put a comma and say which column you're trying to go from but in this case it's a it's just a nice simple list.
So we'll choose that and you'll see that we get item 1 but now as we copy to the right, column of A1 changes to column of B1 and that says give me Item 2 Item 3 Item 4 Item 5 Item six right across.
Completely unintuitive to have to use two functions that you probably have never used.
The INDEX function and the COLUMN function.
But a very simple way to solve this rather hard problem.
Thanks to Richard for coming out to the seminar and for sending this question in.
Will see you next time for another net cast from MrExcel.
Today, we have a question that was sending by Richard. I actually met Richard.
He came to one of my live seminars last week and then mailed this question in the next day.
He says how do you copy a formula horizontally when it's reading from a vertical list?
Thought that was a great question, it's kind of tricky the way that we're gonna do that.
We have a vertical list in column A from A3 to A12 and I've now entered a formula that grabs item 1, but when I copy that across ofcourse it's not gonna work because it will point to the wrong cells.
The approach to this is kind of amazing, and I'm going to take you kind of down a strange path here.
I want to talk about the COLUMN function. The COLUMN function.
We'll use =COLUMN and I always want to point to something in column A. Let's just say A1=COLUMN(A1) is a very fancy way of writing the number 1.
=COLUMN(A1) says what column is column A. Its the first column.
Now the beautiful thing about this though is when I copy it across, column of A1 changes to column of B1 which is a fancy way of saying 2.
Then I get 3 4 5 6 So the column is going to be the solution here. We're going to use =INDEX The index says we have either a vertical or rectangular range.
I'm going to hit the F4 key to make sure that that is an absolute reference and then which item do I want which row number do I want to go to.
Well I want to go to column A1.
In other words the first item in the list because this only has one column I don't have to put a , 1 at the end.
Normally you'd have to put a comma and say which column you're trying to go from but in this case it's a it's just a nice simple list.
So we'll choose that and you'll see that we get item 1 but now as we copy to the right, column of A1 changes to column of B1 and that says give me Item 2 Item 3 Item 4 Item 5 Item six right across.
Completely unintuitive to have to use two functions that you probably have never used.
The INDEX function and the COLUMN function.
But a very simple way to solve this rather hard problem.
Thanks to Richard for coming out to the seminar and for sending this question in.
Will see you next time for another net cast from MrExcel.
