Many Sheets, One DB

[lewashby]

I deal with a lot of Excel files on a daily basis and I need some help. I may have 8 fields (columns) in one file or sheet, let's say file/sheet A, but I may need some of the same data (rows) in another file/sheet, let's say file/sheet B, but maybe only using five of the original fields in file/sheet A and maybe only rows where field ABC = XYZ. I'm currently having to edit more than one file or sheet with mostly the same information on a daily basis and I know there's got to be a better way. Each time I enter the same information I'm giving myself the opportunity to make a mistake, I'd like to be able to insert, drop, and edit changes in one file or sheet and have the other files/sheets act as little more than displays for the main data sheet. I think the current version of Excel I'm using is a little dated, 2010 I think but I'm not sure. If you have a solution please let me know even if it will only works in new version as I might be able to get my employer's IT supervisor to upgrade my Office edition. Thanks for any and all replies.

[gebobs]

Why are you re-entering information? Can't you just copy the sheet you need, delete the unwanted fields, and filter the data you want? You could write a simple macro to automate it if that suits you.

[lewashby]

Originally Posted by gebobs

Why are you re-entering information? Can't you just copy the sheet you need, delete the unwanted fields, and filter the data you want? You could write a simple macro to automate it if that suits you.

I work in a prison and I deal with a lot of inmate files. I manage their work schedules, time, religious diets, allergies, etc... I would like to be able to create a master sheet for a particular area, diets and allergies let's say. Then I would like to be able to create other sheets that read from and are based off of the master sheet. For instance, I might want to divide the sheets by the units they are in for example, so for all inmates that live in units 1-10 the prison staff would have sheets that contain all the inmates in units 1-10 and for all inmates in units 11-20 I could have another sheet with those inmates for the staff in that particular area. But the additional sheets would be automatically built based on the value of a given column that I could add/remove inmates from one sheet or file and have it carry over to the other derived sheets / files. Is this something that a macro would be good at? I've never dealt with macros in any shape form or fashion and outside of a little C++ tinkering I've never really heard of them. Thanks.

[gebobs]

As for separating by units, since each inmate can only have one unit (I presume), that could easily be handled on the same sheet by adding a field for unit and filtering. I know you want separate sheets for this but I only suggest this because I'm a stickler for avoiding data duplication.

However, I'm not sure that is the case for your staffing situation. Would each inmate have many staff? If that's the case, then I think you are forced to use multiple sheets. That in turn would, I think, need code, if it can be done at all in Excel. If you prefer to go that route, maybe repost in the programming section. My VBA skills are rusty and were more with Access any way.

And, speaking of Access, if it is a many-to-many relationship, a relational database might be a better platform, though I know Access has fallen out of favor of late.

However, if there's any chance you think the one sheet/filtering option might work, I'll be glad to help.

[lewashby]

Originally Posted by gebobs

As for separating by units, since each inmate can only have one unit (I presume), that could easily be handled on the same sheet by adding a field for unit and filtering. I know you want separate sheets for this but I only suggest this because I'm a stickler for avoiding data duplication.

However, I'm not sure that is the case for your staffing situation. Would each inmate have many staff? If that's the case, then I think you are forced to use multiple sheets. That in turn would, I think, need code, if it can be done at all in Excel. If you prefer to go that route, maybe repost in the programming section. My VBA skills are rusty and were more with Access any way.

And, speaking of Access, if it is a many-to-many relationship, a relational database might be a better platform, though I know Access has fallen out of favor of late.

However, if there's any chance you think the one sheet/filtering option might work, I'll be glad to help.

Sorry it took so long for me to get back to your reply. The filtering thing might work perfectly and I see only one possible problem with it. When I print out the sheets because the inmates have to sign by their names, I print them of in a range based on the units on the compound. So I have one sheet that has all the inmates for units 1-16, another for units 17-20, 21, 22 + 23, 24 + 25 and one last on for unit 28 (26 & 27 don't exist yet). Each of these sheets has a few rows for a heading describing which units its for and a head header cell for each column. If I use the filtering technique your are describing will I be able to print the sheets out with different heading cells? Thanks again.