linking data from multiple sheets

[sjhonny]

hi,
i have a large excel file with many tables, all pretaining to a list of companies.
one sheet contains the list of companies, and other sheets either contain data about the companies or graphs / formulas.
i would like to to 'break' this large file into smaller files like so:
i'd like to have each table in a file of its own, and have it linked to the large file, in such a way that whenever a change is made to the data in the smaller files, it's automatically shown in the large file.
the list of companies in the small files, though, must be synchronized with the list of companies in the large file (so that if i add companies to the large file, they will also be added to the smaller files).
hope i made myself clear..
any ideas?
thanks

[raypayette]

If everything is properly structured, I would use the macro recorder to code a sample of what I want then I would apply it to all your file.

[sphinx]

i belive that i have same issue, where i had one main table, and most of the time i need to update some rows and colm, i crean an search page, but it is not working probaly , i need also help for the same , to update the code to have the folloing
serch by name and number, field , between dates ....and then allow me only to update particular column

pls find below code

Sub INQURY()
Sheets("Inqury").Select
ActiveSheet.Rows.Hidden = False
[A3:Ad1000].ClearContents
s = ""
s = Application.InputBox("What are you searchin for !", "Search")
If s = False Then Exit Sub
If s = "" Then Exit Sub
Application.ScreenUpdating = False
With Sheets("TRCK").[A4:AC1000]
Set MySearch = .Find(s, LookAt:=xlWhole)
    If Not MySearch Is Nothing Then
        F = MySearch.Address
        MyRow = 4
        Do
            x = MySearch.Row
            y = MySearch.Column
            For MyColumn = 1 To 29
                Sheets("Inqury").Cells(MyRow, MyColumn) = .Cells(x, MyColumn)
            Next MyColumn
            MyRow = MyRow + 1
            Set MySearch = .FindNext(MySearch)
        Loop While Not MySearch Is Nothing And MySearch.Address <> F
    End If
End With
For A = 3 To 1000
    If Cells(A, 5) = "" Then Rows(A).Hidden = True
Next
ActiveWindow.SmallScroll Down:=-100
Application.ScreenUpdating = True
If Application.WorksheetFunction.CountA([A3:AB100]) = 0 Then
    MsgBox "Sorry ! No Data Found", vbExclamation, "Sorryð"

[sjhonny]

1. i found a solution of linking the files like so: [sourceFile]sheetname!A2:A5 etc. It worked well when working on a single computer. however, if i change 'sourceFile' on one machine, i can't see the changes in the other file on a different machine unless i close and re-open it. is there a way i can see it without having to reopen the file?
2. i have a column of numbers (say- prices of different products) i want to average. however, the range of cells is not fixed- it depends on how many products there are; is there a way to write a formula that uses the value in the cell only if the name column in the same line is not empty?