In Excel, your function arguments contain a mix of off-sheet and on-sheet references. Once you touch another sheet in Point mode, Excel starts using the sheet name for on-sheet references. Mike and Bill discuss three ways to prevent this.
Transcript of the video:
Hey, welcome back. It's time for another dueling Excel podcast.
This is our episode 147, Prevent the current sheet name in formulas.
Hey, look Mike and I have recorded this live out at Highline.
We never actually discussed what the problem is though.
So, here's the problem, Amara, she called MrExcel, I need to build a formula that is pointing to some references out on the table sheet.
And, so in the SUMSIFS, the sum range is from Table.
Criteria range one is from table.
But then when I click on East, they're using the sheet name.
Even though this is the current sheet.
So, we're trying to figure out a way to enter this formula.
Preventing the sheet name from appearing.
The same thing is going to happen here when I go grab the products.
Comma and then come back to MrExcel.
So, as soon I click on A4, which they should just put A4 here and see they've already said is on MrExcel.
A4... So, we're trying to come up with a formula.
Way of entering that formula to prevent the Mr. Excel sheet name there, and they're all right.
Well, it's Friday must be time for another dueling Excel podcast.
I'm bill Jelen for MrExcel.
I'm here with Excel Is Fun.
Mike Girvin form Excel is Fun.
We're live at Highline Community College This is our episode: 147.
How to Avoid sheet reference syntax from the current sheet. So, here's our situation I'm on the sheet and we want to come up with a total form them.
We have to look back at another sheet, and this is one of these things where I want to be building.
Several different arguments are gonna be pointing at this sheet, and if I just start to do it the normal way, equal SUMIFS.
It says it wants the sum range.
So, I head off here to the table and and specify my sum range.
[ CTRL+SHIFT+down-arrow ] comma.
And now it wants Criteria range one.
but I don't even remember what the original data was.
I need to go back.
And, so I'm just going to escape out of this and instead I'm going to do a view.
New Window, and then View, Arrange all vertical.
Now in the left side, I can see the original data.
On the right side, I can see the Table alright Alright, so now I can see everything that I need, on here on the screen.
So, we start out with equal SUMIFS.
And when it asked me for the sum range, I just click over here [ CTRL+SHIFT+down-arrow ] comma Then it needs to know the Criteria range, So, we're going to look for a product is next.
So, [ CTRL+SHIFT+down-arrow ] No missed..
[ CTRL+SHIFT+down-arrow ] I want to make sure that I start in Row 2.
Because this criteria range has to match that range.
comma is equal to this product here.
First click selects that window.
Second click selects where thi...
Nike: That is cool.
Bill: and then criteria range 2, that's going to be a region.
So, we click over here from B2[ CTRL+SHIFT+down-arrow ] comma and then come back to east.
All right, so a couple of advantages here.
And manage number one is I get to see both sets of data that I'm dealing with.
Product first and then region Probably wouldn't matter but you know at least I know which columns to highlight, and then benefit number two is that we are not getting this A4 in this B4, does not not have the sheet reference name before.
So, Good way to go there with the view, new window and then arrange all.
Mike.
Well, yeah, very nice.
I used that trick often when I'm creating formulas between lots of sheets.
All right, I'm going to close this one right here.
So, Zach Parise taught me this trick.
Are you kidding me SUMIFS [ CTRL+A ] to open the arguments or...
oops!
or SUMIFS [ SHIFT+F3 ] and the sum range, now check this out.
I can just zip back and forth and I'm going to [ CTRL+ SHIFT+down-arrow ] and now when I go back to the sheet, Boom!
tab, Now I'm back on this sheet.
Oh, I need to go back over there. That's okay I'm in the Criteria 1.
I'm going to go over to the table.
[ CTRL+HOME ] and in the region, [ CTRL+SHIFT+down-arrow ] Tab Criteria and watch this.
I think I did... yeah B, that's region.
No sheet reference.
I love it the she references are up here, but not there.
Using this dialog box, totally helps with that problem, All right, criteria range 2, is going to be the products.
I'll go back over here [ CTRL+HOME ] [ CTRL+SHIFT+down-arrow ] Tab and now I simply click on the criteria.
[ CTRL+ENTER ]...
Bill: That is a slick trick. All right, wow!
Mike: You know here's a crazy way, I usually do this to avoid them.
I just go comma, comma get the criteria one, which is going to be this.
Comma, comma and then criteria to its Region.
Close parentheses and then I go over and it's hard to remember which...
All right, So there you go.
Bill: All right that was good at.
I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel.
And Excel Is Fun.
This is our episode 147, Prevent the current sheet name in formulas.
Hey, look Mike and I have recorded this live out at Highline.
We never actually discussed what the problem is though.
So, here's the problem, Amara, she called MrExcel, I need to build a formula that is pointing to some references out on the table sheet.
And, so in the SUMSIFS, the sum range is from Table.
Criteria range one is from table.
But then when I click on East, they're using the sheet name.
Even though this is the current sheet.
So, we're trying to figure out a way to enter this formula.
Preventing the sheet name from appearing.
The same thing is going to happen here when I go grab the products.
Comma and then come back to MrExcel.
So, as soon I click on A4, which they should just put A4 here and see they've already said is on MrExcel.
A4... So, we're trying to come up with a formula.
Way of entering that formula to prevent the Mr. Excel sheet name there, and they're all right.
Well, it's Friday must be time for another dueling Excel podcast.
I'm bill Jelen for MrExcel.
I'm here with Excel Is Fun.
Mike Girvin form Excel is Fun.
We're live at Highline Community College This is our episode: 147.
How to Avoid sheet reference syntax from the current sheet. So, here's our situation I'm on the sheet and we want to come up with a total form them.
We have to look back at another sheet, and this is one of these things where I want to be building.
Several different arguments are gonna be pointing at this sheet, and if I just start to do it the normal way, equal SUMIFS.
It says it wants the sum range.
So, I head off here to the table and and specify my sum range.
[ CTRL+SHIFT+down-arrow ] comma.
And now it wants Criteria range one.
but I don't even remember what the original data was.
I need to go back.
And, so I'm just going to escape out of this and instead I'm going to do a view.
New Window, and then View, Arrange all vertical.
Now in the left side, I can see the original data.
On the right side, I can see the Table alright Alright, so now I can see everything that I need, on here on the screen.
So, we start out with equal SUMIFS.
And when it asked me for the sum range, I just click over here [ CTRL+SHIFT+down-arrow ] comma Then it needs to know the Criteria range, So, we're going to look for a product is next.
So, [ CTRL+SHIFT+down-arrow ] No missed..
[ CTRL+SHIFT+down-arrow ] I want to make sure that I start in Row 2.
Because this criteria range has to match that range.
comma is equal to this product here.
First click selects that window.
Second click selects where thi...
Nike: That is cool.
Bill: and then criteria range 2, that's going to be a region.
So, we click over here from B2[ CTRL+SHIFT+down-arrow ] comma and then come back to east.
All right, so a couple of advantages here.
And manage number one is I get to see both sets of data that I'm dealing with.
Product first and then region Probably wouldn't matter but you know at least I know which columns to highlight, and then benefit number two is that we are not getting this A4 in this B4, does not not have the sheet reference name before.
So, Good way to go there with the view, new window and then arrange all.
Mike.
Well, yeah, very nice.
I used that trick often when I'm creating formulas between lots of sheets.
All right, I'm going to close this one right here.
So, Zach Parise taught me this trick.
Are you kidding me SUMIFS [ CTRL+A ] to open the arguments or...
oops!
or SUMIFS [ SHIFT+F3 ] and the sum range, now check this out.
I can just zip back and forth and I'm going to [ CTRL+ SHIFT+down-arrow ] and now when I go back to the sheet, Boom!
tab, Now I'm back on this sheet.
Oh, I need to go back over there. That's okay I'm in the Criteria 1.
I'm going to go over to the table.
[ CTRL+HOME ] and in the region, [ CTRL+SHIFT+down-arrow ] Tab Criteria and watch this.
I think I did... yeah B, that's region.
No sheet reference.
I love it the she references are up here, but not there.
Using this dialog box, totally helps with that problem, All right, criteria range 2, is going to be the products.
I'll go back over here [ CTRL+HOME ] [ CTRL+SHIFT+down-arrow ] Tab and now I simply click on the criteria.
[ CTRL+ENTER ]...
Bill: That is a slick trick. All right, wow!
Mike: You know here's a crazy way, I usually do this to avoid them.
I just go comma, comma get the criteria one, which is going to be this.
Comma, comma and then criteria to its Region.
Close parentheses and then I go over and it's hard to remember which...
All right, So there you go.
Bill: All right that was good at.
I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel.
And Excel Is Fun.
