Pending Task Tracker in Google Sheets

google sheets

Can someone help me fix this sheet(ctto.), It works well when the orders numbers are less than 100+/- but when the pending order numbers are too many it's giving me an error and it messes up the sheet.

Formulas:

(Column B, Sheet 2) =INDEX(IF(Sheet1!B2:B<>"",,Sheet1!A2:A)) linked to Sheet 1 for manual adding of (Date) to be auto remove to Sheet 2

(Column C, Sheet 2) =transpose(split(rept(join("|",D3:D)&"|",ceiling(count(A3:A)/COUNTA(D3:D))),"|",1,1))

  • which messes everything, Admin available vs. Pending Orders

(Column D, Sheet 2) manual edit depending on staff/admin available, will affect Column C data.

(Column E2, Sheet 2) =IFERROR(transpose(unique(C3:C))) to show admin names depends on Column D

(Column E-H, Sheet 2) =ifna(filter($B$3:$B,$C$3:$C=E2)) divide order numbers equally per staff

Feel free to edit/check this sheet as reference. Thank you!

https://docs.google.com/spreadsheets/d/17rYP9hEYiVbg_d-ZnSvf8zp-ZToONyujjtoedZpP7js/edit?usp=sharing

Best Answer

Use vlookup() and mod(), like this:

=arrayformula( 
  iferror( 
    vlookup( 
      mod(row(B3:B), counta(D3:D)), 
      { mod(row(B3:B), counta(D3:D)), D3:D }, 
      2, 
      false 
    ) 
  ) 
)

See your sample spreadsheet.

Related Topic