-1

Is it possible that each time a new row is added, the value of a cell is automatically filled based on a sequence that repeats indefinitely?

What I need to do is fill in the Sales Rep name in a column (there are only 6 sales reps in the list) every time a new cell is added.

Here is the spreadsheet I'm working with (the expected result is on "Leads Desired Outcome" and the formula I tried is on "Failed test".

https://docs.google.com/spreadsheets/d/1wvA7t7-S5agwDNEy_N5eIe58YPT1-9vZY8uWwB4sUZQ/edit?usp=sharing

Any help is appreciated!

player0
  • 124,011
  • 12
  • 67
  • 124
fvdev
  • 35
  • 8
  • its possible. share a copy of your sheet with example of desired result – player0 Oct 05 '21 at 15:04
  • Use arrayformula as the header of column with the appropriate formula – Mike Steelson Oct 05 '21 at 16:23
  • Thanks @player0, this is the spreadsheet with an example: https://docs.google.com/spreadsheets/d/1wvA7t7-S5agwDNEy_N5eIe58YPT1-9vZY8uWwB4sUZQ/edit?usp=sharing I need to fill the column "Sales Rep" of the sheet "Leads without Sales Rep" with the name of a sales rep, based on a sequence of 6 people automatically when adding a new row. I tried something, on the sheet called "Failed test". The extra column "SalesCount" doesn't work because I don't know how to automate the value of that column every time a new row is added, which is why I'm stuck. Any help is welcome! – fvdev Oct 05 '21 at 20:06

1 Answers1

1

use in E1:

={"Sales Rep"; INDEX(VLOOKUP(ARRAY_CONSTRAIN(FLATTEN(
 SEQUENCE(ROUNDUP(ROWS(A:A)/MAX('Sales Reps'!A:A)), MAX('Sales Reps'!A:A))-(
 SEQUENCE(ROUNDUP(ROWS(A:A)/MAX('Sales Reps'!A:A)), 1, 0)*MAX('Sales Reps'!A:A))), 
 ROWS(A:A)-1, 1), 'Sales Reps'!A:B, 2, 0))}

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124