Match formula question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Belinda7237 - 16 May 2008 17:16 GMT

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?

Reply to this Message

Max - 16 May 2008 18:28 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I have used a formula:
>
[quoted text clipped - 16 lines]
>
> How do i update my formula to include multiple sheets?

Reply to this Message

Belinda7237 - 16 May 2008 18:51 GMT

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 -

> Offered you some thoughts on your 3 questions in your previous posting. I
> received no indications nor feedback.
[quoted text clipped - 19 lines]
> >
> > How do i update my formula to include multiple sheets?

Reply to this Message

Max - 16 May 2008 19:28 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

T. Valko - 16 May 2008 19:11 GMT

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

Signature

Biff
Microsoft Excel MVP

>I have used a formula:
>
[quoted text clipped - 18 lines]
>
> How do i update my formula to include multiple sheets?

Reply to this Message

Belinda7237 - 17 May 2008 02:58 GMT

Thanks Max - this worked perfectly

> I have used a formula:
>
[quoted text clipped - 16 lines]
>
> How do i update my formula to include multiple sheets?

Reply to this Message

Max - 17 May 2008 03:19 GMT

That's good. You're welcome.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks Max - this worked perfectly

Reply to this Message