Results 1 to 2 of 2

Thread: Combining a formula with a cell search from a different spreadsheet

  1. #1

    Combining a formula with a cell search from a different spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    This one is a bit tough to explain. I have spreadsheet
    E:\Broadband_repair\BBR SUPS\Weekly Rolling Productivity Report\2014\2014.03.19.xls

    I am trying to pull a number from cell K5 from that spreadsheet without typing in 2014.03.19 as I would like to automate this for future weeks.

    This is what I have so far:
    I am currently in a spreadsheet:
    E:\Broadband_repair\BBR SUPS\Weekly Rolling Productivity Report\2014\2014.03.26.xls

    I have cells that pull down currect spreadsheet's name and put it as a specific date and then subtrack weeks from the date:
    Cell R1:
    =MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))+5-SEARCH("[",CELL("filename",A1)))
    This makes the cell look like "2014.03.26"
    Cell K3:
    =DATEVALUE(MID(R1,1,4)&"/"&MID(R1,6,2)&"/"&MID(R1,9,2))
    This makes the cell look like "41724.00"
    Cell I3:
    =K3-7
    This makes the cell look like 19-Mar

    Next I have cells that break down that file name and try to input previous week's date in the filename in order to pull a number from cell K5 in tab Rolling Reports from previous week's spreadsheet:
    Cell R6:
    =TEXT(I3,"dd/mm/yyyy")
    This makes the cell look like 19/03/2014
    Cell R7:
    =MID(R6,7,4)&"."&MID(R6,4,2)&"."&MID(R6,1,2)&".xls"
    This makes the cell look like 2014.03.19.xls
    Cell R8:
    ="E:\Broadband_repair\BBR SUPS\Weekly Rolling Productivity Report\2014\["&R7&"]Rolling Report"
    This makes the cell look like 'E:\Broadband_repair\BBR SUPS\Weekly Rolling Productivity Report\2014\[2014.03.19.xls]Rolling Report
    Cell R9:
    ='R8'!$K$5

    When I try to excecute cell R9, it makes me try to search for the spreadsheet. I am not sure where I am messing up or if excel can even accomplish this. Any help would be welcome.

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013

Posting Permissions

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