Paste link but with a locked ($A$1) address

JimDibb

New Member
Joined
Mar 20, 2007
Messages
2
Is there a way to paste a range of cells as links, but have the links use the locked (I don't know the proper term) address of the target cell). I would have said "absolute" reference but the paste link doc already says it uses absolute references.

For example, if I copy cell A2 from sheet1, and I paste as a link, i get the reference sheet1!A2 in the target cell.

Is there any way to get it to paste sheet1!$A$2? I'd like to be able to recopy and paste the link and have it refer to the original A2 but as it is now, the A2 is adjusted to be relative to where I copy it.

Thanks, first time poster here.
( I searched for paste and link but couldn't wade through the 1700+ results.)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
you might write in a cell

#=sheet1!$A$2
copy it

paste wherever you want on another sheet
then use the the find/replace tool to remove the #

use any character you want besides #, just something that won't exist elsewhere on the sheet...
 
Upvote 0
Jonmo, thanks, that's interesting. What if I had to do it for 400 cells though?

I'm really thinking I want to write a macro though to actually copy the right set of things after they've been changed, rather than having two levels of links.

What I'm really trying to do is have a list of (project name, task) pairs, where there are many projects and each project can have many tasks. This list is in a workbook on a single page. Then there are two other workbooks that need to reference this list.

In each of these (shared) workbooks, multiple people need to get a subset of the project/task pairs onto their own personal sheet.

I guess I'm going to try to work up a form to put on each person's sheet that references the project/task pairs and allows them to pick and insert one into the current selection on their own sheet. Does that make more sense?

The important thing is that each person work from the same set of project/task pairs (rather than typing them in).
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
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