I have a 7 sheet excel national d/base with the 4th sheet working out all the stats and therein lies the problem. Lots of links have been broken. Is there a person I can send it to?

I am in the process of removing the links in the individual records which are in 2019-23 period which coincides when the secretary who invented this system retired on 31Dec2018.

You say the formula for E3 is wrong but don't suggest a correction or how to do it, I am but a novice in Excel, so would appreciate some help in this aspect.

In Name Manager all sheets are in twice because they are pulling data from "The Records" and "pre1950" sheets is how I understand it.

Under "The Records" and "pre1950" sheets I have edited the last cells from 49801 to 54709 in all the lines in Name Manager but that just puts errors everywhere in the "Species & Stats" sheet. Is there another way like selecting all the records on these two sheets and selecting whatever button on the ribbon to correct this?

Keith Naylor

Archived Database-BBRC ( fix 2).xlsm

Please contact again if this is OK, thanks

Worked, thank you. Your file contains links to 4 other files:

Q:\BBRC Archive\The Records-BBRC .xlsm
D:\BBRC Secretary files\The Records-BBRC.xlsb.xlsm
D:\BBRC Secretary files\The Species-BBRC.XLSX
D:\BBRC Secretary files\The Counties-BBRC.xlsx

I suspect the broken links are due to this files, maybe you moved them to an other place?

Anyway, open each file, then open the file "Archived Database-BBRC ( fix 2).xlsm" and the links should be re-connect.

After that a lot of the errors should be gone, but not all. For example in sheet "pre 1950" in column X are errors, no formulas or links behind that cells.

A short look into "Counties & 1-sp stats" the formula
E3: {=SUM(IF((BTOCode=Specific_Species)*(Progress="OK")*(CountyCode=$A3)*(Year>=1950),Count))}

is wrong due to the names, resp. the referred cells.

If you open the name manager you'll see a lot of names with errors.

The name BTOCode appears twice, the global name is used in this case and refers to
='The Records'!$F$2:$F$49801

If I look into the sheet "The Records" I can see that you have F54709 as last cell, not F49801.

I would discard all your evaluation sheets and just keep the data sheets.

These would need to be revised, e.g. in "Species & Stats" you have already formatted the data as a table. This is good because if formulas refer to the column of a table, then formulas in other sheets are automatically expanded and the problem with names that do not refer to all the data does not occur.

Furthermore, there are much better analysis methods today, and your pivoted data can also be converted to data tables, which enables formulas or analysis with pivot tables.

Either way, you have a lot of work ahead of you. Wait for what Jeovany says about your file.

Andreas.

Archived Database-BBRC ( fix 2).xlsm

Please contact again if this is OK, thanks

https://1drv.ms/f/c/06a9fc9ccaf1816b/EuszkzohAipPr2Bd3M5CpO8B6XQRG-eFOe6FNpbZEZX8Zg?e=WxH4bE

Okay, that link works.

First of all, I cleaned up this thread and deleted all the duplicate post with links to that folder. There is no need to post a message more then once, all post can be read by everyone.

All that links works, they refer to the same folder named "Jeovany" on your OneDrive and it contains 2 files:

The Excel file is missing.

Copy the Excel file into that folder and give us a sign.

Andreas.