Name a range of worksheets and find duplicate data in a specific column in each sheet

I have made up 7 worksheets that are completely identical with exception of course to the data they contain. I have a master sheet that contains some of the data from each of the other sheets that will be shared between them. Things were working very well until I discovered that some data was duplicated on a couple of the sheets; and I am in need of adding more worksheets.
The formula I am using for this will not allow me to have more than the 7 worksheets. It is an IF statement array. So now I am wondering how will I be able to add these extra sheets. I would like to use my worksheet names in a range but have evidently done something wrong and could not get that to work before, ending up with this very long 'IF'. !Can worksheet names be used to create a named range?
Also, I would like to know if there is a way to prevent duplicate data on these sheets. I am using conditional formatting on each sheet now, but that of course only works on the current sheet.

Any help that can be given is very greatfully appreciated. If any formulas or other information is needed from me, just ask. Right now I have no idea what I could provide that may be helpfull in this situation. Not sure if what I am trying to do is even possible!

Thanks in advance for any help received.

If your sheets are identical you could use e.g.

=sum(Sheet1:Sheet35!A1)

It will sum from sheet 1 till sheet 35 (so you don't have to use a if statement).


Other option would be:

Use a macro to get all data on 1 sheet; and after that make an pivot table of the data.

Elevation_Coordinate_Calculator_help.zip

Here is that file. I had to zip it since it was slightly too large for the system. Hope this helps.

Seems to be working fine. What do I do now to mark this as solved?