szakharov7723
Board Regular
- Joined
- Jun 22, 2018
- Messages
- 85
- Office Version
- 2019
- Platform
- Windows
So I have a reference formulas created by Concatenate formula. which is spread on 3500+ cells
Here is concatenate formula
Which creates formula like this in a cell:
As you can see it is referenced to specific file. Thing is this file may not exist yet, but may be created in future.
While I can manually make this formula work by:
1) copying concatenate formula and pasting as value (which creates reference formula)
2) replacing "=" with itself (it will also work if I just click inside the cell and hit enter)
3) If one or both files do not exist I need to hit cancel in Update values.
4) even if file doesn't exist yet, once it gets created #REF error changes to referred value.
5) the only serious disadvantage is stored values if referred file gets deleted (,but it is another discussion)
As I said, the formula works, if done manually
BUT
When I try to use replace all, it only replacing the first cell after I go through cancel steps
Then it just ignores the fact I chose multiple cells.
Even when I create a macro to replace all cells it just doesn't go further than first cell , even if this cell is already a working formula.
I tried all codes I could find on replace all and update value cancel topics, and none is working in my case
I am really confused.
Can you please help to find a right code to make text reference formulas work ?
Here is concatenate formula
Code:
CONCATENATE("=","IFERROR","(",CONCATENATE("'",$B$2," ",TEXT($B33,"yyyy"),"\",TEXT($B33,"mmm"),"\","[",TEXT($B33,"ddd, mmm dd"),".xlsb]",CONCATENATE($B$3,C$2))&"+"&CONCATENATE("'",$B$2," ",TEXT($B33,"yyyy"),"\",TEXT($B33,"mmm"),"\","[",TEXT($B33,"ddd, mmm dd"),"_2",".xlsb]",CONCATENATE($B$3,C$2)),",","'",$B$2," ",TEXT($B33,"yyyy"),"\",TEXT($B33,"mmm"),"\","[",TEXT($B33,"ddd, mmm dd"),".xlsb]",CONCATENATE($B$3,C$2))
Which creates formula like this in a cell:
Code:
IFERROR('K:\Maint\PLANING\DSC CALENDARS 2019\Apr\[Tue, Apr 02.xlsb]Millright data'!B7+'K:\Maint\PLANING\DSC CALENDARS 2019\Apr\[Tue, Apr 02_2.xlsb]Millright data'!B7,'K:\Maint\PLANING\DSC CALENDARS 2019\Apr\[Tue, Apr 02.xlsb]Millright data'!B7
As you can see it is referenced to specific file. Thing is this file may not exist yet, but may be created in future.
While I can manually make this formula work by:
1) copying concatenate formula and pasting as value (which creates reference formula)
2) replacing "=" with itself (it will also work if I just click inside the cell and hit enter)
3) If one or both files do not exist I need to hit cancel in Update values.
4) even if file doesn't exist yet, once it gets created #REF error changes to referred value.
5) the only serious disadvantage is stored values if referred file gets deleted (,but it is another discussion)
As I said, the formula works, if done manually
BUT
When I try to use replace all, it only replacing the first cell after I go through cancel steps
![](/board/proxy.php?image=https%3A%2F%2Fcdn1.imggmi.com%2Fuploads%2F2019%2F4%2F15%2Fd17f400a84dafb93e431835dfa0816ad-full.png&hash=8508e9d6831bc19599daa0f77a440b19)
Then it just ignores the fact I chose multiple cells.
Even when I create a macro to replace all cells it just doesn't go further than first cell , even if this cell is already a working formula.
I tried all codes I could find on replace all and update value cancel topics, and none is working in my case
![Confused :confused: :confused:](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f615.png)
Can you please help to find a right code to make text reference formulas work ?