Kambiz sends in a question. He has a Summary worksheet that points to Jan, Feb, Mar worksheets in his workbook. When he copies the worksheet to a new workbook, the formulas keep pointing to the Jan worksheet on the original workbook. In Episode 681, we take a look at using the INDIRECT function to solve this problem.
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 Mr. Excel netcast I'm Bill Jelen.
Boy a great question sent in today by Canvas, with what I think is probably a fairly difficult answer for a Monday.
But he says hey, I have this workbook.
It's created by someone else. It has worksheets January February March for example, and I go through and I build a new summary worksheet and that summary worksheet points to the back sheets.
Good enough.
He gets a new workbook from that someone else, and he wants to take this summary worksheet and move it to the new workbook.
okay, so let's go take a look at the new workbook for the NewYear and let's just change some data here, so that way it's real obvious make this all 999.
Okay now go back to the original workbook.
He knows how to take this worksheet and copy it to the new workbook.
We want to right click on the Summary tab "Move or Copy" "Create a copy" Move it to the NewYear.
Move it before sheet January and you would think that instead of 369 we have a much larger number because we know that January has 999's here.
But when we look at the formula bar when we copied this worksheet, it still is pointing back to the last year worksheet Very very annoying. He said well, how can I how can I do this?
How can I create a summary worksheet that's always going to point to January February March of the current workbook, and it's going to drive you a little bit insane.
I said I'm not sure that this is worth all the effort but it may be.
There's a great function called INDIRECT. The INDIRECT function and basically indirect says.
Hey, instead of specifying exactly what address I want to look up. I want you to build the cell reference.
I want to build a cell reference.
So usually we would say January! and then for example cell B7, but instead of just simply putting that in as the formula, I'm going to use the INDIRECT function, the INDIRECT function, so I use January and the !
Ofcourse if January had a space in it, I have to put apostrophes in and then I use the CELL function. The CELL function says.
Hey, give me the address of the current cell.
I happen to be in cell B7 here So I want the cell of B7. If I needed to add in other sheets for example, if I needed to add in February, I would use indirect again.
("Feb"!&CELL"(address",B7) and so on. I could keep doing this all the way out for March and all the other sheets.
Now you'll see that here, I have an answer of 246.
When we copied this formula to the new workbook.
"Move or Copy" "Create a copy" Go to the NewYear workbook.
Click OK.
This beautiful formula actually goes through and grabs the current values from January February March.
Solves the problem now.
I realize it would take a long time to go through and rewrite all of your formulas that look like this in order to look like this.
But if you find that you're constantly taking this summary worksheet, maybe every day, every week and adding it on to a workbook that someone has sent you, probably well worth the time.
Hey thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Boy a great question sent in today by Canvas, with what I think is probably a fairly difficult answer for a Monday.
But he says hey, I have this workbook.
It's created by someone else. It has worksheets January February March for example, and I go through and I build a new summary worksheet and that summary worksheet points to the back sheets.
Good enough.
He gets a new workbook from that someone else, and he wants to take this summary worksheet and move it to the new workbook.
okay, so let's go take a look at the new workbook for the NewYear and let's just change some data here, so that way it's real obvious make this all 999.
Okay now go back to the original workbook.
He knows how to take this worksheet and copy it to the new workbook.
We want to right click on the Summary tab "Move or Copy" "Create a copy" Move it to the NewYear.
Move it before sheet January and you would think that instead of 369 we have a much larger number because we know that January has 999's here.
But when we look at the formula bar when we copied this worksheet, it still is pointing back to the last year worksheet Very very annoying. He said well, how can I how can I do this?
How can I create a summary worksheet that's always going to point to January February March of the current workbook, and it's going to drive you a little bit insane.
I said I'm not sure that this is worth all the effort but it may be.
There's a great function called INDIRECT. The INDIRECT function and basically indirect says.
Hey, instead of specifying exactly what address I want to look up. I want you to build the cell reference.
I want to build a cell reference.
So usually we would say January! and then for example cell B7, but instead of just simply putting that in as the formula, I'm going to use the INDIRECT function, the INDIRECT function, so I use January and the !
Ofcourse if January had a space in it, I have to put apostrophes in and then I use the CELL function. The CELL function says.
Hey, give me the address of the current cell.
I happen to be in cell B7 here So I want the cell of B7. If I needed to add in other sheets for example, if I needed to add in February, I would use indirect again.
("Feb"!&CELL"(address",B7) and so on. I could keep doing this all the way out for March and all the other sheets.
Now you'll see that here, I have an answer of 246.
When we copied this formula to the new workbook.
"Move or Copy" "Create a copy" Go to the NewYear workbook.
Click OK.
This beautiful formula actually goes through and grabs the current values from January February March.
Solves the problem now.
I realize it would take a long time to go through and rewrite all of your formulas that look like this in order to look like this.
But if you find that you're constantly taking this summary worksheet, maybe every day, every week and adding it on to a workbook that someone has sent you, probably well worth the time.
Hey thanks for stopping by. We'll see you next time for another netcast from MrExcel.
