Creating a log that populates a name in a cell if a specific number is in another cell

[QueenQ]

Hi all!

I'm racking my brain trying to figure out how to create what I thought was a simple spreadsheet for our company's contracted tow roster.

We have 4 towing companies. Each time a vehicle is towed, the next company on the roster gets to tow the next vehicle and so on until all 4 have towed a vehicle, then it starts all over again.

On the 2nd tab of my log, I've created a table listing each company numbered 1 through 4 from which tab 2 can pull to populate into column F.

I want the log to auto-populate in column F the name of the next tow company simply by the user typing a number 1 - 4 in column A.

What would be even greater is if the log could prevent back-to-back duplicate entries for a specific tow company to force the user to choose the next company in line. I hope this makes sense and is actually feasible.

Please, any help you can provide would be most appreciated!

Thanks for your time! Have an awesome day!

 

[skillilea]

Help me understand,

Couldn't you put a formula in column "A" that sequences 1-4 and then your sheet automatically populates the correct company name with a lookup?
Or am I not understanding?

 

[QueenQ]

Hi,

Thanks for your help! Yes, I presently have a formula in place that automatically generates a number in column A if a Case# is written in column B "=SEQUENCE(COUNTA(B$5:B$32))" but I'd like it if this formula could also only number 1-4 then repeat. I also have a VLookup in place that automatically inputs the company name in cell F when a number pops up in cell A. I'm guessing my formula can't do both match number to company and sequence only from 1-4? Sorry, I'm pretty new to Excel.

 

[skillilea]

If you want the sequence from 1-4 you can use this:
Just paste it into your first cell.

=MOD(ROW(A1)-1,4)+1