Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Repeating Data Across Sheets (Excel 2000, SP-1?)

    Okay, I was planning to do something in this project that I'm working on that I thought would be fairly straightforward. It is similar to something I do in Word all the time without code. But heck if I can figure it out! I don't know if I just don't know what to call what I'm trying to do, so I can't find it in any of my reference books, or if it is maybe just one of those things I'm going to have to do through VBA. Here's the scenario. Hopefully, someone can set me straight:

    The workbook has many sheets. On a number of them, the first six columns of data needs to be the same, with additional columns of data on each sheet reflecting the purpose of that sheet. But the information is not static. Users may add, insert, or delete rows on the primary sheet and all the subsequent sheets will need to be updated to reflect the changes, sort of like cascading updates and deletes in Access. Right now, I have the first five columns of the first 150 rows (hopefully more than enough) on the "second" sheet filled with a formula something like this:

    =IF('Sheet1'!RC="","",'Sheet1'!RC)

    That works fine for the existing data, and anything added at the end up to row 150. But inserted and deleted data just updates the formula to refer to the corresponding +/- rows as appropriate (which, under other circumstances, is probably exactly what I'd want it to do). Am I missing some way to refer to a range or sheet that creates this type of interdependence? Or is this something that calls for VBA? And, if it is VBA, and there's a sample of something similar around somewhere, a link to that information would be greatly appreciated. I've attached a sample file. Many thanks!

    By the way, I think I am probably using SP-1. The LAN admin. blew up my computer a couple weeks ago while I was gone for the day, and he had to reformat the hard drive and reinstall everything. I'm pretty sure he didn't install any service packs, and I haven't had time myselff, yet.

    --Karyl

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Data Across Sheets (Excel 2000, SP-1?)

    Not sure if this is what you're after, but you might try the INDIRECT worksheet function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Data Across Sheets (Excel 2000, SP-1?)

    I looked at the Indirect function, and it would solve part of the problem (and a bunch of others in other situations, so a handy function to know about), but it would not solve all the issues with this workbook. But I've been hunting around the web this afternoon, and I think doing something with grouping worksheets will work. If I create formulas to control the data in the last three columns on the Primary sheet, so the user never enters data there, I think I can write code that automatically selects all the worksheets that need to be synchronized whenever the primary sheet becomes active. This would do everything I need to do, I think, as long as the synch'd sheets all shared the same initial columns of data.

    Can anyone see any potential problems with pursuing this line of development? Or have any suggestions for avoiding trouble or facilitating the process? Many thanks!

    --Karyl

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Data Across Sheets (Excel 2000, SP-1?)

    But you must be certain they do not put anything in cells that are NOT the same in those sheets. As long as that is ensured, I see no problem with your approach.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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: Repeating Data Across Sheets (Excel 2000, SP-1?)

    I suggest combining the sheets and just freezing the panes. It might be less problematic inserting/deleting will work on all the items at once.

    You could "group" the columns (select the columns - data -group and outline) to allow "hiding" the various "groups" that would be on other sheets.

    Steve

Posting Permissions

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