need your help to get the function to consolidate the data and create a automated report

Hi Team,

I have attached a file and I mentioned in that file what I needed. Please check and assist me on doing this. Please find the attached file and let us know if you need further details. Thanks.

Regards,
Girish

Hi Giri.hb,

Please be more descriptive in the comments that you have mentioned. Also please remove the data from the tables where you need formulas to be inserted. This will help us to get you desired result

Thank you, every thing is correct but one thing I need to change that you have mentioned 0% but need the actual percentage how much the employee scored whether it is 28% or 25% or 30% instead of 0%. so that i will get a total percentage on the summary of the day table and we can judge the best performer. I have attached your sheet kindly do the need full and also I am not able to see any value on the week summary table. Please advise. Thanks.

Regards
Girish

Sorry for the confusion,

what I need is the actual percentage scored by employee. If employee achieved the given target it should be 30% and if he not achieved it should show the percentage what he is scored. For example Mr.c he is not achieved the target he did 15 on task 1 and 11 on task 2 and his percentage is I assume it is 25%. Please let us know if you need further information. Thanks.

Regards,
Girish

Actually we have shared 30% for productivity and 70% for quality. When user achieved either one of task he will get 30% on productivity. For example if he achieved 40 or more than 40 he should get 30%. If not he done only 35 then his percentage will decrease. Hope this will help. Please advice and kindly do the needful.

Thank you it worked well..... every thing is correct. I need one more help from you if employee worked on 2 clients and achieved 10% on client 1 and achieved 20% on client 2. How to add both and update that value in summary of the day table, since you have used Vlookup formula to get the data on the summary table but it will take data from one cell but not from multiple cell of the same employee name. Please advise.

Hi SDCH,

Thanks for your help.... really it helped me well, but I need one more formula from you before I close this conversation.

If A is having below numbers, I need B column should be shown as only 10, if the number exceeds than 10, B column should shows as 10 and if the A column is 8 or 7 the B column should reflect the same value, the value should remains 10 only when A column is more than 10. Please let us know if you need further details. Thanks.

10
20
07
15
08
10


Regards,
Girish

Sorry SDCH,

As per your attached file, on the summary for the day table, you have added the SUMIF formula and some values are more than 30%, so I need some function which helps me that if employee achieved more than 30% that is MR A achieved 74% on B3 cell, in that case the value should change to 30% only and if the value is less than 30% than the same value will reflect on the cell for example B5 cell where Mr.c achieved only 22% and it should remains same. Please find the attached file for your reference.

Thanks.
Regards,
Girish

Hi SDCH really you are genius, it worked well you have helped me 100%... Thank you very much.... Finally I one more help to get the week summary using sumproduct(sum(indirect function as per your previous attachment I tried but unable to get the data. Please help me in brief an assist me on how to get the week summary. Thank u. I am attaching the spread sheet for your reference. THanks.

Hi try this

Formula:

Please Login or Register  to view this content.

in b3 and drag it down

=SUMPRODUCT(SUMIF(INDIRECT("'"&NameSheet&"'!A3:A8"),A3,INDIRECT("'"&NameSheet&"'!C3:C8"))) in c3
Punnam

Hi All,

thank you every thing worked fine, as you assisted me to calculate the total numbers in the week summary, can I know if their any formula to calculate the average instead of total numbers so that it will be helpful. For example if a person worked only for 3 days in a week the average should be calculate only for 3 days in the week summary sheet and in the mean time another employee worked on 4 days and his average should be calculate for 4 days. another thing is employee name will be not in sequence on all the sheets.

I tried using the above formula by replacing sumif with AVERAGEIF as below but the giving the error. please assist me at the earliest. Thanks.

=SUMPRODUCT(AVERAGEIF(INDIRECT("'"&NameSheet&"'!A3:A8"),A3,INDIRECT("'"&NameSheet&"'!B3:B8")))


Regards,
Girish

Hello all, Please some one assist me on the above query... Thanks in advance

Thank you very much....