"Copying" and "pasting" a reference?

alexcr

Board Regular
Joined
Oct 15, 2018
Messages
59
Office Version
  1. 365
Platform
  1. Windows
I've always wondered why it isn't possible to "copy" and "paste" a reference in Excel. Let me explain...

Let's say that I want the cell WorksheetA!A1 to reference cell WorksheetZ!B2, i.e. "=WorksheetZ!B2" would be the formula entered in cell WorksheetA!A1.

Oftentimes, this involves figuring out which cell I want to reference, i.e. WorksheetZ!B2, then navigating back to WorksheetA, then entering "=" in cell WorksheetA!A1, then navigating back to WorksheetZ to select cell WorksheetZ!B2.

It would be much easier to be able to "copy" a reference to cell WorksheetZ!B2 while I'm on WorksheetZ, after which I would navigate to WorksheetA, where I would be able to "paste" this reference in cell WorksheetA!A1, thus automagically creating the formula above. Steps avoided, time saved.

Does what I'm describing make sense? Is there a way to do this that I'm simply not aware of after all these years using Excel?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Oftentimes, this involves figuring out which cell I want to reference, i.e. WorksheetZ!B2, then navigating back to WorksheetA, then entering "=" in cell WorksheetA!A1, then navigating back to WorksheetZ to select cell WorksheetZ!B2.
Why not :
- Enter "=" in SheetA A1
- Figure out what cell you want to reference and select it.
- Press Enter
 
Upvote 0
Why not :
- Enter "=" in SheetA A1
- Figure out what cell you want to reference and select it.
- Press Enter

That assumes that you already know you want to reference something.

In practice, however, it's oftentimes the case that you're reviewing a worksheet and realize that you want to link to something on that worksheet from another worksheet.

As described in my original post, when this happens, it would be much easier (fewer steps) if you could just "copy" and "paste" the reference, instead of having to go to the other worksheet and then back to the one you're on.

Is there no way to do this?
 
Upvote 0
Try pastespecial - link, keyboard shortcut to paste is Ctrl+Alt+v then L
 
Upvote 0
Or do right click and choose "Paste link (N)" icon:

PasteLink2.png
 
Upvote 0
Goto sheet Z
in a spare cell of sheet Z, put the formula =B1
Cut that cell
Navigate to sheet 1, select A1 and paste

If you know you are going to be referencing SheetZ!B2 often, name the cell
 
Upvote 0
That assumes that you already know you want to reference something.

In practice, however, it's oftentimes the case that you're reviewing a worksheet and realize that you want to link to something on that worksheet from another worksheet.

As described in my original post, when this happens, it would be much easier (fewer steps) if you could just "copy" and "paste" the reference, instead of having to go to the other worksheet and then back to the one you're on.

Is there no way to do this?
I think you are making a meal out of this.
The steps I suggested in post #2 don't involve any more steps than copy/pastelink.
Also, depends whether you want an absolute ref or a relative ref (like in your original post).
PasteLink gives an absolute ref.
 
Upvote 0
That assumes that you already know you want to reference something.

In practice, however, it's oftentimes the case that you're reviewing a worksheet and realize that you want to link to something on that worksheet from another worksheet.

As described in my original post, when this happens, it would be much easier (fewer steps) if you could just "copy" and "paste" the reference, instead of having to go to the other worksheet and then back to the one you're on.

Is there no way to do this?
I find that when I'm designing formulas, ultimately destined for other worksheets, that editing and verifying the formula on the sheet that has the precedent cells is easiest.
Once a formula has been verified as suitable, Cut and Paste will move it to the distant worksheet where it is destined and the referencing will automatically be adjusted.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top