Well, you know what the sheet is going to be called even before you create
it, so use that

sFormula = "=SUMPRODUCT(('[" & Me.SegSocNo & "]'!D2:D101=Cover!D11)*" &
_
"('[" & Me.SegSocNo & "]!D2:D101<=Cover*!D12)*" & _
"'[[" & Me.SegSocNo & "]!E2:E101)"
ws.Cells(iRow, 7).Formula = sFormula



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LoizaMIS" wrote in message
oups.com...
Thanks. I've simplified the code, but the formula is being added, not
to the new sheet, instead to an existing sheet that is like a table of
contents of all sheets. I forgot to add in the previous post where's
going the formula. Here's the

ws.Cells(iRow, 7).Formula =
SUMPRODUCT(('[SheetName]'!D2:D101=Cover!D11)*('[SheetName]!D2:D101<=Cover*!
D12)*'[SheetName]'!E2:E101)


ws = The table of contents sheet
SheetName = The new created sheet <-- This is where I'm confused...