How do I display a range of data from multiple workbooks and sheets in orde

By fletch9 ·
I am curently serving overseas and I have a excel scripting question. I have 3 years of weekly updated workbooks that I would like to display in chronological order. These workbooks contain 5 worksheets each with specific data ranges that track equipment issued to different organisations. The data ranges aren't in the same location on each sheet i.e one worksheet has 5 differnt organisations on it the next 3 etc. Is there a way to pull the data ranges out for each specific organisation and display in chronological order the data so I can see the change in the amount of equipment on hand ( when they gain and/or lose equipment)? I have a basic understanding of scripting and I have tried to modify several that I have found online, but I am either implementing it wrong or I'm searching for the wrong script. Any and all help is greatly appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It might be easier to deal with in a database

by Darryl~ Moderator In reply to How do I display a range ...

Do you also have MS Access installed?
Is there a heading/title at the beginning of each column which remains the same in both the 5 & 3 column spreadsheets?
If you answer yes to both what you could do is create a table with all the required fields which would be named the same as the column headings in the spreadsheets then you could import each spreadsheet into the table (make sure you select "append" when importing); Access should detect the headings as field names if you're using Office 2007 or 2010, if not, make sure you check the box for "first row contains field names" when prompted.....Access won't care about the "order" the spreadsheet columns are in, it will look at the first row (field names) & put the data in the proper field. Once done, you could export back to an Excel file if you are more comfortable using it for your calculations.

Collapse -

might be over thinking it

by databaseben In reply to How do I display a range ...

your probably over thinking by using scripts.

since you can link cells between workbooks, you should just create a workbook that will display the tallies / summaries from all the worksheets.

or like in the prior post regarding msaccess, you could create a database "but" have the database link to your excel worksheets.

however, building a database may be over doing it "because" you just want to see a simple variance, ie, see the change in the amount of equipment on hand ( when they gain and/or lose equipment)

Collapse -

Database may be best

by mperata In reply to How do I display a range ...

If I am reading your Q correctly, you may be needing to report on the same piece of equipment used at different times.
If you simply link the ranges from the various workbooks you'll end up with multiple entries for the same piece of equipment.
If you create a data base, then you could do a search on the single piece of equipment and list its history separately.

Related Discussions

Related Forums