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

="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 Keith,

well, it's not so hard to use, but it's a complete new world for you and it is only the first step to get a professional analyze.

In column A are to slicers, in there you can select one or multiple items and the Pivot table in column B:E updates and calculate the values.

All that full automated, no formulas at all. Here's the file:
https://www.dropbox.com/scl/fi/8ao9vvf14rss22ceg6gwk/e640477c-ab7a-404d-8953-303e6a1dc442.xlsx?rlkey=tgjz013tn3domztq325vv2lra&dl=1

In that file are 4 data sheets and 3 analyze sheets with pivot tables.

I changed the headers in the data sheets a bit, because to get all data together you must use the same headers everywhere. The data in each sheet is formatted as table, so we can access it using Power Query and load it as a connection only.

The first 3 tables are stacked up using Power Query into the All query, which is loaded into the Data Model. The table in the BTO sheet is also loaded into the Data Model.

In the Data Model / Power Pivot I created a relationship between the tables to combine the data. And I've created 2 measures to make the same calculation before/after 1950 as you did in your formulas.

The challenge to get this to work is your data, because you have lot of errors, wrong/bad data. Also, the results you count with your formulas in your file are all wrong, because of the wrong data. And this errors caused some strange results, for example year 777 or empty years appears in the pivot tables.

Look into the year column in the "The Records" sheet, in there is much more as only years.

Also the count columns contains text like "1+" or "1 to 3" and so on. Excel can not sum up text!

You should first start by cleaning up your data, which will take at least several weeks, IMHO. If you then want to delve into all of this, it will keep you busy for months.

Andreas.

Hi Jeovany

I have been in our local book shop Waterstones do 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?

Regards

Keith