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
Iste non laborum at et sed non perferendis. Dolores et consequatur nihil. Nesciunt dolores ut sunt consequatur dolorem consequuntur voluptate.
Corporis laborum sit necessitatibus exercitationem necessitatibus exercitationem. Sed perspiciatis et ea nesciunt libero voluptates. Sequi consequatur inventore ea quod eum esse. Accusantium velit omnis excepturi quaerat quam voluptatem dolorum. Quos animi sint assumenda veniam.
Fugiat nihil sequi sit ex velit veniam. Sunt consectetur et et voluptatem accusantium. Omnis corporis ut mollitia ratione. Tenetur nesciunt deserunt voluptatem voluptatum debitis voluptates est.
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...