I am trying to build and "automate" a report that pulls data from many spreadsheets. I'm not too bad with excel but have little to no experience with macros, etc. but am willing to learn. Let's say I have a folder on my hard drive with a number of files titled "File 1 Jan 10 2009", "File 1 Jan 11 2009" and so on. Ideally what I would like to create is two cells in excel where I can enter the dates and then I want excel to pull specific data from the spreadsheet and run some simple metrics. I could go and copy and paste the data from day to day but figure I should make it simple and save myself some time.
Anyone know how to do this or know where I can find out how to do this? I did some google searches and in a forum or two but have yet to find anything.
Thanks
You will want to use the INDIRECT() and OFFSET() functions to create dynamic references. Remember that you can also use & to combine strings for filenames, for example:
Normal: =[filename2.xls]Sheet1!A1 Dynamic: ="[filename"&B2&".xls]Sheet1!A1"
Doing this off the top of my head, I haven't tested it in excel - but if that doesn't work, read the help for the INDIRECT() function.
thanks for the reply.
in the indirect function's help, it says "If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value. "
The point of my exercise is to try to save time by not opening up several workbooks but just changing the date in a specific cell and have it go to the right path on my hard drive and pull the right data from the right file. is this possible? even if i have to leave the computer for a few min its fine by me. I just dont want to open up so many files. any ideas?
thanks
Debitis cum eos consectetur qui et. Itaque dolores eius necessitatibus perferendis consequuntur est ut.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...