How to cell reference from certain files based on dates

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
I have daily files that "feed" information to summary file. Right now it is a manual process. (copy-paste)
There are certain cell ranges in daily file that feed information to specific sheet and specific date in summary file.
I want to find a way to make it automatic.
I almost did it with the formula, but excel doesn't let me implement it.
I have created the formula in summary file that identifies which cell to reference in daily file based on date and value in cell:
Code:
[COLOR=#333333]=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))

[/COLOR]
This formula is used for 3000+ cells
Which results in the following formula as an example for one cell
Code:
[COLOR=#333333]=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

[/COLOR]
To make this formula work I need to replace "=" with itself. The issue is if daily file doesn't exist yet. It shows the following error and stops replacement process for other cells, even if I make it a macro:

https://ibb.co/TvTfDs7
Is there a way to avoid this popup ? Or any other way to automate this process ?
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
For some reason I cannot see uploaded picture. So just in case describing it here: It is Update Values popup window. It wants me to select the file (I press cancel obviously)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top