Carrie from NYC asks how to set up links between worksheets. Episode 1056 shows you how.
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, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze this. Well, let's fire up a pivot table.
Let's see, if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Summer is just flying by, can you believe it's the second week of July already.
Hey today's question is from Carrie and New York.
Carrie is trying to set up links between, one worksheet and another worksheet.
Now, Carrie has data that she fills in back here on the detail sheets and then right now, she's taking that data and rekey it on the summary sheet and there's a better way to go.
Now, this initially seems very confusing, how to set up links because when you look at the formulas.
The formulas seem to have different syntax depending on what's going on.
So, here, We have an equal sign the worksheet name, worksheet name is NoSpace.
A bang, in other words the exclamation point programmers call that a bang, and then the address where we want to go C5.
But, if your worksheet name happen to have a space, then it gets much more confusing because you have the equal sign and apostrophe the worksheet name another apostrophe, the bang and the cell character.
If you're going to an external workbook.
So, in other words we want to link to a worksheet in another workbook.
Oh, well, then it gets even worse left square bracket workbook name, right square bracket the worksheet name, a bang and the cell address.
Unless, there's a space in there then we put that whole thing equal sign apostrophe and so on out here finally the apostrophe, the bang and the cell address.
Now, here's the good news you do not have to learn any of those rules at all.
All you have to be able to do is type an equal sign.
So, we type an equal sign, if I want to go and grab a value from the no space worksheet, click on NoSpace choose the cell that I want and press enter and we're done.
If the worksheet happens to have a space, it's the exact same thing type the equal sign, click on the other worksheet, click on the cell as you want and press enter and Excel worries about building the proper syntax.
Now, if it's an external workbook, definitely easier if you have that workbook open.
So, we type an equal sign here and then we can come down to the task bar, and go to the LinkToMe worksheet choose the cell we want.
Ok or equal sign and one of the workbooks with spaces and choose the cell that we want, click Ok.
Now, one thing you want to notice is that when I'm going to another sheet in this book, they don't put the dollar signs in.
So, F8, if I would copy this formula, would be free to copy.
If I would get right over it, would change the G8, H8 and so on.
However, if we're going to an external workbook, by default, they put dollar signs in that cell reference.
You might want to press F4 one, two, three times to clear that out especially, if you're going to be copying the formula across or down.
So, there you have it.
An easy way to build cell links, you don't have to worry about the horrible syntax.
You know, whether you put the apostrophe before the left square bracket or right.
Don't need to know that, type the equal sign go click on the cell you want and Excel will build your links.
Well, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by.
See you next time for another from Mr.Excel.
you
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze this. Well, let's fire up a pivot table.
Let's see, if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Summer is just flying by, can you believe it's the second week of July already.
Hey today's question is from Carrie and New York.
Carrie is trying to set up links between, one worksheet and another worksheet.
Now, Carrie has data that she fills in back here on the detail sheets and then right now, she's taking that data and rekey it on the summary sheet and there's a better way to go.
Now, this initially seems very confusing, how to set up links because when you look at the formulas.
The formulas seem to have different syntax depending on what's going on.
So, here, We have an equal sign the worksheet name, worksheet name is NoSpace.
A bang, in other words the exclamation point programmers call that a bang, and then the address where we want to go C5.
But, if your worksheet name happen to have a space, then it gets much more confusing because you have the equal sign and apostrophe the worksheet name another apostrophe, the bang and the cell character.
If you're going to an external workbook.
So, in other words we want to link to a worksheet in another workbook.
Oh, well, then it gets even worse left square bracket workbook name, right square bracket the worksheet name, a bang and the cell address.
Unless, there's a space in there then we put that whole thing equal sign apostrophe and so on out here finally the apostrophe, the bang and the cell address.
Now, here's the good news you do not have to learn any of those rules at all.
All you have to be able to do is type an equal sign.
So, we type an equal sign, if I want to go and grab a value from the no space worksheet, click on NoSpace choose the cell that I want and press enter and we're done.
If the worksheet happens to have a space, it's the exact same thing type the equal sign, click on the other worksheet, click on the cell as you want and press enter and Excel worries about building the proper syntax.
Now, if it's an external workbook, definitely easier if you have that workbook open.
So, we type an equal sign here and then we can come down to the task bar, and go to the LinkToMe worksheet choose the cell we want.
Ok or equal sign and one of the workbooks with spaces and choose the cell that we want, click Ok.
Now, one thing you want to notice is that when I'm going to another sheet in this book, they don't put the dollar signs in.
So, F8, if I would copy this formula, would be free to copy.
If I would get right over it, would change the G8, H8 and so on.
However, if we're going to an external workbook, by default, they put dollar signs in that cell reference.
You might want to press F4 one, two, three times to clear that out especially, if you're going to be copying the formula across or down.
So, there you have it.
An easy way to build cell links, you don't have to worry about the horrible syntax.
You know, whether you put the apostrophe before the left square bracket or right.
Don't need to know that, type the equal sign go click on the cell you want and Excel will build your links.
Well, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by.
See you next time for another from Mr.Excel.
you
