Home |
Search |
Today's Posts |
#1
|
|||
|
|||
link several worksheets
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 |
#2
|
|||
|
|||
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). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "hsg" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a Link Mid-way Across Worksheets | Excel Worksheet Functions | |||
How to link worksheets so editing one will edit others automatica | Excel Worksheet Functions | |||
how do i link a list of items in a workbook to worksheets in the . | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
What formula do I use to link worksheets together within one spre. | Excel Worksheet Functions |