Cell Adds One to Last Entry Used Depending On Selection

See attached workbook.

There is a drop-down list in column A on sheet one. It references the "Animal" table in column A on sheet two. When a person chooses an animal in column A on sheet one, I need column B on sheet one to reference the corresponding "Animal ID" table in column C on sheet two then find the last instance it was used on sheet one and add one to it.

Example:
If in cell A8 I choose "Flamingo", B8 will know to reference its "Animal ID" in column C on sheet two (in this case EAS), then find that "Flamingo" was selected two times already, and then add one to make the entry as EAS-3.

If the animal hasn't yet been selected (say yak), it would know to start it with TVA-1.

Any help would be greatly appreciated!

Unfortunately it doesn't work. The formula sometimes references a different ID, or it'll not update if the animal selection is changed in column A, or it won't add at all. I tried to tweak it to make it work, but it still gives errors. Can you assist?

Can this be applied to the entire column? I seem to have to pull down the formula in the column for it to work. Entries will be added over time, and people may not know how to do this to receive the animal ID.

Is there a way to use either data validation or some other feature that will do it automatically and not have the user copy it down the column? Not everyone knows how to use Excel, and one can easily mess it up.

Remember: People will be adding entries over time.

I tried data validation using the provided formula as Excel has an adaptive way to applying formulae to new entries, but it didn't work.

I am still having to pull down the formula per each animal entry. Unfortunately the macro doesn't work.