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 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

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 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 Jeovany

Many thanks for achieving this, much appreciated.

In the "Counties & 1-sp stats" sheet column D should be recording one in D4 (see record 67212 in "pre 1950" sheet.

Columns K & L headers should alter each year, eg. 1950-2023 (K) and 2024 (L); is this done manually? Also the other headers would all need to be the same period as in 1950-2023 currently - would this data keep changing as the length of the period extends?

In Cell 094 is it OK to change this header 1950-2014 each progressive year manually to be current.

Another question is the question of adding possibly 4/5 extra counties (dual counties like row 14, 18, 40, 45, etc). How easy would it be and does it upset the calculations?

Regards

Keith

Hi Jeovany

Would you be so kind as to format the Species & Stats sheet in Orange Medium 7 for me please as I have had great difficulty in doing it. You will find the little chevron in cell CH434 that I can't seem to get rid off.

Regards

Keith