Formula to only display selected data

[SueCaddy]

If a user enters a number in D1 (here shown in red as 9119) how do I get only those records with 9199 in the Number column to display. (must display all data for that row)

		Enter your code here	9119
Number	Name	Project	Oct-23​	Nov-23​
9119	Joe Blogs	Aurion Update to Cloud - Discovery	50%​	50%​
9009	Jane Doe	Aurion Update to Cloud - Discovery	20%​	40%​
9082	John Smith	Aurion Update to Cloud - Discovery
9073	Alex Ander	Aurion Update to Cloud - Discovery	10%​	10%​
9099	Mary Smith	Aurion Update to Cloud - Discovery	10%​	10%​
9119	Joe Blogs	BundleDocs Single Sign On (WIC)
9009	Jane Doe	BundleDocs Single Sign On (WIC)
9082	John Smith	BundleDocs Single Sign On (WIC)
9009	Jane Doe	Cloud Guard Firewall Tactical Upgrade	25%	50%​
9073	Alex Ander	Cloud Guard Firewall Tactical Upgrade	5%	10%​
9082	John Smith	Cloud Guard Firewall Tactical Upgrade	10%​	25%
9099	Mary Smith	Cloud Guard Firewall Tactical Upgrade	10%​	20%​
9119	Joe Blogs	Contact Centre Capability Uplift (Deliver)	50%​	50%​
9073	Alex Ander	Contact Centre Capability Uplift (Deliver)	30%​	20%​
9082	John Smith	Contact Centre Capability Uplift (Deliver)	40%​	40%​

 

[kevin9999]

You said "Formula" in your title - which is possible (you have 365) using the Filter() function, but I suspect you may want the result to display in the current range? If so, you're looking at a VBA solution. Right click the Sheet tab name, select View Code, and paste the code below into the blank window that appears on the right of screen. You'll need to save your file as macro-enabled or binary format.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If Me.AutoFilterMode Then Me.AutoFilter.ShowAllData
        With Range("A3")
            .AutoFilter 1, Target
        End With
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Sample 1

Book1
	A	B	C	D	E
1			Enter your code here	9119
2
3	Number	Name	Project	Oct-23	Nov-23
4	9119	Joe Blogs	Aurion Update to Cloud - Discovery	50%	50%
9	9119	Joe Blogs	BundleDocs Single Sign On (WIC)
16	9119	Joe Blogs	Contact Centre Capability Uplift (Deliver)	50%	50%
19
Sheet1

Sample 2

Book1
	A	B	C	D	E
1			Enter your code here	9009
2
3	Number	Name	Project	Oct-23	Nov-23
5	9009	Jane Doe	Aurion Update to Cloud - Discovery	20%	40%
10	9009	Jane Doe	BundleDocs Single Sign On (WIC)
12	9009	Jane Doe	Cloud Guard Firewall Tactical Upgrade	25%	50%
19
Sheet1

 

[awoohaw]

Is there a reason you do not want to use the FILTER tool:

 

[SueCaddy]

Is there a reason you do not want to use the FILTER tool:
View attachment 101171



View attachment 101170

Hello there, yes I don't want to use filter as I do not want users to be able to see all entries.

 

[kevin9999]

Hello there, yes I don't want to use filter as I do not want users to be able to see all entries.

So could you get the code in post #2 to work, and did it do what you wanted?

 

[awoohaw]

Hello there, yes I don't want to use filter as I do not want users to be able to see all entries.

Okay, so you want to have all the data hidden and then only display what is matched by the filter?
Can you put the data on a hidden worksheet? and have it displayed on a different worksheet?

