Forum Discussion
Copy one excel sheet with formula to another workbook (Excel sheet)
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.
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 🙂
- Thanks a lot.
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.
