Please help, formulas work - then dont work im confused!!

[sleepers]

Hi have have code that works to concantonate a group of dates and it works as such

=concatmth('Group Formula'!$C5:$IR5,D$2)

Function ConcatMth(rng As Range, item As String, Optional dte = True)
    Application.Volatile
    For Each ce In rng
        If ce <> "" And Sheets(rng.Parent.Name).Cells(2, ce.Column) = item Then
            If dte Then
                holder = holder & Format(ce, "d") & ","
            Else
                holder = holder & ce & ""
            End If
        End If
    Next ce
    ConcatMth = Left(holder, Len(holder) - 1)
End Function

but when i got out of the excel sheet and back in the cells that were supposed to be formulated seem to turn back off - i can make them come back by tools/options/update sheet.. but as i want to be able to vlookup these outcomes it is not an option -

why are the dates disapearing??

 

[acw]

Hi

Do you have the calculation set to automatic?


Tony

 

[sleepers]

yes it calculates when you open the sheet ( after a little wait ) then if i click out of that sheet into another workbook, when i go back all the cell say #VALUE!

 

[acw]

Can you post a sample of the data you have in the relevant ranges...