Hello,
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.
- Follow via:
- RSS
- Email Alert
Question
0
Votes
How do I display a range of data from multiple workbooks and sheets in orde
22nd Feb
Answers (3)
0
Votes
It might be easier to deal with in a database
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.
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.
22nd Feb
0
Votes
might be over thinking it
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)
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)
22nd Feb
0
Votes
Database may be best
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.
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.
23rd Feb
