Match formula question
 

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?

Match formula question
 

Offered you some thoughts on your 3 questions in your previous posting. I
received no indications nor feedback.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"Belinda7237" wrote:

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?

Match formula question
 

i did respond back by checking the box at the bottom indicating yes, the post
answered my questions perfectly. i used all three of your recommendations and
accomplished everything i needed at that point.

I have now asked a new question as originally i was just going to overwrite
my data each week onto my week 2 tab, however, to save the integrity of the
data from week to week, i have added another tab for week 3 and for week 4 -
so within my formula on my master i want to be able to add the additional
tabs and its not working for me -

"Max" wrote:

Offered you some thoughts on your 3 questions in your previous posting. I
received no indications nor feedback.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"Belinda7237" wrote:

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?

Match formula question
 

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?

Match formula question
 

"Belinda7237" wrote:
i did respond back by checking the box at the bottom indicating yes, the post
answered my questions perfectly. i used all three of your recommendations and
accomplished everything i needed at that point.

I'm afraid that your "Yes" action above wasn't registered over there

See your post: "non match result" in:
http://tinyurl.com/5o5qmm

Could you try it again in that thread?

I have now asked a new question ...

You could try this:
=IF(D3="","",IF(OR(ISNUMBER(MATCH(D3,'Week 2
Dataset'!D:D,0)),ISNUMBER(MATCH(D3,'Week 3
Dataset'!D:D,0)),ISNUMBER(MATCH(D3,'Week 4 Dataset'!D:D,0))
),"","cleared"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula question
 

Thanks Max - this worked perfectly

"Belinda7237" wrote:

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?

Match formula question
 

That's good. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Belinda7237" wrote in message
...
Thanks Max - this worked perfectly