• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Formula needed

Dhamo

New Member
Hi,


Consider in a workbook, I have 3 or more sheets in date format (Jan-05-2012,Jan-06-2012,Jan-07-201 and ..)along with a summary sheet. And in every date sheet, i have two or more names (name 1, name 3 and ...). And each name has one value in a day sheet.


Day1 sheet eg:

Names -- Tasks completed -- Tasks Planned

Name 1 -- 4 -- 10

Day2 sheet eg:

Name 1 -- 16 -- 10

Day3 sheet eg:

Name 1 -- 8 -- 10

Day4 sheet eg:

Name 1 -- 12 -- 10


Summary sheet should calculate sum of all other sheets 'Tasks Planned' values for name 1 to name n.


Condition 1: if today is day 1 then the task completed should be '5' and status in summary sheet Red(TC 2/10 TP) and over all status Red. see below


Output should be


Names -- Tasks completed -- status - overall status

day 1

Name 1 -- 4 -- Red -- Red

day 2

Name 1 -- 16 -- Green -- Green

day 3

Name 1 -- 8 -- Amber - amber

day 4

Name 1 -- 12 -- Green - Green


PS: Since the sheet names and names are not consistent. so better to use formula to solve.
 
Hi Dhamo,


Please see this file, you need to update the two newly added sheets by dragging formulas:


http://dl.dropbox.com/u/60644346/Sample.xlsx


Regards,
 
Thanks Faseeh. But I want to have the formula always. And when names are added It should populate related values. Is that Possible? I can write sheet dates in certain columns in order to write the formula. But names are not consistent. It may be 2, 3 4, or etc. Can u help me on this?
 
Hi Dhamo,


I have some query..


* On the 1st day how can you know.. total number of "Total Task Planned" For (1st + 2nd + 3rd Day)


* The sheets name need to used somewhere.. or they are just random..

For example.. If in Summary Sheet A1. you type 6th Jan.. then summary will show upto 6th Jan only.. or Sumamry sheet always consolidated for all sheet.. irrespective of Name..


I guess.. Faseeh Bhaijaan, will give some better technique..


For the time being please find the attached..

https://dl.dropbox.com/u/78831150/Excel/Excel%20Formula%20needed%20%28Dhamo%29.xlsb


__

[pre]
Code:
Private Sub Worksheet_Activate()
Dim sh As Worksheet, last As Long
Set dhsh = ActiveSheet
dhsh.Rows("3:200").Clear
[a1] = Format(Now(), "mmm-dd-yyyy")
Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Dhamo Summary" Then
With sh
last = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To last
If WorksheetFunction.CountIf(dhsh.[A:A], sh.Range("A" & i)) = 0 Then
With dhsh.Range("A1").End(xlDown)
.Offset(1, 0) = sh.Range("A" & i)
.Offset(1, 2) = sh.Range("B" & i)
.Offset(1, 3) = sh.Range("c" & i)
.Offset(1, 4) = Format(.Offset(1, 3) / .Offset(1, 2), "00.00%")
End With
Else
Set fndcell = dhsh.Columns(1).Find(What:=sh.Range("A" & i), LookIn:=xlValues)
With fndcell
.Offset(0, 2) = .Offset(0, 2) + sh.Range("B" & i)
.Offset(0, 3) = .Offset(0, 3) + sh.Range("c" & i)
.Offset(0, 4) = Format(.Offset(0, 3) / .Offset(0, 2), "00.00%")
End With
End If
Next i
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub
[/pre]

Regards,

Deb
 
Hi Debraj Roy,


1. On the starting day itself, we will get to know the number of planned tasks for all working days.

2. Sumamry sheet always consolidated for all (Date format)sheets. irrespective of Name..


Faseeh,

Thanks for your formula, it is working for me after a slight modification. :)


Thanks guys.!!
 
Ofc yes Deb, when I create the summary sheet by macro, I have added code to write specific(date frmt) sheet names in a row(d11,e11,f11..). and then the INDIRECT formula with SUM is working. And for data bars I used If condition, so it is also working fine. Soon I will upload a file and get you the link. If the logic I used is lengthy and if you have another logic then you can suggest. :)
 
Dhamoo,


Thank you and explain the problem correctly to Deb he can certainly make this process much simpler for you. sorry for keep you waiting :)


Regards,
 
Back
Top