Formula Help

[NWPhotoExplorer]

I build a simple spreadsheet to keep track of the job numbers I work on through the day. In order to get paid, these job numbers have a corresponding SR number that I am pulling from a table. Some jobs may not have a SR number, so those need to be requested. I made a simple formula that should work, but isn't. I cannot figure out what the issue is with it.

Here is my table that I use to keep track of the jobs and how long I work on them.

Job Tracker.xlsx
	C	D	E	F	G
7	Date	Job Number	Task Type	OFS SR Created	SR Number
209	3/19/2021	JB0000074884	QC	REQUEST	0
210	3/19/2021	JB0000553225	DESIGN	CREATED	204938
211	3/19/2021	82496	MEETING	CREATED	82496
212	3/19/2021	JB0000553225	DESIGN	CREATED	204938
213	3/19/2021	JB0000069966	QC	REQUEST	0
214	3/19/2021	JB0000586108	QC	REQUEST	0
215	3/19/2021	JB0000586976	QC	CREATED	0
216	3/19/2021	JB0000564050	SURVEY	REQUEST	0
DAILY TRACKER

Cell Formulas
Range	Formula
F209:F216	F209	=IF(OR(COUNTIFS($D$8:$D$216,D209)>1,AND(ISNUMBER(G209),(G209<>0))),"CREATED","REQUEST")
G209:G216	G209	=IF(ISBLANK([@[Job Number]]),"",VLOOKUP([@[Job Number]],OFS,2,FALSE))

Named Ranges
Name	Refers To	Cells
P2JobNumber	=OFS[Job Number]	G209:G216

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8:G216	Expression	=$G8=0	text	NO
D8:D216	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D8:D216	List	=P2JobNumber
E8:E216	List	DESIGN, ASBUILT, MEETING, QC, REDESIGN, REWORK, SURVEY, OTHER

Then I made a separate page to filter out jobs that I am working on for that day without the SR numbers.

Job Tracker.xlsx
	A	B	C	D	E
1	Date	Job Number	Task Type	OFS SR Created	OFS SR Created
2	3/19/2021	#N/A
REQUEST SR NUMBER

Cell Formulas
Range	Formula
B2	B2	=UNIQUE(FILTER(T_JOBS[[#All],[Job Number]:[SR Number]],(T_JOBS[[#All],[Date]]=A2)*(T_JOBS[[#All],[Job Number]]<>82496)*(T_JOBS[[#All],[OFS SR Created]]="REQUEST")),FALSE,FALSE)

I'm not sure what the issue is with the formula. If I get rid of the part where it looks to see if there is a number in the G column, it seems to work, but then it sometimes pulls job numbers that already has the SR number. What do I need to change to my UNIQUE(FILTER) formula work?

Thanks in advance!

 

[jasonb75]

Your formulas work fine when I copy the example to a workbook.
The N/A could be coming from another row that you have hidden in the example, which would likely be caused by the formula in column G not finding a match for the job number in the OFS range. Try nesting the column G formula into IFERROR to show a blank when there is no match.

=IF(ISBLANK([@[Job Number]]),"",IFERROR(VLOOKUP([@[Job Number]],OFS,2,FALSE),""))

Book1 (version 2).xlsb
	A	B	C	D	E
1	Date	Job Number	Task Type	OFS SR Created	OFS SR Created
2	3/19/2021	JB0000074884	QC	REQUEST	0
3		JB0000069966	QC	REQUEST	0
4		JB0000586108	QC	REQUEST	0
5		JB0000586976	QC	REQUEST	0
6		JB0000564050	SURVEY	REQUEST	0
Sheet6

Cell Formulas
Range	Formula
B2:E6	B2	=UNIQUE(FILTER(T_JOBS[[#All],[Job Number]:[SR Number]],(T_JOBS[[#All],[Date]]=A2)*(T_JOBS[[#All],[Job Number]]<>82496)*(T_JOBS[[#All],[OFS SR Created]]="REQUEST")),FALSE,FALSE)
Dynamic array formulas.

Whilst it doesn't appear to be the case here, there are times when including your table headers with the #All name could cause problems, it would be best not to use them unless absolutely necessary.

=UNIQUE(FILTER(T_JOBS[[Job Number]:[SR Number]],(T_JOBS[Date]=A2)*(T_JOBS[Job Number]<>82496)*(T_JOBS[OFS SR Created]="REQUEST")))

 

[NWPhotoExplorer]

Your formulas work fine when I copy the example to a workbook.
The N/A could be coming from another row that you have hidden in the example, which would likely be caused by the formula in column G not finding a match for the job number in the OFS range. Try nesting the column G formula into IFERROR to show a blank when there is no match.

=IF(ISBLANK([@[Job Number]]),"",IFERROR(VLOOKUP([@[Job Number]],OFS,2,FALSE),""))

I added the IFERROR part and now it works. That is so odd that it wouldn't work on mine, yet it did for you without that. Thanks for that!.

Whilst it doesn't appear to be the case here, there are times when including your table headers with the #All name could cause problems, it would be best not to use them unless absolutely necessary.

=UNIQUE(FILTER(T_JOBS[[Job Number]:[SR Number]],(T_JOBS[Date]=A2)*(T_JOBS[Job Number]<>82496)*(T_JOBS[OFS SR Created]="REQUEST")))

As for the [#ALL] header part. I removed them, but I had read something a while back saying I needed them. Can you explain when I should use them?

Thanks!

 

[jasonb75]

Can you explain when I should use them?

I use tables frequently and have never found a time when it has been necessary to use [#All] in a formula. There are occasions when you might find that you need to refer to the header row in a formula, but not at the same time as the main body of the table.