1

I have been trying for a while, I need to get the same values from almost 5 years worth of daily workbooks. They are labelled sequentially (2017.08.14, 2017.08.15, 2017.08.16 etc), but I have tried several formulas that insert this into the full address string and they tend to end up with REF! I am quite happy to use a VBA solution, or really any approach that doesn't involve manually opening and copying several values over 1500 spreadsheets.

The answer I feel might be closest was when I attempted; =INDIRECT("'S:\Reports\2017["&E2&"]Totals'!$C$17") where E2 refers to the cell containing the date string for today, and C17 is tthe target cell in the other workbook, but despite the fact that following it step by step produces =INDIRECT("'S:\Reports\2017[2017.08.19]Totals!$C$17) it still produces REF!, even though simply typing that formula in a cell works fine.

Hubert P Tubbledale
  • 21
  • 2
  • 1
    This is _not_ a free coding service, and also your question involves several steps. I would 1) get number of excel files in directory, 2) loop through each file, 3) inside that loop, open each file, 4) copy desired data, and 5) close opened workbook. Try googling `excel vba get number of files in directory` and `excel vba open file`. – CMArg Aug 20 '17 at 12:38
  • I apologize if my question offended you, I was just seeking help for a problem that had flummoxed me for a while. I was not demanding a coding solution, merely stating that I was familiar enough with excel to use a VBA one if someone proposed a solution that required use of VBA, as I had seen similar questions previously marked as "without using VBA" and I thought there was no point increasing the complexity of the issue for anyone kind enough to help me. You appear to be saying there is no simple way to create a shortcut method without VBA, thank you for that much clarification. – Hubert P Tubbledale Aug 20 '17 at 12:46
  • I was not offended, and there is a simple task. But solution involves several steps. Try to merge together [this](https://stackoverflow.com/a/10382861/1726522) for scanning a directory and [this](https://stackoverflow.com/a/35124870/1726522) for opening and closing workbooks. – CMArg Aug 20 '17 at 13:49
  • You cannot use INDIRECT to reference a closed external workbook. That is explained in the docs for that function. Search for INDIRECT.EXT as an add-in or write your own. –  Aug 20 '17 at 13:50
  • 1
    Show us the code part that gives you the error you mention –  Aug 20 '17 at 17:47

0 Answers0