Using one worksheet as a "Template" for 100 other sheets

[CHillFL]

Is there a way to use the first worksheet in the workbook as a 'template' where if I use that worksheet to create 100 other worksheets in the same Workbook and I need to modify a formula I can do it on the first worksheet 'template' and it modifies through all of the worksheets, but if I add data to any of the 100 other worksheets the template isn't affected and neither are the other worksheets....

Does that make sense?

Thanks,

Carol

[ChemistB]

You could group all the sheets whenever you are working on the "Template" Any changes you make when the sheets are grouped will appear on all sheets. Certain things cannot be done in grouped sheets, like Data Validation and Conditional formatting but formulas can be changed in mass. Then ungroup the sheets to work on the individual sheets. Would that work for you?

[Richard Buttrey]

Hi,

It makes perfect sense. To make a change on any sheet and have it replicated through many other sheets you need to GROUP the sheets. i.e. select a sheet tab name, hold the Ctrl Key down and then select them. That may be a bit of a pain to do manually so the following macro would do it for you

Please Login or Register  to view this content.

However and assuming this is a new development and before going too far down the line with it I'd seriously question why you want to have 100 different sheet tabs. Particularly if any data analysis or reporting across all sheets is needed. I've lost track of the number of times I've seen people use well crafted, colourfully formatted and wizzy sheets just for capturing data and then realising they can't get the summary information they need.

Don't mix up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round. You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So create a single database sheet that will hold all the information for all your 100 sheets. Then it will be a simple matter to extract a subset of information from the database to a reporting template.

[CHillFL]

Richard,

This is an on-going project that I am modifying with improvements as I work with it - definitely a work in progress. I have 100 employees that I am tracking various categories for a monthly reward program. Every week I have 18 possible data points to collect for each of 100+ employees. Then each week of the month is averaged or added and then weighted together for an ultimate single score and that is how we base their reward for the month. Each month has either 4 or 5 weeks associated with it.

I have been using 1 workbook per employee with 12 worksheets (1 per month). Now into my second month of data collection and input, I am finding this tedious to say the least. If I have to temporarily remove a data point from the equations for a certain month or make other formulary changes I have to do it 100+ times....and heaven forbid I miss an employee's workbook.

I thought I would create a template that gathered all 18 data points each week of the year and then break it up every 4 or 5 months to get my monthly total. Do all this on the same worksheet for each employee and have all the employees in 1 workbook, 1 worksheet per employee. That was why I asked the question, so that I could truly make the master changes 1 time and not 100+ times. I have thought about that being a very big number and that I might have to split it up into 2 different workbooks, but that would be better than 100+ workbooks.

As it is, this part of the process is actually the collection point for a number of other reports and is not the final roll up for the company totals. At this point it is very manual and tedious, partly because the majority of the reports are subjective and need to have someone look at the results to determine if the employee is going to get "marked off" for the item or not. The "Main" report takes quite a bit of 'work' to take it from 'raw' data to the final numbers before I have 4 of the 18 numbers that I need (these 4 are the primary data points). Then there are at least 7 other reports that have to be reviewed for the rest of the 18 data points.

There will come a point when I would LOVE to be able to take the 4 data points that come out of the main report and have them automated into this collection point so that I don't have to do all the data entry. I'd also like to change it from a weekly process to a monthly process, however, all that is 'down the road' as we work with the data and determine what we are seeing and where we need to go with it. We do hope to eventually have this process as automated as we can and have a truely working and successful model, but we also know that this is going to take some time and pain as we move to that final destination.

That all being said, this particular workbook is the collection point of several other workbooks - but they are not linked, right now it is manual.
The roll up or reporting for this data - outside of the monthly final number for each employee for each month - is not part of this spreadsheet (that is a whole other can of worms I have to figure out).

Can Excel do it for me? I'm sure it can. Am I ready to implement tomorrow? No, we are still figuring out what we need and how to improve the original entry of information that ends up in all these reports (how to only enter the anomalies so the subjectivity is removed).

With an x axis and a y axis available to me, I haven't figured out how to track 100+ employee's 18 data points each week on a single worksheet....if you have an idea, please do share I would LOVE to see such a thing. Would you repeat the 18 data points across the page for every week? And with manual data entry being done right now, how would you know which week you are entering? Can you see my struggle?

Carol

[CHillFL]

ChemistB - yes, yes, that worked beautifully. Thank you! Carol

[Richard Buttrey]

