Showing a record if it has a certain label

[NJS1982]

Hi all, I have a sheet with a list of URNs (Unique Reference Numbers) on one worksheet. Each URN has a 'status'.
On another sheet entirely, I want to be able to lift the URNs with a set status of 'Completeness and Authenticity Checks'. If the status is anything else, I am not bothered.
Does anyone know what formula I should try and use? Assume it is a IF formula, perhaps with some sort of vlookup function?
Thanks!

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

Attachments

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[NJS1982]

URN	ZZZ	YYY	Current State	DDD	# Days
2017005875	ZZZ	YYY	Completeness and Authenticity Checks	DDD	4
2017006424	ZZZ	YYY	Completeness and Authenticity Checks	DDD	6
2017005622	ZZZ	YYY	Completeness and Authenticity Checks	DDD	8
2017006580	ZZZ	YYY	Completeness and Authenticity Checks	DDD	10
2017005728	ZZZ	YYY	Completeness and Authenticity Checks	DDD	22
2017006937	ZZZ	YYY	Completeness and Authenticity Checks	DDD	35
2017006068	ZZZ	YYY	Completeness and Authenticity Checks	DDD	45
2017006871	ZZZ	YYY	Completeness and Authenticity Checks	DDD	67

<tbody>
</tbody>

URN (col A)	Current State (Column B)
2017003323	XX
2017004796	XX
2017005306	XX
2017005875	Completeness and Authenticity Checks
2017006424	Completeness and Authenticity Checks
2017001182	XX
2017001120	XX

<tbody>
</tbody>

 

[micsza]

The good solution will depend on how you want to store the result. But if you are ok with just simple column, then it couldn't be easier: select the range you want your result then Control+Shift+Enter with

=IF(Sheet3!D5:D9="Completeness and Authenticity Checks",Sheet3!C5:C9,"")

adjusting bolded ranges.

 

[NJS1982]

So, the first table is my working sheet and the second table is the OA sheet (where the data is coming from).
On the working sheet, I want the URN to be there IF it appears with a 'Current State' of 'Completeness and Authenticity Checks' in column B on my OA sheet. If it has 'xx', then I don't want it to appear on my working sheet.

 

[NJS1982]

The good solution will depend on how you want to store the result. But if you are ok with just simple column, then it couldn't be easier: select the range you want your result then Control+Shift+Enter with

=IF(Sheet3!D5:D9="Completeness and Authenticity Checks",Sheet3!C5:C9,"")

adjusting bolded ranges.

Thank you. This half works...this is my formula:

=IF('OA'!$O$2:$O$2500="Completeness and Authenticity Checks",'OA'!$A$2:A2A2500,"")

is there a way of it not using a line if the state is not Completeness and Authenticity? At the minute, lines 2,3,4,5 are blank (as they are ' Submitted'), but lines 10 and 11 are populating (as their state is 'Completeness and Authenticity Checks), lines 12,13,14,15 are blank (their states are 'Submitted'), line 16 is populated (this one is 'Completeness and Authenticity Checks').... I want to really just show lines 10, 11 and 16, if that makes sense? So not have a load of blank lines?

Not sure it is possible without a Macro (and I am a novice at those!)