• New Patreon Tier and Early Access Content available. If you would like to support AVForums, we now have a new Patreon Tier which gives you access to selected news, reviews and articles before they are available to the public. Read more.

A probably simple excel question that I need help with

soupdragon

Distinguished Member
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
 

Stuey1

Prominent Member
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
 

davema1kin

Prominent Member
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.
 

soupdragon

Distinguished Member
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?
 

Stuey1

Prominent Member
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
 

soupdragon

Distinguished Member
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 :)
 

The latest video from AVForums

CES 2023 Round Up: New TV Lineups for 2023 from LG, Samsung, Panasonic, Hisense & TCL
Subscribe to our YouTube channel
Support AVForums with Patreon

Top Bottom