Search function

[Graham Mackay]

Hi, I have a workbook that I am setting up to manage Permits. It has two active sheets. Register is the database and Index is the input function. Within Index I also have a search function which I want to search the register and display all the open Permits by searching column N and displaying any with Blank cells. I am struggling to get a macro to do this. Any help gratefully received

 

[Graham Mackay]

Hi Fluff,

Hopefully this is what you are looking for.

Permit To Work Register (3).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Permit Number	Task Description	Permit Type	Location	Permit Requested by	Permit Issued By	Permit Issuer N°	Date Issued	Time Issued	Permit Receiver	Permit Returned By	Date Returned	Time Returned	Date Cancelled	Time Cancelled	Person Cancelling Permit	Comments
2	8312869	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
3		8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
4		8321041	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
5		8132789	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
6		8312869	8321041	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
7			8321041	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
8			8132789	8321041	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
9			8132789	8321041	8218451	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
10			8312869	8321041	8321041	8218451	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901	8321901
11				8132789	8321041	8218451	8218451	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901	8321901
12				8132789	8321041	8321041	8218451	8218451	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901	8321901
13				8132789	8321041	8321041	8218451	8218451	8218451	8218451	8218451	8218451	8321901	8321901	8321901	8321901	8321901
14				8312869	8132789	8321041	8321041	8218451	8218451	8218451	8218451	8218451	8218451	8321901	8321901	8321901	8321901
15					8132789	8321041	8321041	8218451	8218451	8218451	8218451	8218451	8218451	8218451	8321901	8321901	8321901
16					8132789	8321041	8321041	8321041	8218451	8218451	8218451	8218451	8218451	8218451	8218451	8321901	8321901
17					8132789	8132789	8321041	8321041	8218451	8218451	8218451	8218451	8218451	8218451	8218451	8218451	8321901
18
Live Permits

Cell Formulas
Range	Formula
B2:Q17	B2	=IFERROR(INDEX(Register!$A$2:$A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:O$1000="")),ROWS(B$2:B2))),"")
A2:A17	A2	=IFERROR(INDEX(Register!$A$2:$A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!N$2:$N$1000="")),ROWS(A$2:A2))),"")
A18	A18	=IFERROR(INDEX(Register!$A$2:$A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!N$2:$N$1000="")),ROWS(A$3:A19))),"")

 

[Fluff]

The formula in A2 should be
=IFERROR(INDEX(Register!A$2:A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:$N$1000="")),ROWS(A$2:A2))),"")

copied down & across

 

[Graham Mackay]

The formula in A2 should be
=IFERROR(INDEX(Register!A$2:A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:$N$1000="")),ROWS(A$2:A2))),"")

copied down & across

Thanks you for that it seems to have dealt with the numbering issue but doesn't copy over text. The source information is below. Thank you for your patience as I am a total novice at this.

Permit To Work Register (3).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Permit Number	Task Description	Permit Type	Location	Permit Requested by	Permit Issued By	Permit Issuer N°	Date Issued	Time Issued	Permit Receiver	Permit Returned By	Date Returned	Time Returned	Date Cancelled	Time Cancelled	Person Cancelling Permit	Comments
2	8321901	Tank Cleaning		SWPH	J Begg	GA Mackay	1234567	20/03/2020	10:23	0				5/2/2020
3	8218451	Welding		STF	M Sutherland	GA Mackay	13456789	20/03/2020	9:20	M Gunn				2/6/2020
4	8321041	welding		DWTF	me	you	123456	10/2/2018	10:55	him				3/6/2020
5	8132789	Tank Cleaning		MAXWELL HOUSE	You	Me	123456	17/03/2020	12:45	Him				2/10/2020
6	8312869										65465463541	uiygukyg	654654654		654654654	54654654654	6.54655E+11
7		tank cleaning
8
9
Register

 

[Graham Mackay]

This is the table now

Permit To Work Register (3).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	8312869	0	0	0	0	0	0	0	0	0	65465463541	uiygukyg	654654654	0	654654654	54654654654	6.54655E+11
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Live Permits

Cell Formulas
Range	Formula
A2:Q16	A2	=IFERROR(INDEX(Register!A$2:A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:$N$1000="")),ROWS(A$2:A2))),"")

 

[Fluff]

That;s exactly what I would expect to see from the data you posted.
In what way is it wrong?

 

[Graham Mackay]

Sorry, I was expecting to see the text, example in column B in the source worksheet there is a task description in text but this is displayed as a zero in the second worksheet. as is the remaining columns with text.

 

[Fluff]

There is no text in col B for that permit number, the only data for that row is in cols K:M & O:Q

 

[Graham Mackay]

I apologise, yes I forgot I had entered dates in column N. This works perfectly. Thank you so much for taking the time to resolve this for me. I am very grateful.

Kind Regards and Stay Say
Graham

 

[Fluff]

You're welcome & thanks for the feedback.