You have a formula with 6 absolute references. You want to keep one of those constant, but change the other 5 references to point to a new range. Today's episode shows two methods - one involves dragging a range five times, the other is even more convoluted. However - the big discovery in this episode is that you can use F4 to change all nine references from Absolute to Relative, provided your selection does not begin or end on a cell reference.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1741 - Point all 5 References to a New Range!
Alright, today's question posted on YouTube in reference to episode 1455.
I don't know exactly what formula he has, but he wants to leave $BT$7 constant, but he wants to move all of these other references in the formula to a new 2x5 array elsewhere on the sheet.
And in episode 1455 I talked about going to Edit mode, using F2 or double click, and then drag the references to a new spot.
But it's funny, this particular formula points to the same reference twice, so you have to drag and then drag and then drag and then drag, and it really is quite a drag to do all that.
So is there another way to go?
I have another copy of this worksheet here.
OK, so here's what we can do: You press F2 to put this in Edit mode, then up here in the formula bar, it's strange, it doesn't seem to work all the time.
But if I start here, after the quotation mark, and go all the way back to the beginning of the word INDEX, there's 9 different cell references included in that range.
When I press F4, it's going to affect all 9 of those cell references.
So my goal is to remove all the $, that means I'm going to have to press F4 3 times.
The first time gets rid of the $ before the column letters, and then switches it to the $ before the column letter but not the row, and then one more time I go back to all relative references.
You see I left BT7 out of that, so that way it remains constant.
Alright, so now by taking those $ out, here's what happens: I'm allowed to now copy this, Ctrl+C down to the new place, Ctrl+V, and all of the references change.
So now we're pointing not row 7-11, but to rows 15-19.
Great, right?
Now, once I have that, I'm going to Ctrl+X to cut, and go back to the original spot, Ctrl+V to paste, and we now have that similar formula pointing at the new 2x5 range, instead of the original 2x5 range.
Alright, so kind of a hassle there, although, I think the one thing that I learned, is that you can change a whole bunch of different cell references at once.
Now it's interesting, you know, you can't do like this, it'll just be you F4 F4 F4, somehow your selection has to include more.
You know, I'm not touching any self-reference at the beginning, and not touching any self-reference at the end, and it will work perfectly, so that's a cool trick.
Next time you need to change a lot of $, I originally thought it's going to use Ctrl+H Find and Replace, place the $, you know, but that would lose the $ on the BT7 and have to go back and put them in, so this seems to be a better way to go.
Alright well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1741 - Point all 5 References to a New Range!
Alright, today's question posted on YouTube in reference to episode 1455.
I don't know exactly what formula he has, but he wants to leave $BT$7 constant, but he wants to move all of these other references in the formula to a new 2x5 array elsewhere on the sheet.
And in episode 1455 I talked about going to Edit mode, using F2 or double click, and then drag the references to a new spot.
But it's funny, this particular formula points to the same reference twice, so you have to drag and then drag and then drag and then drag, and it really is quite a drag to do all that.
So is there another way to go?
I have another copy of this worksheet here.
OK, so here's what we can do: You press F2 to put this in Edit mode, then up here in the formula bar, it's strange, it doesn't seem to work all the time.
But if I start here, after the quotation mark, and go all the way back to the beginning of the word INDEX, there's 9 different cell references included in that range.
When I press F4, it's going to affect all 9 of those cell references.
So my goal is to remove all the $, that means I'm going to have to press F4 3 times.
The first time gets rid of the $ before the column letters, and then switches it to the $ before the column letter but not the row, and then one more time I go back to all relative references.
You see I left BT7 out of that, so that way it remains constant.
Alright, so now by taking those $ out, here's what happens: I'm allowed to now copy this, Ctrl+C down to the new place, Ctrl+V, and all of the references change.
So now we're pointing not row 7-11, but to rows 15-19.
Great, right?
Now, once I have that, I'm going to Ctrl+X to cut, and go back to the original spot, Ctrl+V to paste, and we now have that similar formula pointing at the new 2x5 range, instead of the original 2x5 range.
Alright, so kind of a hassle there, although, I think the one thing that I learned, is that you can change a whole bunch of different cell references at once.
Now it's interesting, you know, you can't do like this, it'll just be you F4 F4 F4, somehow your selection has to include more.
You know, I'm not touching any self-reference at the beginning, and not touching any self-reference at the end, and it will work perfectly, so that's a cool trick.
Next time you need to change a lot of $, I originally thought it's going to use Ctrl+H Find and Replace, place the $, you know, but that would lose the $ on the BT7 and have to go back and put them in, so this seems to be a better way to go.
Alright well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