Hide this worksheet:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F
1			PUT ON HIDDEN WORKSHEET
2
3	Number	Name	Project	Oct-23	Nov-23
4	9119	Joe Blogs	Aurion Update to Cloud - Discovery	50%	50%
5	9009	Jane Doe	Aurion Update to Cloud - Discovery	20%	40%
6	9082	John Smith	Aurion Update to Cloud - Discovery
7	9073	Alex Ander	Aurion Update to Cloud - Discovery	10%	10%
8	9099	Mary Smith	Aurion Update to Cloud - Discovery	10%	10%
9	9119	Joe Blogs	BundleDocs Single Sign On (WIC)
10	9009	Jane Doe	BundleDocs Single Sign On (WIC)
11	9082	John Smith	BundleDocs Single Sign On (WIC)
12	9009	Jane Doe	Cloud Guard Firewall Tactical Upgrade	25%	50%
13	9073	Alex Ander	Cloud Guard Firewall Tactical Upgrade	5%	10%
14	9082	John Smith	Cloud Guard Firewall Tactical Upgrade	10%	25%
15	9099	Mary Smith	Cloud Guard Firewall Tactical Upgrade	10%	20%
16	9119	Joe Blogs	Contact Centre Capability Uplift (Deliver)	50%	50%
17	9073	Alex Ander	Contact Centre Capability Uplift (Deliver)	30%	20%
18	9082	John Smith	Contact Centre Capability Uplift (Deliver)	40%	40%
19
SueCaddy

and have this as your output worksheet:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H
1		Enter Code:	9119
2	9119	Joe Blogs	Aurion Update to Cloud - Discovery
3	9119	Joe Blogs	BundleDocs Single Sign On (WIC)
4	9119	Joe Blogs	Contact Centre Capability Uplift (Deliver)
5
6
SueCaddy2

Cell Formulas
Range	Formula
A2:C4	A2	=FILTER(SueCaddy!$A$3:$C$18,SueCaddy!$A$3:$A$18=C1,"No Record")
Dynamic array formulas.

 

[SueCaddy]

So could you get the code in post #2 to work, and did it do what you wanted?

Thank you Kevin, yes it has. much appreciated

 

[SueCaddy]

Okay, so you want to have all the data hidden and then only display what is matched by the filter?
Can you put the data on a hidden worksheet? and have it displayed on a different worksheet?

Hide this worksheet:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F
1			PUT ON HIDDEN WORKSHEET
2
3	Number	Name	Project	Oct-23	Nov-23
4	9119	Joe Blogs	Aurion Update to Cloud - Discovery	50%	50%
5	9009	Jane Doe	Aurion Update to Cloud - Discovery	20%	40%
6	9082	John Smith	Aurion Update to Cloud - Discovery
7	9073	Alex Ander	Aurion Update to Cloud - Discovery	10%	10%
8	9099	Mary Smith	Aurion Update to Cloud - Discovery	10%	10%
9	9119	Joe Blogs	BundleDocs Single Sign On (WIC)
10	9009	Jane Doe	BundleDocs Single Sign On (WIC)
11	9082	John Smith	BundleDocs Single Sign On (WIC)
12	9009	Jane Doe	Cloud Guard Firewall Tactical Upgrade	25%	50%
13	9073	Alex Ander	Cloud Guard Firewall Tactical Upgrade	5%	10%
14	9082	John Smith	Cloud Guard Firewall Tactical Upgrade	10%	25%
15	9099	Mary Smith	Cloud Guard Firewall Tactical Upgrade	10%	20%
16	9119	Joe Blogs	Contact Centre Capability Uplift (Deliver)	50%	50%
17	9073	Alex Ander	Contact Centre Capability Uplift (Deliver)	30%	20%
18	9082	John Smith	Contact Centre Capability Uplift (Deliver)	40%	40%
19
SueCaddy

and have this as your output worksheet:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H
1		Enter Code:	9119
2	9119	Joe Blogs	Aurion Update to Cloud - Discovery
3	9119	Joe Blogs	BundleDocs Single Sign On (WIC)
4	9119	Joe Blogs	Contact Centre Capability Uplift (Deliver)
5
6
SueCaddy2

Cell Formulas
Range	Formula
A2:C4	A2	=FILTER(SueCaddy!$A$3:$C$18,SueCaddy!$A$3:$A$18=C1,"No Record")
Dynamic array formulas.

thank you, yes this would also work

 

[kevin9999]

Glad we could help, thanks for the feedback, and welcome to the forum!