Please help work out why this formula turns off

[sleepers]

Hi, this is in worksheet and works okay until i click out of the worksheet into another one, when i go back in the formulas have all gone to #value.. i have to go into tools/options/calculate/calculate sheet to get them back - what is doing this please because it stops all my lookup references fromworking once the data is decalulated (for want of a better word )

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

the code that i use in the cells is

=concatmth('Group Formula'!$C6:$IR6,D$2)

 

[Derek]

Hi there

If you can't find out why (and I don't know) perhaps you can use an event macro on that sheet that tells it to calculate every time the sheet is activated or deactivated?

regards
Derek

 

[Tazguy37]

Can you describe what you're actually trying to do with the code? As I'm seeing it, it looks like there's some missing, or you're using two different names for your function.

 

[sleepers]

i have rows with names and columns with dates, what i am trying to do is auto concantenate all columns within a range - say friday- so i will list all fridays showing available

its a avails list for musicians

i have one of these for days and one for months

sorry i put the code for one and the other together -

but they are both in system and do same error

 

[Tazguy37]

You could just filter the rows (or columns) so that only the days (or months) you choose will be visible.

 

[sleepers]

it has a cell for each day in each row for each artist..

i would be very hard to hide particular rows and i want to be able to just type in an artist name and it will show all dates avail for the desired month or all friday avail..

this formula does excatly what i want but i just looses the info some how

 

[sleepers]

how do i make it so that when a person opens or activates a worksheet - two sheets are automatically calculated so the data will always formulate

 

[acw]

Hi

What version of Excel are you using???


Tony

 

[sleepers]

excel 2000 and i need to share the file so i has to work shared

 

[Tazguy37]

You could use something like this, in that sheet's code module:

Private Sub Worksheet_Activate()
Dim Sh As Worksheet

    For Each Sh In Sheets(Array("Sheet1", "Sheet2"))
        Sh.Calculate
    Next

End Sub

HTH