Round Robin Triggered by Button Click

[jorgegarcia27]

What I need: a cell that populates the next name from a list whenever a macro button is clicked. When the list is exhausted, it should restart with the first name. I'm essentially looking to route leads evenly by creating a Google Sheet that provides the lead router with the next name on the list. The reason it needs to be done by click is the sheet will be shared with multiple lead routers.

I used this video to show me how to create a round robin in Excel. I want to hide the round robin in a hidden tab and simply create a button that when clicked, will bring up the next name on the list. The round robin is triggered by I5 (the start number). I can't seem to figure out how to get a click to add 1 to that number with each click and then go back to 1 after I reach 5 as a start number.

Here's the file.

Does this make sense?

 

[MickG]

Try this Example:-
Data in column "A", result (each Click) in "B1"

[COLOR="Navy"]Sub[/COLOR] MG16Nov06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
Static n
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    n = n + 1
    n = IIf(n = Rng.Count + 1, 1, n)
    Range("B1") = Rng(n)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jorgegarcia27]

Try this Example:-
Data in column "A", result (each Click) in "B1"

[COLOR=Navy]Sub[/COLOR] MG16Nov06
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
Static n
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    n = n + 1
    n = IIf(n = Rng.Count + 1, 1, n)
    Range("B1") = Rng(n)
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

That didn't work. When I created a button and assigned that macro, it simply copied cell A1 into B1.

 

[MickG]

Try this Example:-
https://app.box.com/s/v68jk5wn5d4h5bqhjb6ddn2eoo3l6ako

 

[jorgegarcia27]

You are awesome!!!!

 

[jorgegarcia27]

I'm relatively new to this so I wasn't aware the VBA wouldn't work in the Google Sheets script editor. Is there an easy way to translate the code?

 

[MickG]

Possibly , but not something I'm able to do !!!