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
Et adipisci ducimus architecto vero et. Veniam et sit nam officia et accusamus optio. Ipsa consequatur recusandae quod qui ut dolorem dolor. Velit vel omnis eos beatae. Odit est fugiat ut est dolor.
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...