I want to Match entries and code the value, then append incremental numbers.

Hi Excel type Gurus who I hope can help me with his query.

I am developing a spread sheet to record and name heritage sites, for upload through an oracle forms heritage management system.
The excel sheet will be secured to only allow people to enter relevant information in certain formats.
ie the easting and northings are effectively a bounding box around an area and places outside this area will generate an error.

I have placed in Data Validation and named ranges.

There are several effects I still want to see and cant quite figure out.
I want the "Site Code" column to select a coded value from "Coded Area" and the apply a ranged number that I have previously selected to it.
eg If I select that a consultant will have 001-199 and another consultant will have 201-299 both working in the same area "BELCONNEN, ARANDA (BE-ARA-)" I want to be able to lock that value on the Hidden sheet but as data is entered generate the Site name of BE-ARA-001 or for the other consultant BE-ARA-201 and incrementally increase the value.

I hope that makes sense.

Also is there a way I can clean up my named ranges a little bit to be more intuitive?

I have attached an unlocked version. Tab Hidden will be hidden! How I think it should look will change names depending on date of report. Users will only be able to alter A:3 to AH:200 with the exception of B:2 to B:200 where I want to generate the naming.

HERO UPLOAD Master V4 unlocked.xlsx

Any help would be great!

Cheers

I solved my own post! Using OFFSET, MATCH and CONCATENATE.
HERO UPLOAD Master V4 unlocked V3.xlsx

If anyone wants to see it here it is