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?

Hi Keith

Please,

Find the link below, a copy of your workbook with the amended formulas as requested.

https://we.tl/t-EmJVBXyAfj

NOTES:

To find a solution and correct the formulas we did the following:

1. On the Records tab, we converted the table to an Excel table (named "RecordsTb") to be used in the formulas as dynamic table/range reference, allowing you to add more records without having to adjust the formulas on the Stats sheets.

2. On the "...Stats" sheet, a new set of formulas were entered

IMPORTANT NOTE:

3. I added a new sheet to show you the list of the unique entries/categories entered in the Progress column of the Records table, (142 in total,... it is a lot!!!!)

Please, notice that the data entered in this column is very inconsistent, with many typos as shown on the cells I grouped by color in the picture below. It also has many ways to refer to one category, for example, the "OK...ish"

All these records are not accounted for in the Stats sheet due to the inconsistency or misspelling in the data.

My suggestion is:

a) To add a column for additional "Remarks/Comments"

b) Re-categorize and reduce the list of values for the Progress column.

c) Use Data Validation dropdown list in the Progress column to avoid data entry errors.

Please, take a look at the file and let us know if you need further help.

Regards

Jeovany

Hi Keith,

About the names, you think you can create "AName" with the scope of a sheet and a name "AName" with the scope Workbook and have a formula =SUM(AName) and this formula sum up all data from all names "AName".

That is completely wrong. In your case the formula is in an other sheet there ONLY the global name (with the scope Workbook) is used.

BTW, if the formula is used inside the sheet where the locale name (with scope of the sheet) ONLY the locale is used.

There is no way to combine all the data from all the names using a formula.

About the dates, it is normal that you can not have dates before 1/1/1900 in Excel. Therefore all "dates" before are a text and can not be used.

Once again, I would discard all your evaluation sheets and just keep the data sheets. In more detail I would keep "Pre 1950" and "Species & Stats", clean up this sheets, remove any calculation and just keep the year of each observation.

Then I would use Power Query to combine all data and create a new Stat sheets on this basis.

Andreas.

Hi Andreas

in the "pre1950 sheet" I have found that when you select the date columns, then select Clear, Clear Formats off the ribbon it displays some rows in date form and some in text!

How do you correct this please?

Keith

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.