One way...

Assuming your sheets are named:

Week 2 Dataset
Week 3 Dataset
Week 4 Dataset

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'Week "
&{2,3,4}&" Dataset'!D:D"),D3))0,"","Cleared")

--
Biff
Microsoft Excel MVP


"Belinda7237" wrote in message
...
I have used a formula:

I have a master sheet that has all unpaid bills. i have added a status
column to the master sheet which this formula in it.

=IF(D3="","",IF(ISNUMBER(MATCH(D3,'Week 2 Dataset'!D:D,0)),"","cleared"))

I run a job on monday of each week from our source system that produces
output of all unpaid bills. the first monday i place the output on week 2
sheet. The formula above matches the invoice numbers and if the invoice
number is not on the updated list (ie. not on the listing imported to week
2
dataset) then the word cleared will be populated on the master- as the
invoice is no longer outstanding.

I have added a week 3 tab, and a week 4 tab so that i can import new
datasets each monday and see what else clears from the master. I want to
update the above formula to match the invoice number from the master to
any
of these sheets as right now it is just looking at week2 dataset sheet-

How do i update my formula to include multiple sheets?