-2

I have attached the example file just in case my explanation is not clear. Excel Case 01.xlsx

So everyday I need to e-mail to my customers their daily delivery order based on their request and contract. Usually, they only order what they requested and leave out their required take of daily order. So, I usually mark the "forced" order using orange color.

Each file is based on each month. And each sheet is based on each day so I have July file with 01,02,03 etc as sheet name. Each sheet has company and products details and the amount kgs of their order.

Each day I have to email them the "forced" order only to notify them what's coming to them.

Now, I have separate sheet for e-mail like this: Excel Case 01 - E-mail.jpeg

My formula for cell D6:D10 is:

{=IFERROR(INDEX(INDIRECT("'"&$K$4&"'!$A$2:$H$6"),(MATCH(1,(INDIRECT("'"&$K$4&"'!$A$2:$A$6")=$B6)*(INDIRECT("'"&$K$4&"'!$B$2:$B$6")=$C6),0)),$K$7),0)}

The problem is that the orange color is not shown in that column and I need the color so I can use the filter by color option. I also want to keep the "Sheet Name" function so that I only have to change the sheet name to get the datas that I want since I will have up to 31 sheets.

Is there a way to keep the indirect formula to show not only values but also the format of the referred cell?

Thought of using paste link VBA but when I searched for the codes, it seems that I only find codes that refer to specific cells. But I don't mind using VBA if I can still use the "Sheet Name" function.

Thank you in advance.

Carissa
  • 7
  • 3

1 Answers1

0
  1. Add a VBA function called Function SumByColor(CellColor As Range, SumRange As Range, Condition1 As Range, Condition2 As Range, ColOffset As Integer) as shown below
  2. change the formula in D6 to =SumByColor($K$9,OFFSET(INDIRECT("'"&$K$4&"'!$A$2"),0,$K$7,6,1),B6,C6,$K$7) and copy down
  3. amend the lookup table on the RHS slightly (subract 1 from each value) to make everything tidier
  4. change the cell color of K9 to match your 'forced' orange

This worked in a modified version of your file I edited. 

Function SumByColor(CellColor As Range, SumRange As Range, Condition1 As Range, Condition2 As Range, ColOffset As Integer)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range

ICol = CellColor.Interior.ColorIndex

For Each TCell In SumRange
    If ICol = TCell.Interior.ColorIndex And TCell.Offset(0, -ColOffset).Value = Condition1.Value And TCell.Offset(0, -ColOffset + 1).Value = Condition2.Value Then
        SumByColor = SumByColor + TCell.Value
    End If
Next TCell
End Function

enter image description here

BTW: I just modified the SumByColor function I found somewhere else so I can't claim credit for its basic operation.

Tim Joy T-Square Consulting
  • 266
  • 2
  • 12