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]

[TABLE="class: grid, width: 586"]
<tbody>[TR]
[TD]URN[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Current State[/TD]
[TD]DDD[/TD]
[TD]# Days[/TD]
[/TR]
[TR]
[TD]2017005875[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2017006424[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2017005622[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2017006580[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2017005728[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]2017006937[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]2017006068[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]2017006871[/TD]
[TD]ZZZ[/TD]
[TD]YYY[/TD]
[TD]Completeness and Authenticity Checks[/TD]
[TD]DDD[/TD]
[TD]67[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 405"]
<tbody>[TR]
[TD="align: left"]URN (col A)[/TD]
[TD="align: left"]Current State (Column B)[/TD]
[/TR]
[TR]
[TD="align: right"]2017003323[/TD]
[TD="align: left"]XX[/TD]
[/TR]
[TR]
[TD="align: right"]2017004796[/TD]
[TD="align: left"]XX[/TD]
[/TR]
[TR]
[TD="align: right"]2017005306[/TD]
[TD="align: left"]XX[/TD]
[/TR]
[TR]
[TD="align: right"]2017005875[/TD]
[TD="align: left"]Completeness and Authenticity Checks[/TD]
[/TR]
[TR]
[TD="align: right"]2017006424[/TD]
[TD="align: left"]Completeness and Authenticity Checks[/TD]
[/TR]
[TR]
[TD="align: right"]2017001182[/TD]
[TD="align: left"]XX[/TD]
[/TR]
[TR]
[TD="align: right"]2017001120[/TD]
[TD="align: left"]XX[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!)