Reoccuring table problem

[clint6998]

So here is my dilemma. I have one file that has a list of employees and their position. I have a second file for their performance review. the way it is set up now is I have 10 different tabs (different roles get asked different questions) and I have to manually add or remove pages from each tab depending on role changes.

Example: 1st qtr we had 3 assistant managers but 2nd qtr we now have 5. I have to add 2 more pages (not tabs) of tables for those 2 new assistant managers and delete the pages where those two employee roles were previously.

What I would like to do is still have those 10 tabs, but have it automatically add or remove pages based on the number of people in a specific position.

Same example as above but instead of me having to add and delete the 2 pages due to role change, I would like for it to do it automatically.

any ideas?

Thanks,

Clint

[ChemistB]

Okay, so by pages on a tab, each assistant Manager (for example) would get a page of questions which is identical to every other assistant managers page of questions? Or, put another way, each page is a table which contains that specific assistant manager's answers to a list of assistant manager questions?

[clint6998]

Correct on the first part. When I say pages, I mean what will fit on one page when printed as set by 8.5x11 giving multiple pages on one tab.

All Asst Managers have the same questions. All regional managers have a set of questions and so on.

Each tab would then have its own position (ie regional manager, assistant manager, etc).

[Ace_XL]

Why do you need identical multiple pages on a single tab? If all you need it for is printing, then simply print multiple copies of the single page

[clint6998]

I need multiple pages because each page has the employee name on it, and the cells next to each questions are interactive rating the employee from 0%-200% and then totals at the bottom of each page.

so we create, print, then go back and fill in based on the review to get their score.

[ChemistB]

Okay, this is probably best done with VBA but since my VBA is intermediate at best, I'll give you a Formula soluition.

Create a Management Page which contains a list of current management and positions and a table which tabulates those positions (D1:E3 in my workbook). I gave this table a defined name of MgmtCount.

On each of the tabs specific to positions, set up your first pages as you would like them.
Select all and right click to format cells and set to custom General;General;;General, OKAY (This makes 0's invisible because your formulas will return 0 when nothing is in the reference cell.
Set VIEW>Workbook Views to Page Layout so you can see exactly how many rows fit on a page. For example, let's say it's 34.

In A35, (for example),
=IF(VLOOKUP($A$1,MgmtCount,2,FALSE)>INT((ROW()-1)/34), INDEX(A$1:A$18, MOD((ROW()-1),34)+1),"")
Copy that across to fill the page and then down a thousand rows or so (or further if you think you'll need it)

As you modify Management!A:B, the MgmtCount table will update and the individual tabs will compensate.
see attachment.

[clint6998]

That should work perfectly! I can format it the way I need and just copy the formula. Thank you!

[clint6998]

I have another question though.. How do I get the employees name to replace the position on each page?

[ChemistB]

Can you upload an example with the "page" set up the way you'd like it? (Go Advanced>Manage Attachments)

[clint6998]

See attached

scorecard test.xlsx

[clint6998]

Any more ideas? Maybe pull data from an access db?