Excel help.

December 27, 2006 1:12 PM

Excel question: I'm looking to pull a months and years worth of spreadsheet information to a summary report. The days work is saved as reportxxxxxx (month/day/year)

Rather than opening all files, I would like excel to look up the information on the summary and extract a time or comment column from the day's reportxxxxxx. All reports are stored in a common folder but some are missing. At some points during the past two years, new information has been added and some deleted, making a specific cell lookup pulling incorrect information. Suggestions welcome.

posted by brent to computers & internet (4 comments total)

Get an intern to normalize the data into a database, and stop storing important business logic in Excel.
posted by SirStan at 1:17 PM on December 27, 2006

If there's a particular label for the row you want - like "total" or "time" - then you can use match-index to do this.
posted by milkrate at 1:25 PM on December 27, 2006

for stuff like this, i use VBScript with the Excel Automation functions/object (built-in to Windows/Office).

But SirStan has it right; DATABASE DATABASE DATABASE!
posted by chota at 7:15 PM on December 27, 2006

If you have Access it makes it pretty easy to convert Excel into a database. Oh, and quit putting that kind of stuff in a spreadsheet!!!! (Forgive me, huge pet peeve)
posted by Carbolic at 9:11 AM on December 28, 2006