Dynamic formula in excel?

[Therrace]

Hi,

I am working on a report which divides total income down to each consultant.
In a sum up page I sum up the total amount of income on different projects gathered from different pages in an Excel document. In addition, I want to add under each consultant a formula which fetch project number, project name, income of the project and total amount of hours spent on that project. Normally one could use a simple VLOOKUP-formula, but in this case the project numbers differ from each period. As do the name of the projects.

So - I was wondering if there is any formula or macros that could gather the correct project number and update the number of rows every time I change the period in the control cell? Meaning, if I enter 201508 (August 2015) in cell A1, the rows under each consultant should update with the projects where that specific consultant have worked in that specific time period. There should be one row for each Project.

Is there someone of you that have any suggestions to how I could solve this problem?

 

[Mackers]

Hi

The easiest way would be to create a summary sheet containing the consultants on one axis and every project on the other, then perform a SUMIFS() based on the project name, consultant name and date range provided. This is not quite what you asked for, as the table will not change size to remove projects that were not worked on, but I think it should be presentable and very readable. You could definitely make one that adjusted on the fly, for example using a pivot table with a macro, but I think this solution is actually preferable in some ways.

=SUMIFS(HoursOrIncome, Names, RequiredName, Projects, RequiredProject, Dates, "<="&DATE(LEFT(A1,4),RIGHT(A1,2)+1,0), Dates, ">"&DATE(LEFT(A1,4),RIGHT(A1,2),0))

Note I have cut up your date to turn it into a more workable format (DD/MM/YYYY of the end of the month before and during, rather than YYYYMM).

Hope that helps

Mackers

 

[Therrace]

I see that this could work. However, the problem is that the total amount of projects exceeds one thousand. So when having all of the projects on one axis, you'll get a whole lot of columns. I think maybe a macro is the best solution, I just don't know how to execute it.

The wanted end-layout is something like this:


Period (201501)
Department
Consultant 1
Project number 1 Project Name 1 Income Project 1 Hours Spend
Project number 2 Project Name 2 Income Project 2 Hours Spend
Project number 3 Project Name 3 Income Project 3 Hours Spend

Consultant 2
Project number 1 Project Name 1 Income Project 1 Hours Spend
Project number 2 Project Name 2 Income Project 2 Hours Spend
Project number 3 Project Name 3 Income Project 3 Hours Spend

And the data-sheet have column names like this:

Periode - Tjenestetyp- Total inntekt - Total timer - Timepris - Inntekt konsult - Avdelning - User - Prosjekt - Hours

"Periode" refers to correct time periode. "
"Inntekt konsult" refers to the total amount of Income of that consultant on that project in a certain time period.
"User" is the user code of each consultant.
"Prosjekt" refers to the Project number.
"Hours" refers to the amount of hours spend on each project in that time period.

Hope that this explained more thoroughly what I am after.

Hi

The easiest way would be to create a summary sheet containing the consultants on one axis and every project on the other, then perform a SUMIFS() based on the project name, consultant name and date range provided. This is not quite what you asked for, as the table will not change size to remove projects that were not worked on, but I think it should be presentable and very readable. You could definitely make one that adjusted on the fly, for example using a pivot table with a macro, but I think this solution is actually preferable in some ways.

=SUMIFS(HoursOrIncome, Names, RequiredName, Projects, RequiredProject, Dates, "<="&DATE(LEFT(A1,4),RIGHT(A1,2)+1,0), Dates, ">"&DATE(LEFT(A1,4),RIGHT(A1,2),0))

Note I have cut up your date to turn it into a more workable format (DD/MM/YYYY of the end of the month before and during, rather than YYYYMM).

Hope that helps

Mackers