Learn Excel from MrExcel: Dana asks if there is a way to link data from one worksheet to another, when the linking data is vertical and it needs to be linked into a horizontal lookup table. Today, in Episode #1334, Bill shows us how this is done.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode, 1334.
Link and Transpose.
Well, hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Dana.
Dana means to link from one work book to another workbook.
Okay! Well, that's straightforward enough, but here's the issue, this is the data that Dana wants to grab and it's going vertically.
The place where she needs to paste it is going horizontally.
So, I'm gonna show you a really really cool powerful array formula, that will solve this very easy to do not as hard as most array form this.
But I need to count how many cells, I want to grab on, I want to grab these six cells.
So, we come back here and I choose six cells.
Weird, how that you have to choose more than one cell but that's another way that array formula is used.
We use the transpose function.
Transpose, so we open parentheses there.
I press [ ctrl Tab ], to jump back to my other open workbook, choose that day.
I see that it does have the dollar signs and then [ control shift enter ].
And sure enough, it takes that data turns it sideways back in the original worksheet, and let's just do a little test here.
Let's change these numbers, will change the four thousand to forty five hundred and eight thousand to seventy five hundred.
And when we go back and look at our formula it is accurately picking up.
So, a really cool way to take things and turn it sideways.
Usually, I use the index function combined with either row or column.
But transpose, if you can get over the kind of the hassle of having to select many cells first and then press [ control shift enter ], at the end of the formula very, very good way to go.
Hey, let's thank Dana for sending that question in.
Thank you for stopping by, see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode, 1334.
Link and Transpose.
Well, hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Dana.
Dana means to link from one work book to another workbook.
Okay! Well, that's straightforward enough, but here's the issue, this is the data that Dana wants to grab and it's going vertically.
The place where she needs to paste it is going horizontally.
So, I'm gonna show you a really really cool powerful array formula, that will solve this very easy to do not as hard as most array form this.
But I need to count how many cells, I want to grab on, I want to grab these six cells.
So, we come back here and I choose six cells.
Weird, how that you have to choose more than one cell but that's another way that array formula is used.
We use the transpose function.
Transpose, so we open parentheses there.
I press [ ctrl Tab ], to jump back to my other open workbook, choose that day.
I see that it does have the dollar signs and then [ control shift enter ].
And sure enough, it takes that data turns it sideways back in the original worksheet, and let's just do a little test here.
Let's change these numbers, will change the four thousand to forty five hundred and eight thousand to seventy five hundred.
And when we go back and look at our formula it is accurately picking up.
So, a really cool way to take things and turn it sideways.
Usually, I use the index function combined with either row or column.
But transpose, if you can get over the kind of the hassle of having to select many cells first and then press [ control shift enter ], at the end of the formula very, very good way to go.
Hey, let's thank Dana for sending that question in.
Thank you for stopping by, see you next time for another netcast from MrExcel.
