A probably simple excel question that I need help with

soupdragon

Distinguished Member
Joined
Jun 22, 2008
Messages
8,476
Reaction score
4,012
Points
1,991
Location
Antrim NI
Another excel thread.

My issue.

I've 2 spreadsheets, lets call them work2016 and work2017.

Both sheets are almost identical but I've an extra sheet in 2016 that I don't have in 2017 that I want to copy it in. So when I copy in the 2016 sheet into the 2017 one, it links the formulas into the 2016 file. I want the formulas to stay the same but I don't want them linking to 2016.

For example, the formula might be really simple, like ='payroll'AB14 but when I move the sheet into the 2017 file it now reads =[work2016] 'payroll'AB14

I can manually adjust the formula so that it doesn't read the 2016 file but its a very large page and its going to take me ages to do it.

I just need to know the quick way :)

thanks in advance
 
Haven't got a computer to test but you may be able to use find and replace (CTRL + H) - find the workbook name and replace with nothing
 
you can do a find and replace using ctrl F. copy the bit of the formula that links to the other sheet and put that in the find part, then in the replace part leave this blank.

click on replace all and you are good.
 
Thanks, I'll give it a try shortly and see how I get on.
 
No luck :(

Below you can see the formula. The link [copy of copy of Belfast CCM 27th feb.xlsm] is what I'm trying to remove from the formula

IMG_20170602_104908.jpg


I'm thinking that prior to importing the full sheet, I can import it without bringing the links in?
 
you need to select from [Copy of copy....feb.xlsm]

your find and replace box should look like this
find and replace.JPG
 
Last edited:
No luck :(

Below you can see the formula. The link [copy of copy of Belfast CCM 27th feb.xlsm] is what I'm trying to remove from the formula

View attachment 869203

I'm thinking that prior to importing the full sheet, I can import it without bringing the links in?

When you are using find and replace make sure that it is looking in the formulas

Don't think you can do anything prior to importing as it's only once you move it to the new workbook that the link will add the workbook name because excel will try to preserve the original link

View attachment 869207
 
I got there in the end. I just highlighted the cell, then clicked 'data' and 'edit links' and that let me select the destination of the links. I then used the 2017 file as the link source which removed all the '2016' links. Saved me a few hrs work if I had to manually do it :)
 
Missed this - yes changing the source like that is the way to do it
 

The latest video from AVForums

Is 4K Blu-ray Worth It?
Subscribe to our YouTube channel
Back
Top Bottom