I haven't been able to get the normal worksheet functions to do what I
need, so I think I'm stuck making my own.

I have 7 sheets, each with identical tables. On an eight sheet, I have
another table (same format) in which I need to generate unweighted averages
of the relative cell from each source page. I would have done this with an
array formula, but cells without values in the source tables are =N/A (and
I need to keep them that way for graphing purposes), and apparently isna
and iserror don't work in array formulas across multiple pages.

So, in cell C3 of my roll-up table, I need to cycle through the values of
cells C3 on each source worksheet, and if the value is something other than
N/A, include that value in my unweighted average.

Where I'm stuck is how to reference the other sheets in the UDF: it stops
execution on the "choose" line, without giving any errors

Public Function Consolidated(SourceCell As Range) As Variant
Dim userSheet As Worksheet
UseRow = SourceCell.Row 'I'm the only one using this, so I know I'll
only have one cell selected
UseCol = SourceCell.Column

For i = 1 To 7
Set userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16",
"Sheet17", "Sheet18", "Sheet19")
'I also tried:
'ActivePull = Choose(i, Sheet9, Sheet12, Sheet15, Sheet16, Sheet17,
Sheet18, Sheet19) ' without quotes didn't work either
'Set userSheet = ActivePull

'do something here like :
'if userSheet.Range(UseCol & UseRow).value < NA then
' TotalValue = TotalValue + userSheet.Range(UseCol & UseRow).value
' DivCount = DivCount+1
'end if
' then divide TotalValue/DivCount to get the average

MsgBox userSheet.Range("A2").Value 'it never gets to this line
Next
End Function


Any and all help is welcome and appreciated-
Thanks,
Keith R
XL97