thanks for reading, but don't spend a lot of time on this. just wondering
which formula you'd use.
had to recreate some formulas to take into consideration the user entering a
space or lower case character. as an exercise, i did it 2 different ways. the
2nd way was faster, but the first way is a lot easier to decipher when you look
at the formula. probably never need the code again.

a little background
arrRow contains 5 elements, starting row numbers for each table, 4,
37,70,103,136. columns b-m are used.
the first one uses a loop for each column and row element, then autofills. the
2nd one just writes the entire range for each row element. B3 in this instance
contains Apr. both code examples return the same result from the formula.

first formula and code to create it:
=IF(UPPER(TRIM(Apr!$P3))="M",Apr!G3,0)

For r = LBound(arrRow) To UBound(arrRow)
For c = 2 To 13
With .Cells(arrRow(r), c)
.Formula = "=if(Upper(trim(" & Cells(3, c) _
& "!$P3))=" & arr(z) & "," & Cells(3, c)
& _
"!G3,0)"
End With
Next
.Range(.Cells(arrRow(z), 2), .Cells(arrRow(z), _
13)).AutoFill Destination:=.Range(.Cells(arrRow(z),
2), _
.Cells(arrRow(z) + 30, 13))
z = z + 1
Next
================================================== ===================
2nd formula and code that creates it:
=IF(UPPER(TRIM(INDIRECT(B$3&"!$P"& ROW()-1)))="M",INDIRECT(B$3&"!G"& ROW()-1),0)

For r = LBound(arrRow) To UBound(arrRow)
With .Range("B" & arrRow(r) & ":M" & _
arrRow(r)).Resize(31)
.Formula = "=IF(Upper(Trim(INDIRECT(B$3&" & _
"""!$P""" & "& row()" & -1 - z & ")))=" &
arr(c) _
& ",INDIRECT(B$3&" & """!G""" & "& ROW()"
& -1 - _
z & "),0)"
z = z + 33
c = c + 1
End With
Next

--


Gary