Match formula question


B

Belinda7237

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?
 
Ad

Advertisements

M

Max

Offered you some thoughts on your 3 questions in your previous posting. I
received no indications nor feedback.
 
B

Belinda7237

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 -
 
T

T. Valko

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")
 
M

Max

Belinda7237 said:
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"))

---
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top