Avatar
Board Regular
- Joined
- Sep 20, 2005
- Messages
- 193
Greetings all,
In Short:
indirect vs indirect.ext vs Please give me another option!
In Depth:
Here’s my problem, I have an excel application that is designed to measure the productivity of the staff within my department. The key aspects it needs to cope with are:
•14 teams each with up to 25 people per team (350 possible users)
•each user needed to have their own workbook that cannot be accessed by other users
•each users workbook has 1 tab per day that they have been using it
•each sheet has a maximum of 100 processes that they give how many they did and how long it took them
•there is one main workbook that the managers of each team use to retrieve the productivity scores for their team on an individual basis
•each manager can only access their own teams productivity.
•the managers workbook consists of 11 worksheets. These are; login, settings, daily, week1, week2, week3, week4, week5, week6, month, monthly review (between these sheets that are in the region of 10’000+ cells that have dynamic formula referencing up to 25 user workbooks)
okay.. the users sheets are fine and easy. The size gets a little large after 2 months (2-3Mb) but I’m working on an archiving function to take all the data over 2 months old and dump it in a database in case it's needed at a later date.
The manager’s sheet is where the problem occurs. I made the managers workbook as dynamic as possible so it didn't have to load all the data each time it opened, so it checks the user that’s logged onto the machine accessing the file and compares it to the list of authorised users, returns the team they are authorised to access, changes a predefined series of cells with the appropriate team, and process group (different teams do different processes), and unhides the sheets that display data (all except settings).
The problem is, to do this dynamically I had to use indirect or indirect.ext to change the location on the network drive to look for the teams productivity directory, and the cell formula in each of the cells that read from the user workbooks. I tried two ways of doing this:
One:
on opening the code checks the team and opens all .xls files in the desired team productivity folder and hides them all, then recalculates using indirect.
Two:
on opening the sheets changes 3 cells with the appropriate data such as team name and processes used etc and the cells then dynamically update with indirect.ext.
EDIT: Yikes! I started a full calculation at 13:14, and it's 19% done at 15:09!!
EDIT AGAIN: It finished at 15:38. - back to the drawing board..
Both of these take an age to update and calculate, the quicker being option one, but still takes about 5-10 minutes to open the desired files and recalculate. Can anyone think of a better way of doing this?
In Short:
indirect vs indirect.ext vs Please give me another option!
In Depth:
Here’s my problem, I have an excel application that is designed to measure the productivity of the staff within my department. The key aspects it needs to cope with are:
•14 teams each with up to 25 people per team (350 possible users)
•each user needed to have their own workbook that cannot be accessed by other users
•each users workbook has 1 tab per day that they have been using it
•each sheet has a maximum of 100 processes that they give how many they did and how long it took them
•there is one main workbook that the managers of each team use to retrieve the productivity scores for their team on an individual basis
•each manager can only access their own teams productivity.
•the managers workbook consists of 11 worksheets. These are; login, settings, daily, week1, week2, week3, week4, week5, week6, month, monthly review (between these sheets that are in the region of 10’000+ cells that have dynamic formula referencing up to 25 user workbooks)
okay.. the users sheets are fine and easy. The size gets a little large after 2 months (2-3Mb) but I’m working on an archiving function to take all the data over 2 months old and dump it in a database in case it's needed at a later date.
The manager’s sheet is where the problem occurs. I made the managers workbook as dynamic as possible so it didn't have to load all the data each time it opened, so it checks the user that’s logged onto the machine accessing the file and compares it to the list of authorised users, returns the team they are authorised to access, changes a predefined series of cells with the appropriate team, and process group (different teams do different processes), and unhides the sheets that display data (all except settings).
The problem is, to do this dynamically I had to use indirect or indirect.ext to change the location on the network drive to look for the teams productivity directory, and the cell formula in each of the cells that read from the user workbooks. I tried two ways of doing this:
One:
on opening the code checks the team and opens all .xls files in the desired team productivity folder and hides them all, then recalculates using indirect.
Two:
on opening the sheets changes 3 cells with the appropriate data such as team name and processes used etc and the cells then dynamically update with indirect.ext.
EDIT: Yikes! I started a full calculation at 13:14, and it's 19% done at 15:09!!
EDIT AGAIN: It finished at 15:38. - back to the drawing board..
Both of these take an age to update and calculate, the quicker being option one, but still takes about 5-10 minutes to open the desired files and recalculate. Can anyone think of a better way of doing this?
