meddlingkid
12-15-2007, 05:44 PM
I currently have a worksheet with months accross the top row, and names down the left-most column. Therefore,for each cell, I am trying to accumulate a person's hours for a given month. This involves using an HLookup to find a percentage of a project's hours a person is working for a given month. For instance, in each cell, I would lookup "Bob Smith" and return the percentage of hours assigned to him for that particular month for a project. So if it returned 25%, I would multiply that by the 100 total hours for that project during the month, coming up with a value of 25. Unfortunately, there are going to be about 50 projects. Therefore, I would need to run 50 different calculations for each cell's value.
I had thought this was a perfect opportunity to re-learn VBA, since it seems like something that could be automated. It seems I would run a calc, then move down to the next project and run the same calc, then move down to the next proejct, etc. until I reach the end of the project list. However, I'm having difficulty wrapping my head around how to do this all for one cell's value.
Then, once that's done, I will have to jump around to other people and other months until everything's done. The cheater way to do this would have been to just paste a bunch of hlookup calcs into each cell, but the character limit prevents that.
Any ideas on how to best approach this?
Thanks in advance!
I had thought this was a perfect opportunity to re-learn VBA, since it seems like something that could be automated. It seems I would run a calc, then move down to the next project and run the same calc, then move down to the next proejct, etc. until I reach the end of the project list. However, I'm having difficulty wrapping my head around how to do this all for one cell's value.
Then, once that's done, I will have to jump around to other people and other months until everything's done. The cheater way to do this would have been to just paste a bunch of hlookup calcs into each cell, but the character limit prevents that.
Any ideas on how to best approach this?
Thanks in advance!