Trying to do a total sum for each user over multiple sheets

[no-name]

Hello, I’m trying to create a summary page to add up users total figures over each day (seperate pages per day) but I have a couple of issues with this.

Firstly, the summary page needs to be able to look at user names and add each row when they appear on all sheets (mon-sun) and put each name with the total added sums for each collum.

Lets say Bill isnt in for a week due to holiday, he shouldnt be on that weeks summary page.

Secondly, I don’t want to have a list of names of people to keep on top of, I want this to be as automated as possible, people come and go and this would be one less thing to worry about.

And lastly, I need it to be smart enough to recognise names that are not writen the same e.g

will
Will
WILL
william
William
WILLIAM

and to be able to group these names as one user and also count the number of times a person has been on till over week.

Also, no macros if possible as IT wont accept this.

Sorry for the English as I am rather dyslexic.
Hope this made sence.

I have included a sheet to better show what I am trying to explain.
Many thanks

test.xlsx (30.9 KB)

[karolus]

@no-name

And lastly, I need it to be smart enough to recognise names that are not writen the same e.g

will
Will
WILL
william
William
WILLIAM

Yoe want Software smarter than humans…good luck!

[Wanderer]

@no-name

the summary page needs to be able to look at user names

Have you looked into pivot-tables?

@no-name

will
Will
WILL

This is the easy part of the game, as you can use a function like LOWER() to search/compare as LOWER(Will) is equal to LOWER(WILL). The complicated part starts with accents á umulauts ä and special Characters like ß wich (until recently) had no uppercase, so was translated to SS.
.

@no-name

William

Now we come to the impossible part:

• Your William is the same person as Will? So your idea could be to search for parts in the list of names.
• If you said yes, who is Liam ? Also the same person as William ?
• So if William is in your list Will and Liam are the the same person as William. If you exclude William they are different persons.

@no-name

this would be one less thing to worry about

If this is your main point, do a pivot table and never think about accuracy. In the other cases you may find out why we all get this nice cards with id-numbers, qr-codes when it is important to register the precise person.
.
PS: And even rare names are not always unique, the John Smith types need additional work in any case.
.
PPS: As you use Excel-Files IMHO you can not use macros in LibreOffice anyway - you would be restricted to the part of VBA, LibreOffice can execute…