Displaying one dataset in multiple worksheets

[Darsu]

I need to organize a database (appox. 40 variables and 9500 records) in parallel worksheets, each containing the same data but sorted by different variables. The twist is that changes made to a cell in one worksheet should propagate into the corresponding cell in the other worksheets while taking into consideration that the cell will very likely be located on different rows in all worksheets due to the sorting. Is there any way to construct such a system with Excel so that a non-computer-savvy person can make changes to the data?

 

[Jon von der Heyden]

Hi.

Welcome to the board.

I think you may need to be more specific. As in what is the nature of the data, what is it's structure in the 'MASTER' table and in what different ways do you want it displayed? Suggest you use colo's Html maker.

Right now I'm thinking you should be investigating pivot tables and / or INDEX() and MATCH() / LOOKUP() / VLOOKUP() / HLOOKUP() methods to contruct the different views.

Regards,
Jon

 

[Darsu]

Thanks for the reply. I'm sorry, I'm not sure what constitutes essential detail in a case like this.

The data is very plain, there are no gimmicks involved. Each row describes one database record and contains values of number and string variables, one of these being a unique record identifier. This data should be sorted by eg. column A in sheet 1, column B in sheet 2 and column C in sheet 3. The sorting will be static.

 

[Jon von der Heyden]

Hi,

Suggest you use a macro then as formula may be quite a complex route to go down. Go to the VBE (Alt+F11) -> Insert -> Module and paste this into the new module:

Sub SortSheets()

Sheets("Sheet1").Activate
    With Range("A:C")
        .Sort Key1:=Range("A1"), Order1:=xlAscending
        .Copy Destination:=Sheets("Sheet2").Range("A:C")
    End With

Sheets("Sheet2").Activate
    With Range("A:C")
        .Sort Key1:=Range("B1"), Order1:=xlAscending
        .Copy Destination:=Sheets("Sheet3").Range("A:C")
    End With

Sheets("Sheet3").Activate
    Range("A:C").Sort Key1:=Range("C1"), Order1:=xlAscending

Sheets("Sheet1").Activate

End Sub

To run from Excel go to Tools->Macro->Run.

Is this helpful?
If not again I suggest you investigate pivot tables or that you post a sample of you spreadsheet.

Regards,
Jon

 

[Darsu]

Thanks for your replies. The problem wasn't the sorting, but the correct automatic propagation of changes from one worksheet to all the others. After sitting down and looking up leads, I have a clearer view of the problem, and I believe worksheet_change could be what I need. Eg. semi-pseudocode in my mind for Worksheet 1:

Private Sub Worksheet_Change(Sumthin' Target as Range)
    Application.EnableEvents = False
    row1 = Target.Row
    ID = Sheets("Sheet1").Range("AJ"+row1).Value   // Col. AJ contains unique database record identifier

    row2 = Sheet("Sheet2").Columns("AJ").Find(ID) // the row in sheet 2 where the changed database record is located
    row3 = Sheet("Sheet3").Columns("AJ").Find(ID) // same for sheet 3

    Sheets("Sheet2").Range(Target.Column+row2).Value = Target.Value
    Sheets("Sheet3").Range(Target.Column+row3).Value = Target.Value
    Application.EnableEvents = True
End Sub

A sensible scheme? (I still have an hour an a half left to learn VB syntax...)

Corollary stupid question: if I use VBA, will every Excel be able to use the code or do I risk the workbook being randomly crippled on some computers? The workbook will be sent to cities halfway across the country for its contents to be read and updated by people who must be assumed to know only the bare basics of using a computer, people who don't know what VB is and wouldn't recognize a misfiring Excel macro.