With an x axis and a y axis available to me, I haven't figured out how to track 100+ employee's 18 data points each week on a single worksheet....if you have an idea, please do share I would LOVE to see such a thing. Would you repeat the 18 data points across the page for every week? And with manual data entry being done right now, how would you know which week you are entering? Can you see my struggle?

Carol

Hi Carol.
No I don't see why you would struggle with this. The thing to bear in mind is that the amount of data you enter will be the same whether you enter it across 100 sheets or in a single sheet, one way or another you need to record 93600 numbers/values (100 * 18 * 52). And to answer your specific question about the week reference this would be one field (or column) that held the w/e date. I'm not sure what you want to plot on the x & y axes, presumably the data points would be one axis but what would be the other axis. Depending on what you want to present and how then the table of data would be 93600 rows in four columns for

Date
Employee
Data Point Number
Data Point Value

For these sorts of system I generally create a dedicated data entry area where you'd enter the four values for each record and click a button. This would automatically add the new record to the database. The data entry area cells would be validation drop down cells allowing you to pick an employee name or data point.

It sounds like you have formulae to create other fields/columns of information which presumably use the data values you've entered. The beauty of using the sort of design that I'm talking about is that the rules for these formulae would be held in a dedicated area outside the database.

I see the system being driven by a few simple macros to extract each employees data to a report template to which the formulae would be applied and added at run time. This means that changes to formulae need only be made in one place and avoids your nightmare of having to change 100 sheets each time you change a formula.

[CHillFL]

Richard,

That is why I needed to 'see' what you meant, because it is totally different than where my brain was or where it even started.

I was looking at
date 1 date 2
employee1 data1 data2 data3 data4 data5 data6 data7 data8 data1 data2 etc
employee2 data1 data2 data3 data4 data5 data6 data7 data8 data1 data2 etc
employee3 data1 data2 data3 data4 data5 data6 data7 data8 data1 data2 etc
etc

What I 'hear' you saying is

date date1 date1 ....... date1
employee employee1 employee1 ........ employee2
data point number data1 data2 through data8 data1
data point value 0.00 0.00 ........ 0.00

However... (Lightbulb moment, maybe?)....That would allow me to enter the data points as I worked on the report and they wouldn't necessarily need to be in a certain cell.....in other words, I could enter employee1's data2, data6, data7 and data8 and then employee67's data6, data5, data4, right? I wouldn't have to enter all of the employee's data into the input at the same time. Because the report that pulls would do it based on a date range, employee, and data point number, right?

The formula's that I have right now are as you say - but they could easily be in a separate file that pulls from the database. And it would make individual employee reporting and corporate wide reporting much easier.

Can you confirm that my lightbulb moment is real and of the color it should be?

Thanks,

Carol

[Richard Buttrey]

Hi,

It's not quite what I had in mind. Have a look at the attached.
The Data sheet is the database I was talking about, or at least the first two weeks of rows for just two employees.
The Pivot Table Report sheet will react to selections you make with the two Slicers for Employee and w/e Date
The Employee Report will also react to the Slicer changes and extract the subset of data for the selections
The Employee Chart charts the data on the Employee Report.

It may not be exactly what you want since I don't yet fully understand your data but hopefully it gives you some ideas. I've not built in any formula rules that would kick in when you extract subsets of employee data but that would be a trivial addition

[CHillFL]

Richard,

I see what you are saying now. I haven't had the pleasure of diving into Pivot Tables as of yet. But I am liking this - it would make it easier, for certain. My data entry would be quicker. And adding new employees would be easy (we add several every month). I am extrapolating that we would be able to place several weeks on the Report page so so that we can see the monthly picture....so, yes, yes, I am liking this. It would be a great base to expand off.

I have attached what I am currently working with to give you a better understanding of what we are trying to do (I should have done that already - sorry). And yes, I am certain I am trying to do too much in one worksheet, especially after seeing your sample. I see so many more possibilities now and this whole thing doesn't seem quite as overwhelming as it did (it has been extraordinarily overwhelming at times). I knew Excel was capable, and I knew my current skill level was not. I obviously need someone with a more advanced skill set to send me in the right direction.

Do you think, after seeing what we are attempting to do, that I can do all that with the direction you were going?

Thanks, Carol

[Richard Buttrey]

Hello Carol,

My experience of 37 years with spreadsheets in general and 20 years with Excel tells me that using a database in the way I've shown along with either a Pivot Table report and associated chart or a purpose built report is eminently doable and the most efficient way to go

