Formula to only display selected data

S

SueCaddy

New Member
Joined
Oct 29, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 here9119
NumberNameProject
Oct-23​
Nov-23​
9119Joe BlogsAurion Update to Cloud - Discovery
50%​
50%​
9009Jane DoeAurion Update to Cloud - Discovery
20%​
40%​
9082John SmithAurion Update to Cloud - Discovery
9073Alex AnderAurion Update to Cloud - Discovery
10%​
10%​
9099Mary SmithAurion Update to Cloud - Discovery
10%​
10%​
9119Joe BlogsBundleDocs Single Sign On (WIC)
9009Jane DoeBundleDocs Single Sign On (WIC)
9082John SmithBundleDocs Single Sign On (WIC)
9009Jane DoeCloud Guard Firewall Tactical Upgrade25%
50%​
9073Alex AnderCloud Guard Firewall Tactical Upgrade5%
10%​
9082John SmithCloud Guard Firewall Tactical Upgrade
10%​
25%
9099Mary SmithCloud Guard Firewall Tactical Upgrade
10%​
20%​
9119Joe BlogsContact Centre Capability Uplift (Deliver)
50%​
50%​
9073Alex AnderContact Centre Capability Uplift (Deliver)
30%​
20%​
9082John SmithContact Centre Capability Uplift (Deliver)
40%​
40%​
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
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.

VBA Code: 
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
ABCDE
1Enter your code here9119
2
3NumberNameProjectOct-23Nov-23
49119Joe BlogsAurion Update to Cloud - Discovery50%50%
99119Joe BlogsBundleDocs Single Sign On (WIC)
169119Joe BlogsContact Centre Capability Uplift (Deliver)50%50%
19
Sheet1


Sample 2
Book1
ABCDE
1Enter your code here9009
2
3NumberNameProjectOct-23Nov-23
59009Jane DoeAurion Update to Cloud - Discovery20%40%
109009Jane DoeBundleDocs Single Sign On (WIC)
129009Jane DoeCloud Guard Firewall Tactical Upgrade25%50%
19
Sheet1
 
Upvote 0
Solution
Is there a reason you do not want to use the FILTER tool:
1698657118617.png




1698657058553.png
 
Upvote 0
SueCaddy said:
Hello there, yes I don't want to use filter as I do not want users to be able to see all entries.
Click to expand...
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
ABCDEF
1PUT ON HIDDEN WORKSHEET
2
3NumberNameProjectOct-23Nov-23
49119Joe BlogsAurion Update to Cloud - Discovery50%50%
59009Jane DoeAurion Update to Cloud - Discovery20%40%
69082John SmithAurion Update to Cloud - Discovery
79073Alex AnderAurion Update to Cloud - Discovery10%10%
89099Mary SmithAurion Update to Cloud - Discovery10%10%
99119Joe BlogsBundleDocs Single Sign On (WIC)
109009Jane DoeBundleDocs Single Sign On (WIC)
119082John SmithBundleDocs Single Sign On (WIC)
129009Jane DoeCloud Guard Firewall Tactical Upgrade25%50%
139073Alex AnderCloud Guard Firewall Tactical Upgrade5%10%
149082John SmithCloud Guard Firewall Tactical Upgrade10%25%
159099Mary SmithCloud Guard Firewall Tactical Upgrade10%20%
169119Joe BlogsContact Centre Capability Uplift (Deliver)50%50%
179073Alex AnderContact Centre Capability Uplift (Deliver)30%20%
189082John SmithContact Centre Capability Uplift (Deliver)40%40%
19
SueCaddy


and have this as your output worksheet:
Mr Excel Questions 71.xlsm
ABCDEFGH
1Enter Code:9119
29119Joe BlogsAurion Update to Cloud - Discovery
39119Joe BlogsBundleDocs Single Sign On (WIC)
49119Joe BlogsContact Centre Capability Uplift (Deliver)
5
6
SueCaddy2
Cell Formulas
RangeFormula
A2:C4A2=FILTER(SueCaddy!$A$3:$C$18,SueCaddy!$A$3:$A$18=C1,"No Record")
Dynamic array formulas.
 
Upvote 0
awoohaw said:
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
ABCDEF
1PUT ON HIDDEN WORKSHEET
2
3NumberNameProjectOct-23Nov-23
49119Joe BlogsAurion Update to Cloud - Discovery50%50%
59009Jane DoeAurion Update to Cloud - Discovery20%40%
69082John SmithAurion Update to Cloud - Discovery
79073Alex AnderAurion Update to Cloud - Discovery10%10%
89099Mary SmithAurion Update to Cloud - Discovery10%10%
99119Joe BlogsBundleDocs Single Sign On (WIC)
109009Jane DoeBundleDocs Single Sign On (WIC)
119082John SmithBundleDocs Single Sign On (WIC)
129009Jane DoeCloud Guard Firewall Tactical Upgrade25%50%
139073Alex AnderCloud Guard Firewall Tactical Upgrade5%10%
149082John SmithCloud Guard Firewall Tactical Upgrade10%25%
159099Mary SmithCloud Guard Firewall Tactical Upgrade10%20%
169119Joe BlogsContact Centre Capability Uplift (Deliver)50%50%
179073Alex AnderContact Centre Capability Uplift (Deliver)30%20%
189082John SmithContact Centre Capability Uplift (Deliver)40%40%
19
SueCaddy


and have this as your output worksheet:
Mr Excel Questions 71.xlsm
ABCDEFGH
1Enter Code:9119
29119Joe BlogsAurion Update to Cloud - Discovery
39119Joe BlogsBundleDocs Single Sign On (WIC)
49119Joe BlogsContact Centre Capability Uplift (Deliver)
5
6
SueCaddy2
Cell Formulas
RangeFormula
A2:C4A2=FILTER(SueCaddy!$A$3:$C$18,SueCaddy!$A$3:$A$18=C1,"No Record")
Dynamic array formulas.
Click to expand...
thank you, yes this would also work
 
Upvote 0
Glad we could help, thanks for the feedback, and welcome to the forum!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,223
Messages
6,123,722
Members
449,116
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top