Excel Summary Report Display Expired Dates From Multiple Sheets

[wcm69]

Hi

Hoping someone can help/advise.

I have created a workbook showing various companies employee’s training certificates expiry dates. Cells are formatted to highlight expired and soon to expire (within 30 days) dates. Each of the (21) companies is shown on a separate worksheet (tab).

Current workbook layout:

Each company name is in (Cell: A6). Below this are the worker’s names in (Cells: A7:A10). The (various) certificate headings are across 22 columns (Cells: B6:W6). Below each of these columns are the formatted cert (expiry) dates. Note. Some columns are blank where a cert is not held.

Due to the number of companies (possibly increasing in the future) I would like to have a ‘Summary Report’ page (Sheet1) to look-up/find the desired company (using a dropdown list) and display the data/dates beneath the following header columns (Sheet 1 / Cells: B5:E5).

1) The worker’s name. 2) The certificate name. 3) Soon to expire certificate dates. 4) Expired certificate dates.

On the ‘Summary Report’ (Sheet1) I have already created the dynamic drop-down list with the company names (Cell: H5) from the 21 worksheet tabs to use for the lookup.

Any help is greatly appreciated.

Thanks in advance.

 

[awoohaw]

Very doable. But, I'd recommentd you create a master data sheets with only these columns:
Company, Employee, Certificate Name, Certificate Expiry Date.

Take all of the 22 sheets you have now, bring them into Power Query and "Unpivot" them to make your initial master list. You can then provide all kinds of reports.

Except, you need a version of excel that has power query. but that would only be necessary for the first set up. Once you have the data in a master table you can do regular reports and such.

 

[wcm69]

Very doable. But, I'd recommentd you create a master data sheets with only these columns:
Company, Employee, Certificate Name, Certificate Expiry Date.

Take all of the 22 sheets you have now, bring them into Power Query and "Unpivot" them to make your initial master list. You can then provide all kinds of reports.

Except, you need a version of excel that has power query. but that would only be necessary for the first set up. Once you have the data in a master table you can do regular reports and such.

Hi Awoohaw,

Many thanks for the swift response and advise. Unfortunately I can not use the Power Query option, and was hoping for a work round of some sort using excel functions.

I've looked online (YouTube) and seen what look like a few possibilities, but I'm not really Excel proficient to be able to use them correctly to achieve what I want.

If you've got another solution or other suitable advice I'd be really grateful.

 

[awoohaw]

Well, there are ways to unpivot "manually".
Here is a youtube I just found. I do not know how great it is.

his formula seems somewhat automated.

Your request is is similar to asking for an unpivot for the summary reports, so you are going to need to do something like that anyway. But, that would be monthly. The method I'm suggesting is just a one time event.

And some folks here may have VBA solutions as well. I am not VBA proficient. I'll try to dummy a file up and see i can replicate what the guy in the youtube does.

 

[awoohaw]

Which version of Excel do you have? And, if possible you should add that to your profile here so people can see what you have and make suggestions to fit your version.

 

[wcm69]

Well, there are ways to unpivot "manually".
Here is a youtube I just found. I do not know how great it is.

his formula seems somewhat automated.

Your request is is similar to asking for an unpivot for the summary reports, so you are going to need to do something like that anyway. But, that would be monthly. The method I'm suggesting is just a one time event.

And some folks here may have VBA solutions as well. I am not VBA proficient. I'll try to dummy a file up and see i can replicate what the guy in the youtube does.

Thank you for the link. I've taken a look at the video - a solution may lay within this approach. I'll it a try to see if I can make any progress, though I'm still not sure how I'll get all the sheets into one master sheet - which I think is needed.

I'll give feedback asap.

Many thanks again.

 

[awoohaw]

Well, you could copy and paste 22 times. Or you could write a formula referencing the sheet names in an Indirect function. If you aren't familiar with that, is not a great function for permanent use in worksheets, but it is good for your situation. As you'll be copy/paste value after the unpivot. You'll see the guy use it in the video.

 

[awoohaw]

I've come up with something.
I'm not sure the range names will come through in the calculations, if not I'll respost the calculations separately. I also have some instructions, that i hope are accurate.

