Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking worksheets (Excel XP)

    I am creating linked worksheets in one workbook. So far, I have a master sheet that the data for 60 people is entered. I have created one worksheet that has all the appropriate links (35) connected for one person (one will be needed for each of the 60 people). Is there a way to somehow copy the first linked worksheet with all the styles, formatting and links, so I do not have to manually do each of the 60 required?? Yet change the link information down a row? Please say it is so [img]/forums/images/smilies/smile.gif[/img] I have attempted this several ways but an unable to figure out an easy way to change the links. Thank you

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking worksheets (Excel XP)

    copy the worksheet (it should then be identical to first). Then find/replace to find the old link and replace the new file for the new person. Repeat 60 times.

    You could do it once with the macro recorder on, and then modify it to loop thru 60 times. Keep the find the same, but change the replace to read from a 60 record array that you populate with the appropriate file info.

    Steve

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking worksheets (Excel XP)

    With all those links, you will find Navigator Utilities handy to keep track of them all. With Navigator Links, you can see all the links in a list box for each linked workbook. You can also produce a report of all links for checking that you have entered them all correctly.
    You might also find the enhanced Find/Replace utility helpful too if you are using find and replace to edit your workbook. The Navigator FInd/Replace can work across all worksheets in a workbook, or even all currently open workbooks.

    regards

  4. #4
    Lounger
    Join Date
    Jan 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking worksheets (Excel XP)

    I am not sure I understand..............Example of some of the formulas are [img]/forums/images/smilies/sad.gif[/img]=Master!$B$5) (=Master!$F$5) (=Master!$D$5) where all the 5's, now need to be 6's (etc.) in the copied worksheet. How do I do a find and replace only one character??

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking worksheets (Excel XP)

    If the 60 reference change by only one row, maybe instead of linking individual cells, you might be better to name the entire range in master that you will use as a lookup table and then in the 60 sheets just have a reference to a row/column. Then all you have to do is change the row or column on the individual sheets to pull up different info. You might not even need to have 60 copies of links only 1 and just change several values in it to lookup a different range to be able to "on the fly" pull up 60 peoples data.

    You can use the:
    =INDEX(array,row_num,column_num)
    or even
    =OFFSET(reference,rows, cols,height,width)

    Steve

  6. #6
    Lounger
    Join Date
    Jan 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking worksheets (Excel XP)

    Thanks for the quick reply. Would you mind if I sent the workbook I have started with the master worksheet and the first worksheet that references from it. Would you look at it and prvoide me with suggestions.

    I have do not have strong skills in excel.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking worksheets (Excel XP)

    You can attach a sample file of what you are trying to accomplish with some explanation. Instead of an example with 60 maybe 3 or 4 and you can expand the concept once you understand. I think I know what you are trying to do, but the explanantions can be clearer if I make it more related to your project then too general, so please give us some details about your goals and setup.

    Steve

  8. #8
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking worksheets (Excel XP)

    When using Find/Replace, make sure that "Find Entire Cells only" is not selected. You would then choose to replace $5 with $6. Change them one at a time or choose replace all.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking worksheets (Excel XP)

    This might seem an odd question, but did you post a workbook and later delete it?

    Before I quit for the evening last night, I thought I saw that you had posted one, and this morning, when I came to look at it (and work on it), I can't find it.

    Steve

  10. #10
    Lounger
    Join Date
    Jan 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking worksheets (Excel XP)

    Sorry. I realized I had not completely finished the draft. Here goes............on the Master sheet - data will be entered that needs to be reflected on the corresponding worksheet. This will be printed out for each individual person. I made copies of the good one and did a simple find and replace to adjust the cells. What is not happening, is when data on the master sheet changes, it is not changing on the corresponding worksheet. I am not sure my find and replace worked out. Please advise.

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Linking worksheets (Excel XP)

    Hi,

    Take a look at the attached revision to your spreadsheet. You'll note that I've added a new worksheet named 'Student'. If you type a student's name into cell A/B2, the scores for that student are imported into the worksheet. This obviates the need to have a separate worksheet for each student, unless you really want to go down that path, because simply changing the name changes the displayed data. If you need a separate worksheet for each student, copy the 'Student' worksheet as often as needed and change the name in cell A/B2 accordingly.

    The revision uses a combined INDEX & MATCH formula to extract the data for the student referenced in cell A/B2 from the master worksheet. These formulae are volatile, meaning that every time you close the workbook you'll be prompted to save even if nothing's been changed.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking worksheets (Excel XP)

    How about this? I have your master and just 1 sheet for ALL the teachers. Just select the name from the pulldown and the index looks up all the items in the appropriate row and puts it into your table.

    This has the advantage that if you want to change the format, you only have to change it once. If you add more students all it takes to expand all the ranges is to just insert a row within the data range.

    Steve

  13. #13
    Lounger
    Join Date
    Jan 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking worksheets (Excel XP)

    WOW! They are both so good and so much easier than what I was trying to do. Thank you sooooooooo much. They were created so quickly! I need the same course you both took to learn this software program. Thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •