link several worksheets

[Guest]

Is there any way to achieve following:

WORKSHEET 1 (contains downtime in hours, max value 24.00)
link1 link2 link3
date1 2.50 0.00 1.50
next date 0.00 0.00 0.00

WORKSHEET 2 (computes availability in percentage based on value in worksheet1)

link1 link2 link3
date1 89.58 100.00 93.75
next date 100.00 100.00 100.00

Thus every day, data is fed in WORKSHEET1, and thus it keeps on growing in
size.
The data in WORKSHEET2 should get updated automatically, by addition of
extra rows everyday, and copying formula etc.

Kindly also help if WORKSHEET two data should appear in transposed manner,
i.e. with each row addition in WORKSHEET1, columns get added in WORKSHEET2.

Please help

 

[David McRitchie]

Why not just use Worksheet 1 and have it contain everything,
then you don't have to work hard at making things work.

One way to keep Worksheet 2 up to date for viewing, would be
to recreate it each time the worksheet is selected for viewing
with a worksheet activation event macro.
http://www.mvps.org/dmcritchie/excel/event.htm

Disadvantage is that the sheetname for worksheet 1 would
have to be in the macro or determined from the sheetname,
which may not stand up to renaming of worksheets.

As long as all cells refer back to worksheet 1 including those in Column A
A2: = 'worksheet 1'!A2
B2: =100*(24-'worksheet 1'!B2)/24

you could extend the formulas from the row of the last cell in A to the
row of the last cell in A of worksheet 1.

Since you or someone else is going to ask for the solution as asked:
here is the code for worksheet activation

Right-Click on the sheet tab for the second sheet, view code, place the following code therein:

Option Explicit
Private Sub Worksheet_Activate()
Dim primaryname As String
primaryname = "wk 1" ' <=== FILL IN NAME OF PRIMARY SHEET
Dim Lastrow_primary As Long
Cells(Rows.Count, ActiveCell.Column).End(xlUp).EntireRow.Select
Lastrow_primary = Sheets(primaryname).Cells(Rows.Count, ActiveCell.Column).End(xlUp).row
If Lastrow_primary > ActiveCell.row Then
Range(ActiveCell.row & ":" & Lastrow_primary).Select
Selection.FillDown
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'-- simulate manual activation of worksheet -- basically for testing in this case
Cancel = True 'Get out of edit mode
Worksheet_Activate
End Sub

Kindly also help if WORKSHEET two data should appear in transposed manner,
i.e. with each row addition in WORKSHEET1, columns get added in WORKSHEET2.

I think you have enough to get started, when you understand the coding
you can figure the third sheet out for yourself. Hint recording a macro
for Fill RIght (Ctrl+R) should help with the macro code to see what you
might need. The code supplied did the equivalent of Fill Down (Ctrl+D).