Originally Posted by
pete_uk
change the formula in n2 of the summary sheet to this:
=if(and(max(indirect("'"&m2&"'!a:a"))=0,sum(n1:n1)=0),0,max(indirect("'"&m2&"'!a:a")))
and copy down to n5. Also, i see that in the spalu sheet you have put the starting value for the sheet in cell a8, so put this formula in a7:
=max(hfmhcs!a:a)
and this one in a8:
=if(e8="submitted",max(a$7:a7)+1,"-")
that should now give you three records in the summary sheet, one from the bcpo sheet and two from the spalu sheet.
Hope this helps.
Pete
edit: Note that column n is giving a cumulative count of the records, not the records on each sheet that meet the criteria.
Note also that you will have to copy the row of formulae down from row 7 to row 57 on the summary sheet
pete
Bookmarks