Check if a column has a reference on each tab but using 2 criteria

[GrumpyChi]

Hi


I have a worksheet with several tabs. Each tab is named after the Nominal code. On each individual tab there will be data like the below.

Tab named "520000"
Ref Profit centre Nominal Code
1 07 520000
1 08 520000
2 07 520000

Tab named "520001"
Ref Profit centre Nominal Code
1 07 520001
1 08 520001
2 07 520001

There is then a centralised tab which uses this formula to check whether each line has been referenced with a number. The formula is =IFERROR(IF(MATCH(A673,INDIRECT(R673&"!A:A"),0)>0,"ok","check"),"check")

In this case, A673 is the reference eg. 1

I want to add something to this formula to check whether that particular profit centre has a reference. At the moment, the formula would return "OK" for the tab 520000 for the line with a reference of 1. But in this case, I want it to be profit centre specific. Eg. if the second line from the above example was left blank, the current formula would still return OK. Hope this makes sense. Thank you.

 

[ExceLoki]

can you give an example of what your goal results would be from a given data set?
examples of both ok and check please

 

[GrumpyChi]

Hi, yes sure
So the reference tab would have an extract of data like the below

Ref OK? Profit Centre Nominal Account Text
1 ok PC01 560101 1 - Journal posting
7 ok PC02 560202 7 - accrual posting
5 check PC02 560000 5 - Journal posting

The Ref column is just extracting the number from the text column.

The OK? is the part I am struggling with. The workbook basically has this reference tab, along with a number of tabs which are named after each nominal code. So tabs are named "560101", "560202" etc.
The OK? formula is currently checking whether there is a corresponding reference on the subsequent tab. So Ref 1 above is checking the tab named "560101" to see if there is a reference of 1 (which happens to be in column A also). It does work.
The issue is, each tab may have different profit centres on it, so the formula only checks to see whether there is a 1 in the relevant tabs column, regardless of whether the profit centre is PC01 or PC02 etc.
I want it to check specifically whether the subsequent tabs have the relevant reference for that particular profit centre.

An example would be:
Reference tab:
Ref OK? Profit Centre Nominal Account Text
1 ok PC01 560101 1 - Journal posting

Tab named '560101':
Ref Profit Centre Nominal Account Text
1 PC02 560101 1 - Journal posting

The reference tab is still returning 'ok' even though the Profit centre on the tab named 560101 is PC02

Hope this makes sense.

Thank you

 

[ExceLoki]

i think this might help with what i believe you're looking for:

Check if a column has a reference on each tab but using 2 criteria.xlsx
	A	B	C	D
1	Ref	Ok?	Profit Centre	Nominal Account
2	1	ok	7	520000
3	2	ok	7	520000
4	3	check	7	520000
5	4	check	7	520000
6	1	check	8	520000
7	2	ok	8	520000
8	3	check	8	520000
9	4	check	8	520000
10		check	7	520000
11		check	8	520000
12	1	ok	7	520001
13	2	ok	7	520001
14	3	check	7	520001
15	4	check	7	520001
16	1	ok	8	520001
17	2	check	8	520001
18	3	check	8	520001
19	4	check	8	520001
20		check	7	520001
21		check	8	520001
central

Cell Formulas
Range	Formula
B2:B21	B2	=IFERROR(IF(A2="","check",IF(MATCH(1,(C2=INDIRECT(D2&"!b:b"))*(A2=INDIRECT(D2&"!a:a")),0)>0,"ok","check")),"check")

-----------------------

Check if a column has a reference on each tab but using 2 criteria.xlsx
	A	B	C
1	Ref	Profit centre	Nominal Code
2	1	7	520000
3	2	7	520000
4	2	8	520000
5		8
520000

-----------------------

Check if a column has a reference on each tab but using 2 criteria.xlsx
	A	B	C
1	Ref	Profit centre	Nominal Code
2	1	7	520001
3	1	8	520001
4	2	7	520001
520001

 

[GrumpyChi]

You are an absolute genius. This is exactly what I was after, it works a charm!

Thank you so much, it is hugely appreciated.

 

[ExceLoki]

happy to help