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,

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

Hi Jeovany

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

On the other hand

I'm agree with Andrea's comments on using Power Query to create all the tables for the Stats sheet. (No formulas, no VBA codes).

Jeovany

Hi Keith

1. Cell D4=0 because the record 67212 does not exist in the "The Records" sheet which is the table the formulas take the information from.

So you need to copy that info into the Records Table. And check for others possible missing records.

2. You may use the following formula for making the headers in column K,L change according to the current year.

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

3. The 3rd question I don't understand what you mean,

Could you please give more details, add some pictures to illustrate the problem?

Regards

Jeovany