looking for macro to replace formula's

[nikoniko]

Hi All

I have been having a problem with the worksheet (attached) and was wondering if anyone could give me any help.

I will explain the purpose of the sheet etc first and what it does at the moment:

This work sheet is to record peoples projects, hours related to those projects, and calculate their availability 37.5-(project hours+pto) for Belfast and dublin and 40-(project hours+pto) for chicago, and also their utilization which is =((37.5-Availability)/37.5)*100 for belfast and Dublin and ((40-Availability)/40)*100 for Chicago.

When a project is added I am currently going in and updating the formula for that person to incorporate the new row which their project is on, this is proving to be quite time consuming, and I was wondering if there were any macro’s that could be used so that it would search for a name on the first column and use each of the hours that are in the column for the given week to calculate the availability?

The utilization is not a big problem as it just uses the availability so as long as the availability is correct then the utilization is correct..

If there is not a macro do you know of any way this can be automated so that I do not manually have to add to formula for each cell when a new row is added with a project and hours?

Any help would me much appreciated

Many thanks
N

[teylyn]

Hi,

please upload a workbook with a small data sample, illustrating
- data input (where and what)
- processing logic in plain words
- data output (where)

I honestly do not have the time or patience to try and understand your complex workbook, relationships between cells (including circular references) and working out where your problem might be.

If you want help, please consider those who want to help you and make it as easy as you can for us to understand your problem.

cheers

[nikoniko]

i thought it would be better to show the workbook to give you a picture of what i am doing, i will put something together, i was just looking for ideas, no need for the attitude!

[teylyn]

c'mon, dude, I'm on a data plan and I'm putting in my time and advice for free. If every sample workbook posted here was half a meg and I'd have to spend 90 minutes trying to figure out on my own where the input and output needs to be, according to a logic that is hardly explained, I would only be able to answer 5 questions a week. I'm averaging 12 a day, when I'm online.

[nikoniko]

sorry dude, spent ages on that one taking out any references to the company etc and thought it would be ok, im working on a simple one now, i'll get it together and hopefully it gives a clear picture of what i am trying to do..
thanks

[teylyn]

cheers. I'll look out for that!

I know how it is when you live and breathe a complicated spreadsheet. Sleeping any?

[nikoniko]

a bit, but my dreams are filled with cells and formula's

[nikoniko]

Hi Again

Here is a smaller workbook, but the same general idea is there.

When someone is added with a project I add a formula to the Utilisation and Availability areas, but if that person is added again, with another project I have to go in and update the formula’s to take this new project and hours into account.
e.g. Stefan Doran (all made up names btw) has two projects, for Availability I have formula’s in each column of his row that take into account these two projects (rows) i.e. =37.5-(H17+H19+H42)

Utilisation is ok, it just uses the value of availability =((35.5-Availability)/35.5 )* 100

now if stefan comes along than and adds a new row with a project and hours for himself I will have to go in and add this new row say =37.5-(H17+H19+H21+H43)

also if Stefan was to go in and remove one of his projects as it was cancelled I would have to go in and remove the reference to that row in the formula’s, for each cell/column.

Is there a way, by using macro’s, or something else, that it can be set up that the formula is based on searching the first row and calculating when the persons name is entered, so that the manual update of formula’s is not needed… I don’t know if it is even feasible as there are almost 100 people on the real sheet, with hundreds of rows and multiple projects/rows for each person…

Im really just asking is there a way to make my life easier as at the moment if (which happened yesterday) there are a range of projects cancelled, and removed from the sheet, I have to go in and update the formula’s in multiple cells for each person that had a reference to that project, it can work out very time consuming.

Any ideas, small or big would be much appreciated, even if it is to cut my workload down some.

Many Thanks
Neill

[royUK]

Not sure what you are xpecting, but Excel Functions are generally more efficient than VBA coding!

[nikoniko]

nobody else got any suggestions, even something that will make the manual enrty of the formula's easier... anything?????

[teylyn]

Hi Neill,

take a look at the attached.

I'm using SUMPRODUCT to add the hours per person.

This requires a few things in place, though:

1. a range name for the current project list (I called it ProjectList)
2. a range name for the PTO/Admin list (Admin)
3. a row above your titles, numbering the columns

The first range name I define dynamically, i.e. it will grow automatically when you insert a new row for a new project or delete projects. I do this by finding the word "Availability" in column B and using the row number for that row to calculate how many rows the range needs to have. Look at the definition of the range name ProjectList with Insert - Name - Define.
This is a a so-called dynamic range and will not show up in the GoTo/F5 dialog box, only in Insert - Name - Define.

The range name for Admin is not dynamic, since the data layout is a bit unfortunate in that it lists everything on one sheet. There's no way of telling where it will start and where it will end, at least not through OFFSET, COUNT or similar functions. So, whenever you add a new person to the list, you need to update the range name Admin to include all people in that list.

The row with the column numbers at the top can be hidden if you want. I need it in the formulas to figure out how many columns to the right of ProjectList and Admin I need to look for the data to add.

The formula in the Availability section looks like this:

=37.5-(SUMPRODUCT((ProjectList=$A24)*(OFFSET(ProjectList,0,H$1-1)))+SUMPRODUCT((Admin=$A24)*(OFFSET(Admin,0,H$1-1))))

The first SUMPRODUCT looks for the name in column A in the ProjectList range and the values in the column xx columns to the right of the ProjectList range. The second SUMPRODUCT looks for the name in column A in the Admin range and the valuse in the column xx columns to the right of the Admin range (where xx is the number in row 1 of that column)

So, that's a possible solution without macros, without the need to change the formulas when projects are added and deleted, and only requires updating a range name when people are added or deleted from the staff list.

Will that work for you?

[nikoniko]

Thank You So Much!!!

that is a serious bit of great work...

so, if someone is added, the only range that needs updated is Admin, if everything else is in place?

i will get to work, the actual sheet is huge but it is the same principle as here, and will hopefully cut down my admin of the sheet a great deal!

leaving some extra time for the pub!!

you have been a great help

Neill

[teylyn]

leaving some extra time for the pub!!

Good on ya! Have a Guinness on me!