Excel: How can I display the data from several sheets in one?

Question

I have written a script which creates a complex Excel sheet which contains data from several data sources. The data on each sheet is usually 1-5 rows. It would be great if I could create another sheet which would display the data from the other sheets online (i.e. the overview sheet should change as I change the data sheets). I also need to format the data with various styles and have the overview sheet copy them. Lastly, the data sheets have different column widths.

I was thinking that I could simply import the sheets. This works when I save each sheet into a single file and then import them as OLE objects. Isn't there a way to achieve the same effect with just a single file?

[EDIT] What I need is to tell Excel "draw A1:F3 on Sheet2 on Sheet1 into cell A1".

"Insert Object" offers me to create a Word document or a PP presentation and put that into a cell, but I can't create a new Excel sheet and put that into a cell. What gives?

Answer 1

A single Excel workbook can contain multiple sheets. Formulas in one sheet can reference cells in another sheet in the same workbook.

Answer 2

Hey,

if you want a build a Solution by yourself try:
http://poi.apache.org/
Apache POI - Java API To Access Microsoft Format Files Its really easy to use...and you can easily format data etc...

I used POI with Apache Digester (http://commons.apache.org/digester/)
to read Data from XML Files and create Excel Sheets.

[EDIT] With this approach, you can read in the existing Excel sheet, copy the relevant cells between worksheets (including the formatting). That doesn't solve the "different column widths" and the "online update", though.

Answer 3

You can reference other sheets with the following syntax:

SheetName!Cell

so in Sheet1, A1 a formula of

=Sheet2!B4

will have a live reference to the value in sheet2 at B4

Answer 4

Copy the range. Move to the cell you want to hold the reference. Hold the Shift key down as you select the Edit menu. Notice there are new menu items; "Paste Picture" and "Paste Picture Link". You want to select "Paste Picture Link". Now whenever the "source" is updated or the format changes, the "destination" will update.

Sub DemoIt()

    'open excel and vbe windows side-by-side
    '   to see results when break points hit

    'setup values
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "A"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "B"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "C"

    'select "source" values and copy
    Range("A1:A3").Select
    Selection.Copy

    'move to "destination"
    Range("B1").Select

    'paste the "picture link"
    ActiveSheet.Pictures.Paste(Link:=True).Select
    Application.CutCopyMode = False

    'check out results
    Stop

    'change values
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "99"

    'notice "destination" changed
    Stop

    'change formatting
    Range("A3").Select
    Selection.Font.Size = 20

    'notice formatting changed
    Stop

End Sub

Answer 5

You can use camera objects.

1. Copy the range you want on the cover sheet
2. Go to the cover sheet
3. Hold down shift and click the Edit menu
4. Click the Paste Picture Link menu item

This pastes a dynamic picture object that updates whenever the corresponding region on the data sheet changes.