Copy one excel sheet with formula to another workbook (Excel sheet)

[NKSNagendra7871]

Hi Guys,

I have a excel file where 3 sheets have data and 4th sheet (Formula Functional sheet)  have various formula, 4th sheet take references from initial 3 sheets.

Now I have moved these 4 sheets into another new excel file but in Formula Function sheet still formula reference are of source file. Please let me know how to change the source of formula and link it with new one.

Regards

[#2]

@NKSNagendra7871 It sounds like you have references to other files and not only between sheets in the same file..? Otherwise the references between sheets ought to have followed without issues.
‪However, you do have references to unwanted places, eg =[Book3]Sheet2!$A$1 where Book3 is a file you do not want referenced.

You may press Ctrl+H and replace [Book3] with nothing, or another file.

Click a few Replace before using Replace All in case the new formula does not work out well.
Also, you may select only the cells you want to change since‪ the replace action applies to selected cells. If you only want to change one cell, you may select that one and another, empty.
And save a copy first :):)

Find what: [Book3]
Replace with:
Options >>
Look in: Formulas
[  ] Match entire cell contents

Depending on your exact needs, you may just
File: Info: Edit Links to Files
and Change Source to the new file.

[#3]

@bosinander 

Thanks for your solution, there are two problems;

Hi, Thanks

1. Moved sheet have many  Index Match formulas (more then 20), these formula are taking reference from old sheet.

2. Do you think that Replace function will work in excel formula as well specially we are indexing some data from other sheet. Will this change for all the example of formula is like 

=INDEX('D:\Nagendra_This Com\Nagendra_Main\AB_PREMIUM\Maize\[Maize_FERTILIZER MGMT_21Sep.xlsx]Fertilizer Dose Master'!F3:F326,MATCH('Fertilizer Management-Impro (2)'!$D$1&'Fertilizer Management-Impro (2)'!$D$2&','D:\Nagendra_This Com\Nagendra_Main\AB_PREMIUM\Maize\[Maize_FERTILIZER MGMT_21Sep.xlsx]Fertilizer Dose Master'!$A$3:$A$326,0))

I have above many Index match formula, highlighted is showing reference of old sheet from where these sheets are sourced.

Please let me know if any other option is available.

Regards and Thanks. 

[#4]

@NKSNagendra7871  Hi.

1
to change the reference to the origin sheet, ie one not having suffix (2);
Find what: 'Fertilizer Management-Impro (2)'!
Replace with: 'Fertilizer Management-Impro'!
This should work just fine.

2
Seems you may have new data in another file..?

Find what:
D:\Nagendra_This Com\Nagendra_Main\AB_PREMIUM\Maize\[Maize_FERTILIZER MGMT_21Sep.xlsx]

Replace with:
D:\Nagendra_This Com\Nagendra_Main\AB_PREMIUM\Maize\[Maize_FERTILIZER MGMT_21Oct.xlsx]

It is possible to minimize the strings and shortly replace 21Sep.xlsx with 21Oct.xlsx.

The choice of longer or even shorter strings depends on the risk for changing wrong parts in (other) formulas.

This part however should be even easier to apply by changing source file, using

File: Info: Edit Links to Files.

Also, if you have not looked at the rather new function XLOOKUP yet, it may be worth the while :)

[#5]

Thanks a lot.

[#6]

@NKSNagendra7871  as long as the tab names are the same in the new file as they are in the original file, go to the Data tab in the new file and select Edit Links in the Queries & Connections section.

When the pop-up box appears, select the option to Change Source... and then select the new file as the source file. You will need to have saved the new file to be able to do this.

[#7]

@JSHEP73 

Thanks, Its really helpful. 

Regards

Nagendra