James T asks if the Ctrl+G Dialog Box provides a way to jump 3000 columns to the right. (After all, Column E + 3000 columns is what? AAA? AAB? How would you know?) R1C1 style referencing is one ugly option, but Bill has something easier.
Transcript of the video:
MrExcel podcast is sponsored by Easy- XL. Learn excel from MrExcel podcast. Episode # 1863 Ctrl+G to Go To 3000 columns to the right Hey you know last Friday we had that cool tip from Mike Gervin in the dueling excel podcast.
To hold on shift while pressing enter in the Go-to dialog box that generate us a more email. Bob Umlas said hold down Ctrl while pressing Enter and then this question from James came in can you from the Go-to dialog box say hey, I want to go N columns over to the right.
You know we don't know is that AAA or XYZ or there's any XYZ.
You know it's just hard to calculate so let's run through these.
I'll press either F5 or Ctrl G.
Currently in E 10 if I would press H 10 Shift + Enter.
There's Mike's trick it selects from the current cell out to that other cell.
Bob's trick Ctrl G or F5. We are currently in E 14 if I would press H 14 and Ctrl+Enter it selects both cells so I could enter the same thing in both items.
But how are you gonna get it to go 3,000 columns to the right.
Well the first thing I thought is what if we switch over to R1C1 notation where we could use current row and then column in square brackets at 3000 that says wherever we are, go 3000 columns over.
That doesn't work and the reason it doesn't work is because Well we're not in R1C1 style, but if we a lot of R1C1 notation for example, RC3005 is a valid A1 style address, it's in row 3005 column RC.
So it would be ambiguous if they allow that. I'm certainly not going to suggest.
File Options Formulas R1C1 reference style We are now we're currently in row 5 and we could do Ctrl G and do RC3005.
Which takes us 3005 columns over or even Ctrl+G and RC[ -3 ] There's just too many downsides to being in R1C1 style to make that be viable.
So turn that off.
So is there a solution Ctrl G and check this out if we use =OFFSET from the current cell which is E 10 and we want to go 0 rows down and then let's say 50545 columns to the right.
I don't know what that's going to be press Enter and sure enough it takes us right out there to column H.
Yeah, I'm not sure that that is actually a good way to go.
It's up to you.
It's interesting though that OFFSET will work there in the Go-to dialog box.
So James not sure if that is a good solution to your question or not, I'm putting it out there for interest.
If you have a better way to do this, please in the YouTube comments, let us let us know.
Hey i want to thank James for sending that question in and I wanna thank you for stopping by.
Will see you next time for another netcast from MrExcel.
To hold on shift while pressing enter in the Go-to dialog box that generate us a more email. Bob Umlas said hold down Ctrl while pressing Enter and then this question from James came in can you from the Go-to dialog box say hey, I want to go N columns over to the right.
You know we don't know is that AAA or XYZ or there's any XYZ.
You know it's just hard to calculate so let's run through these.
I'll press either F5 or Ctrl G.
Currently in E 10 if I would press H 10 Shift + Enter.
There's Mike's trick it selects from the current cell out to that other cell.
Bob's trick Ctrl G or F5. We are currently in E 14 if I would press H 14 and Ctrl+Enter it selects both cells so I could enter the same thing in both items.
But how are you gonna get it to go 3,000 columns to the right.
Well the first thing I thought is what if we switch over to R1C1 notation where we could use current row and then column in square brackets at 3000 that says wherever we are, go 3000 columns over.
That doesn't work and the reason it doesn't work is because Well we're not in R1C1 style, but if we a lot of R1C1 notation for example, RC3005 is a valid A1 style address, it's in row 3005 column RC.
So it would be ambiguous if they allow that. I'm certainly not going to suggest.
File Options Formulas R1C1 reference style We are now we're currently in row 5 and we could do Ctrl G and do RC3005.
Which takes us 3005 columns over or even Ctrl+G and RC[ -3 ] There's just too many downsides to being in R1C1 style to make that be viable.
So turn that off.
So is there a solution Ctrl G and check this out if we use =OFFSET from the current cell which is E 10 and we want to go 0 rows down and then let's say 50545 columns to the right.
I don't know what that's going to be press Enter and sure enough it takes us right out there to column H.
Yeah, I'm not sure that that is actually a good way to go.
It's up to you.
It's interesting though that OFFSET will work there in the Go-to dialog box.
So James not sure if that is a good solution to your question or not, I'm putting it out there for interest.
If you have a better way to do this, please in the YouTube comments, let us let us know.
Hey i want to thank James for sending that question in and I wanna thank you for stopping by.
Will see you next time for another netcast from MrExcel.
