Linking Worksheets within a Workbook

mmv113

New Member
Joined
Jun 20, 2008
Messages
3
I have one workbook with 12 worksheets...11 are Master Sources that have all been copied with the Paste Link feature into the 12th worksheet.

My question is: Is there a way to auto update the 12th one if new data is entered (NOT edited) on one of the 11? It would be nice to not have to copy and paste special everytime a new line of data is entered -- multiple people will be handling this workbook and I'm concerned that someone will forget this important step.

Thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
In what way are the 11 Master Sources linked into the 12th? Is each master source represented by specific columns in the 12th sheet? What layout is being used? What do you want to happen to that layout when a new line of data is entered? What about when new data is entered anywhere in one of the 11 master sources ... what is to happen then?

I have to say that I'm not sure that you approach is correct at all .. why have your data in separate sheets at all? An explanation would be appreciated.
 
Upvote 0
Hi,

Are you effectively keeping a contents page?

If so, maybe this could be used or a variation of, you could maybe add it within an OnOpen Event or worksheet_Activate Event which would keep the contents page up to date;

Code:
Sub IndexWs()

Dim Ws As Worksheet
Dim c As Range
Dim k As Integer

Set c = Application.InputBox(Prompt:="Where do you want to begin your index:", Type:=8)

k = Worksheets.count

For i = 1 To k

    With c
        .Value = Sheets(i).Name
        .Hyperlinks.Add Anchor:=c, Address:="", _
        SubAddress:="'" & Sheets(i).Name & "'!A1"
    End With
        
    If Sheets(i).ProtectContents = True Then
        c.Offset(0, 1) = "Protected"
    Else
        c.Offset(0, 1) = "Unprotected"
    End If
        
    If Sheets(i).Visible = xlSheetVisible Then
        c.Offset(0, 2) = "Visible"
    Else
        If Sheets(i).Visible = xlSheetHidden Then
            c.Offset(0, 2) = "Hidden"
        Else
            c.Offset(0, 2) = "Very Hidden"
        End If
    End If

    Set c = c.Offset(1, 0)

Next i

End Sub
 
Upvote 0
I suppose I could have prefaced my post by saying that I am in no way an Excel expert and that I tend to figure out the more advanced features on my own.
I created all 12 worksheets with the same header names in R1. I work in a college with 11 departments. Each department has their own scholarships...so each department has its own list/worksheet. The 12th worksheet is simply each of the 11 worksheets copied and Paste Special/Paste Link into it. I never entered any formulas, although correct the linking formula shows up in the formula bar when I click anywhere on the 12th worksheet.
If I update, let's say, worksheet #8, it also updates #12. But what if I want to ADD a scholarship to worksheet 8? Is there a way for it to auto add to the first empty row in 12? It may sound silly, but I thought there may be some way to do it considering the column headers in R1 are identical on all 12 worksheets.
If it comes down to me having to start over or re-do what I've already done, then I won't worry about it. Just thought it would be nice since, like I said, many people will be updating this thing.
 
Last edited:
Upvote 0
Have all your information in one list, with a column to indicate department. You can filter on the department column to view only scholarships for that department. This is not a flippant suggestion, you will be able to handle and analyse the data much better this way!
 
Upvote 0
GlennUK,
Thanks for the suggestion. I will run it by my supervisor - while it's not what she had in mind initially (obviously), I may be able to convince her otherwise!
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top