WorkBook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5											Company	Name	Certification	Cert Exp Dt
6	Company Name	Cert1	Cert2	Cert3	Cert4	Cert5	Cert6	Cert7	Cert8		Company Name	Name1	Cert1	1/0/1900
7	Name1					8/25/2023	3/7/2023				Company Name	Name1	Cert2	1/0/1900
8	Name2	10/23/2023		8/10/2022	3/7/2023						Company Name	Name1	Cert3	1/0/1900
9	Name3	12/25/2022		2/8/2023					3/27/2023		Company Name	Name1	Cert4	1/0/1900
10	Name4			5/7/2022	3/1/2023	7/11/2022	11/28/2023		4/10/2023		Company Name	Name1	Cert5	8/25/2023
11											Company Name	Name1	Cert6	3/7/2023
12				NamedRanges							Company Name	Name1	Cert7	1/0/1900
13					Steps to unpivot with the layout in A6:I10						Company Name	Name1	Cert8	1/0/1900
14			Address	Name of range	save a read only version of the workbook so you don't overwrite formulas with paste values.						Company Name	Name2	Cert1	10/23/2023
15	Company Name	Cell where Comp Name is;	$A$6	CompanyName	1. Figure out your cell locations.						Company Name	Name2	Cert2	1/0/1900
16	Employee Names	Cells where Empl Names are	$A$7:$A$10	EmpNames	2. Use Name Manager>>Define Names according to the chart to the left.						Company Name	Name2	Cert3	8/10/2022
17	Certificate Name	Cells where Cert Names are	$B$6:$I6	Certifications	3. Copy the row header K5:N5 as on this sheet						Company Name	Name2	Cert4	3/7/2023
18	Certificate Exp Dates	Cells where the dates are	$B$7:$I$10	CertDates	4. At K6, copy K6:N6						Company Name	Name2	Cert5	1/0/1900
19					5. Copy Down.						Company Name	Name2	Cert6	1/0/1900
20					7. Paste as Values						Company Name	Name2	Cert7	1/0/1900
21					8. Turn on Filter						Company Name	Name2	Cert8	1/0/1900
22					9. Filter all columns to show the zero values						Company Name	Name3	Cert1	12/25/2022
23					10. delete the rows with zero values in certificate date						Company Name	Name3	Cert2	1/0/1900
24					11. format the date column.						Company Name	Name3	Cert3	2/8/2023
25					12. I think that worksheet is finished.						Company Name	Name3	Cert4	1/0/1900
26											Company Name	Name3	Cert5	1/0/1900
27											Company Name	Name3	Cert6	1/0/1900
28											Company Name	Name3	Cert7	1/0/1900
29											Company Name	Name3	Cert8	3/27/2023
30											Company Name	Name4	Cert1	1/0/1900
31											Company Name	Name4	Cert2	1/0/1900
32											Company Name	Name4	Cert3	5/7/2022
33											Company Name	Name4	Cert4	3/1/2023
34											Company Name	Name4	Cert5	7/11/2022
35											Company Name	Name4	Cert6	11/28/2023
36											Company Name	Name4	Cert7	1/0/1900
37											Company Name	Name4	Cert8	4/10/2023
38
Unpivot Old Way

Cell Formulas
Range	Formula
K6:K37	K6	= IF( ROWS(CompanyName) > (ROWS(EmpNames)*COLUMNS(Certifications)),"",CompanyName)
L6:L37	L6	=INDEX( EmpNames, ROUNDUP( ROWS($A$7:A7) / COLUMNS(CertDates),0),1)
M6:M37	M6	=INDEX( Certifications, (1+MOD( (ROWS($A$7:A7)-1), COLUMNS(CertDates))))
N6:N37	N6	=INDEX( CertDates, ROUNDUP( ROWS($A$7:A7) / COLUMNS(CertDates),0), (1+MOD( (ROWS($A$7:A7)-1), COLUMNS(CertDates))) )

Named Ranges
Name	Refers To	Cells
CertDates	='Unpivot Old Way'!$B$7:$I$10	L6:N37
Certifications	='Unpivot Old Way'!$B$6:$I$6	M6:M37, K6:K37
CompanyName	='Unpivot Old Way'!$A$6	K6:K37
EmpNames	='Unpivot Old Way'!$A$7:$A$10	K6:N37

 

[awoohaw]

if the above works it is much less complicated than how the guy in you tube does it.

One note that i did not make clear... you need to name your ranges that fit the dimensions of your cross tab data. You will probably have more than 4 rows, and I think you mentioned you have over 20 certifications.

 

[awoohaw]

another thing just occured to me. the formulas use defined names for the ranges and they would be same on each worksheet in your workbook. So that means you have to
1. Delete the names after every worksheet you unpivot.
or
2. when you add a name, you'll need to define at the worksheet level, and not the workbook level.