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.
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.
