PDA

View Full Version : Repeating hlookup addition within a cell



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!

meddlingkid
12-15-2007, 05:56 PM
Here's a generic attachment that illustrates my question a bit better. The Staff Availability worksheet is where I need to accumulate the hours. I have included the sample calculation for the first project, but again, am attempting to have each cell show all projects (I currently only have three projects, but will actually have 50 or so).

The project information is on the Pipeline worksheet.

Thanks again for your advice.

meddlingkid
12-15-2007, 08:59 PM
Nobody has any suggestions?

XLGibbs
12-16-2007, 08:18 PM
You want multiple results of multiple calculations to be simultaneously displayed in one cell?

Not only does that seem impractical, it seems overly cumbersome.

If you want this to work properly, and have much more flexibility you should rethink that table design.

Have as much of it in columns as possible. All individuals should be identified in row level info, not across columns as you have done. Same with dates....

The structure of that table doesn't lend itself to any efficient means of reporting, analysis, or use.