Excel 2007 question - display information from various worksheets in a master worksheet

[sandra_nz]

I have 8 workbooks, all set up the same way, with information in columns A - Q, from rows 10 onwards.

Is it possible to create a new workbook and have it display all the nonblank rows of information from these 8 workbooks?

[BubbaDog]

There are a number of ways to do this depending on how you want to treat the data.

• You can write a macro that opens and copies the values in each page of the 8 satellite workbooks when you open it.

• You can directly link cells in the master book to cells in the satellite books. Any changes to the satellites are automatically updated to the master when you open it. If changes occur to the satellites after you open the master those changes will update when your master book recalculates.

There are advantages and disadvantages to either method. The copy values method requires macro programming using VBA. VBA is rather easy as far as programming languages go and Excel has some great auto-programming capabilities via the “record macro” feature. But its still a program language requiring some investment in learning time.

The linking method is fast but it can get messy. A simple link example you can try is to open two spreadsheets. If you are familiar with writing calculations like selecting A1 typing = and then going a selecting another cell and hit enter. this automatically links your second cell to A1.

Well, you can do the same thing with any open workbook. If you select A1 in workbook 1, type = and then open another workbook, select any cell and hit enter; that second workbook now has a cell linked in to your first workbook.

So you can link A1 of sheet 1 in your master workbook to a satellite and by copying across and down link all cells of one workbook sheet to another. Now you will end up with zeros where blanks occur but with a little thought you could either format those away or change you formulas to show a blank when a blank occurs.

One big problem with active linking is that the master sheet will not keep track of any links that move. So if one of your linked satellites gets moved to another directory or you try to move everything to a new hard drive, the links will crash.

[sandra_nz]

I think you’re right, in that I need to learn some VBA. Might go check out a For Dummies book! Thanks for your help.

[drachillix]

@sandra_nz

I have 8 workbooks, all set up the same way, with information in columns A - Q, from rows 10 onwards.

Is it possible to create a new workbook and have it display all the nonblank rows of information from these 8 workbooks?

Once you start getting much deeper it may be prudent to mount these workbooks as access tables and start using access to do your reporting/heavy lifting. Its reporting systems are waaay more powerful and flexible.

[CookingWithGas]

@BubbaDog

The linking method is fast but it can get messy. A simple link example you can try is to open two spreadsheets. If you are familiar with writing calculations like selecting A1 typing = and then going a selecting another cell and hit enter. this automatically links your second cell to A1.

Well, you can do the same thing with any open workbook. If you select A1 in workbook 1, type = and then open another workbook, select any cell and hit enter; that second workbook now has a cell linked in to your first workbook.

It is possible to set up formulas that reference only the nonblank rows. This type of formula gets long and complex, however, with a lot of fancy footwork using some combination of INDIRECT, INDEX and MATCH. If you use INDIRECT in a formula referencing another workbook, the referenced workbook must be open or you get errors.

If you want help with VBA I highly recommend www.excelforum.com. There is a Programming forum there for VBA problems. Free registration. You can attach files, and there are gobs of people who are willing and able to help.