Pivot Tables are absolutely brilliant for summarising and analysing data in just about any way you want. They don't involve formulae or VBA and provided you have a database along the lines I've showed you it takes only seconds to create the table. Once you have the PT then you can drag and drop the field names around wherever you want and see the view of your data change instantaneously.

The only downside to them is that you don't have much control over the formatting. i.e. you can't include blank rows, colour cells differently, merge cells (which to most of us is a no no anyway) and one or two other restrictions. They are of most benefit to users who have responsibility for producing raw statistics and information. If you want to present the data in a more formalised free form wizzy layout, typically for senior managers then you would still use the database and have it populate your generic reports.

In the attached you can see I've added two weeks of data for two employees to the data sheet and used formulae in your CG Template sheet to feed the first 5 weeks. Note how when you click the Employee drop down in G4 then the data changes.

In a production system you may want to issue the employee reports to individual Employees in which case you'd have a macro cycle through all the employee names, change the G4 cell and save and eMail the resulting CG sheet to the employee.

Hope this gives you some more ideas.

[CHillFL]

I can see this working, but I didn't really need the full thing for the whole year because I can create other ways to get all that - I liked the 1 week report from your original, just needed to pull the whole month into it, somehow, with just a selection of the date range and the cells that don't have data entered for them would need to be blank, not filled with zeros....they are meant to add to the final number but not take away from it. I haven't worked with sumifs enough to be able to figure out that modification, although I'm sure with the right search criteria I could figure it out, unless you can send me to the best place to learn it. I'm mostly self taught and will pick things up very quickly.

We really like the graph too, so I can see the possibility of merging some of your original with what you did in my original. I don't need it to be colored and pretty with merged cells and all that, so it can be a Pivot Table, I just need to be able to have multiple weeks in it at one time and then add the formulas to get that final number. The version you saw was because there are others that are helping me with some data entry and they needed more visual assistance. I will work on where my brain is heading with this (merging it all) and will post it, probably next week, if you would be willing to look at it to see that I'm going the right way - I don's want to get too far and have it be a complete waste of time. Eventually too, some of the data points will be automated from the reports where it is gathered, so that will be less data entry for us (wishlist item), so the way you have suggested to set it up will be most helpful with that.

At the end of the processing, we were thinking it would be great to use a macro to cycle through all the employee names, but instead of emailing the results, printing them to a pdf. I will also need to figure out some report that will spit out all of my 95.00 to 99.99 and then all of my 100.00 + names and totals....so I do have work to do.

But like I said, if you'd be willing to take a look at my revisions next week.......For me this is called crash course into extreme-advanced Excel whether you are ready or not....guidance along the way would be appreciated.

Carol

[CHillFL]

Questions on the attached. I'm happy to do the work, I just need some direction - tutorial? solution that I can apply with an explanation?

Worksheet - Question(s)

CG Template
1.The formula below is for most of the cells K-O
3.In Green: K14-O14 - I did a simple If statement, but I need to account for a blank - a blank should return a blank, not a zero - will I still need to do this if the originating formula has the blank option already applied?
4.The yellow is a drop-down from the Variables page - see Variables notes

Variables
1.I haven't quite figured out how these tie exactly to the Data page, I'd rather not use the Employees Variable, I'd rather it just pulls from the data, so when a new employee pops up I don't have to add them. It ties back to CG Template tho (see yellow highlight) and I'm not sure how to change it
2.The rest of the variables I'm ok with...to a point....but I think I need to add one for the total hours (see Test Data) - which I did, but I had to insert a cell in the middle of the list, otherwise I couldn't get it to add at the end.

Data
1.I want to be able to add data quickly and easily - I created a "entry form" that I would love to use - if you have an idea how I can hit enter and it creates individual line items in the data list if K has data in it...is that even possible?
2.Otherwise, how do I enter new data? - I figured out it is pretty manual...I fixed the range so I don't have to continually correct it....

Test Data
1.This is how I get 4 of the major numbers I need . Is there a way to quickly and easily convert it to the format on the Data page? So that I can just copy and paste it in?

Test Report
1.This is just my playing with the pivot tables - that I ended up deleting so it's not relevant..


Thanks for ANY assistance you can offer.

[CHillFL]

Ok, a co-worker says that I am really pushing against the boundaries of Excel's capabilities with these requests and that I should probably go to an Access Database.....what do you all think? Can I do these things with Excel?

Thanks for your input.....Carol