Results 1 to 2 of 2

Thread: How to cell reference from certain files based on dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Posts
    53
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default How to cell reference from certain files based on dates

    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:
    =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))
    
    
    This formula is used for 3000+ cells
    Which results in the following formula as an example for one 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
    
    
    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 by szakharov7723; Apr 24th, 2019 at 12:02 PM.

  2. #2
    Board Regular
    Join Date
    Jun 2018
    Posts
    53
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to cell reference from certain files based on dates

    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)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •