sorting data with colors

[crazyamerican]

All I need u r help with this problem.
i have two sheets one that has data and the other that displays data based on the color on the data sheet.
data sheet

result sheet

now i want the color associated with number on the data sheet to populate the result sheet with the corresponding suffix.

now next month i have some more data and if i copy it into the data sheet and press refresh i should be able to populate the results sheet with the colors associated with the latest date.
any help would greatly be appreciated.
Thanks In advance

 

[GlennUK]

You'd have to use VBA to write a user-defined function (UDF) for this. So for this case:

Excel Workbook
	A	B	C	D
1	name	date	#1	#2
2	234-abs	1/1/2011	12	12
3	246-one	1/2/2011	32	1.2
4	345- hem	1/3/2011	12	13
5	432-iss	1/4/2011	64	74
6	234- iss	1/5/2011	15	61
7
8
9		results
10		name	abs	iss
11		234	24	76
Sheet1

you'd use this VBA ( in a normal module ):

Function mysumbycolour(strPrefix As String, _
                    strSuffix As String, _
                    rngCriteria As Range, _
                    rngSumArea As Range)
    Application.Volatile
    Set myrange = Application.Caller
    mycolour = myrange.Interior.ColorIndex
    runningtot = 0
    For Each c In rngCriteria
        If c.Interior.ColorIndex = mycolour Then
            If Left(c.Value, Len(strPrefix)) = strPrefix And _
                Right(c.Value, Len(strSuffix)) = strSuffix Then
                For Each c2 In rngSumArea
                    If c2.Row = c.Row Then
                        runningtot = runningtot + c2.Value
                    End If
                Next
            End If
        End If
    Next
    mysumbycolour = runningtot
            
End Function