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?

="1950-"&YEAR(TODAY())

Can that be amended to read currently 1950-2023 please, then it adds the year in the new year.

3. As you did The Records sheet in pale blue alternate rows, would it be possible to use the Format Orange medium 7 to give alternate coloured rows. But their is a problem in that the dragging handle is in cell CH434 in the Species & Stats sheet and I can't get rid of it to start again. "> Hi Jeovany
4. Record 67212 is in the pre 1950 sheet (2nd one)

and that is where column D should get its data from.

2. ="1950-"&YEAR(TODAY())

Can that be amended to read currently 1950-2023 please, then it adds the year in the new year.

3. As you did The Records sheet in pale blue alternate rows, would it be possible to use the Format Orange medium 7 to give alternate coloured rows. But their is a problem in that the dragging handle is in cell CH434 in the Species & Stats sheet and I can't get rid of it to start again.

Hi Keith

1. Sorted: the formula has been corrected to

=COUNTIFS(pre1950tb[BTO],Specific_Species,pre1950tb[Progress],"OK",pre1950tb[CountyCode],$A4,pre1950tb[Year],"<1950")

2. Sorted: the formula has been corrected to ="1950-"&YEAR(TODAY())-2

It means that in 2026 the formula will return 1950-2024,

in 2027 will return 1950-2025,

In 2028, it will return from 1950-2026, and so on.

I also corrected other formulas to change calculations automatically each year

3. That is correct, as it shows/marks the table boundaries, it will automatically move/expand when you enter new data

What do you mean by "...I can't get rid of it to start again."?

You may download the amended file from this link https://we.tl/t-taVU5JPqlw

4. Re,

"I have been in our local book shop Waterstones, to buy a book on Excel, but they had a poor selection.

I don't know a thing about Power Query! Is it easy to use and set up?

Well...I believe you, as everything is online nowadays, I suggest you check YouTube video tutorials for quick learning

a) Color formatting tables

(Re, "...would it be possible to use the Format Orange medium 7 to give alternate coloured rows.")

https://youtu.be/yLWJFjRk9zM

https://www.youtube.com/watch?v=PiHO5TzHjrk&pp=ygUZY29sb3IgZm9ybWF0IHRhYmxlcyBleGNlbA%3D%3D

https://www.youtube.com/watch?v=f7e0NqPbPl0&pp=ygUZY29sb3IgZm9ybWF0IHRhYmxlcyBleGNlbA%3D%3D

b) Power Query

UDEMY's Full Courses https://www.udemy.com/course/excel-master-class-power-query-consume-and-transform-data/?utm_source=adwords&utm_medium=udemyads&utm_campaign=Search_DSA_Beta_Prof_la.EN_cc.ROW-English&campaigntype=Search&portfolio=ROW-English&language=EN&product=Course&test=&audience=DSA&topic=&priority=Beta&utm_content=deal4584&utm_term=.ag_162511579644.ad_696197165439.kw.de_c.dm_.pl_.ti_dsa-1677053898368.li_1007880.pd_._&matchtype=&gad_source=2&gclid=Cj0KCQjw4cS-BhDGARIsABg4_J1t7_zBQlRyttqRvuyTjC9dahA2JceQDQXv1EgSra_kuDqTPSIsVwoaAtSDEALw_wcB&couponCode=PMNVD30A

Youtube

https://www.youtube.com/watch?v=6lBqYInBldk&t=425s&pp=ygUbaW50cm9kdWN0aW9uIHRvIHBvd2VyIHF1ZXJ5

https://www.youtube.com/watch?v=NM36N6COMuQ&pp=ygUbaW50cm9kdWN0aW9uIHRvIHBvd2VyIHF1ZXJ5

https://www.youtube.com/watch?v=L4BuUzccLpo&t=343s&pp=ygUbaW50cm9kdWN0aW9uIHRvIHBvd2VyIHF1ZXJ5

Hi Jeovany & Andreas

Absolute brilliant job! Many thanks again.

A few queries I would like you to answer for my peace of mind,

1). Is your added sheet "To be Revised" now permanently fixed in the database?

2). Would it be alright to copy records from another source that are more up to date and paste in "The Records" sheet and the "pre 1950 sheet"?

Will it impact on the stats page?

3). In the Age/Sex columns (M-O) does it use the count column G in "The Records" to produce the totals in the "Counties & 1-sp stats" sheet? If so I cane safely delete the numerals in column J of "The Records" sheet.

4). Is the "Species & Stats" sheet have to be updated manually, if so that is OK.

5). To insert a record into "The Records" of "pre 1950" sheets is it OK to select the row underneath and click insert row and use an unused number in Column A, or is their another way?

6). Would I be able to add another column (Comments/Remarks) say into "The Records", "pre1950" and "Miscellany-WD & Scarce etc" sheets (as they all should have the same amount of columns) without it upsetting everything again like the stats?

Many thanks once again!

Regards

Keith

Hi Jeovany & Andreas

My database is not adding the pre 1950 records up properly. If you remember you have added an extra worksheet "To be revised" and I can't find what is causing this problem again. My pre 1950 worksheet has 5556 rows but only displays 5543 in name manager. Is there any thing that I can do at this point?

Regards

Keith Naylor

Hi Jeovany

The database is not adding the records up correctly. I believe the problem lies in the fact that the code is not using the column Count which can have numbers more than one in there. For instance when you type in AW in cell E2 the result is 2 short in cell D91 because two records have a two in the cell in sheet pre1950. The same applies to the Records sheet.

=COUNTIFS(pre1950tb[BTO],Specific_Species,pre1950tb[Progress],"OK",pre1950tb[CountyCode],$A59,pre1950tb[Year],"<1950")

Would you please supply me with the extra bit of code to add into the formula.

Regards

Keith

Would you please supply me with the extra bit of code to add into the formula.

Regards

Keith "> Hi Jeovany

The database is not adding the records up correctly. I believe the problem lies in the fact that the code is not using the column Count which can have numbers more than one in there. For instance when you type in AW in cell E2 the result is 2 short in cell D91 because two records have a two in the cell in sheet pre1950. The same applies to the Records sheet.

=COUNTIFS(pre1950tb[BTO],Specific_Species,pre1950tb[Progress],"OK",pre1950tb[CountyCode],$A59,pre1950tb[Year],"<1950")

Would you please supply me with the extra bit of code to add into the formula.

Regards

Keith

Hi Keith

The formulas are correct, and here is the reason:

On the pre-1950 sheet, in rows 93 and 94, in the Progress column, was entered "OK same" shown in the highlighted yellow cells. The formula only counts the "OK" values, these 2 lines of data are missing in the count.

The "To be Revised " sheet has nothing to do with the formulas, and is not used in any calculations.

The sheet was added simply, to alert you about the many mistakes/typos/alterations entered in the Progress column that could affect the accuracy of the calculations, exactly as pointed in the above comments and pictures.

I explained that in a previous post.

Here is the list of the many OK types entered in the Progress column, repeated multiple times, and surely not counted in the Stats sheet (shown in the "To be Revised " sheet)

The formula I previously used follows the basis of your original file.

If you want to include/count all the ones that contain the word OK in the Progress column, then

We need to replace in all the formulas the "OK" with "*OK*"

You may download the file with the correction here Archived Database-BBRC (Jeovany Fix 16-03-2025).xlsx

Regards

Jeovany