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

[kmonaco]

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

[pieterse]

Not sure if this is what you're after, but you might try the INDIRECT worksheet function.

[kmonaco]

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

[pieterse]

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.

[sdckapr]

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