Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match with yes or no question | Excel Worksheet Functions | |||
INDEX/MATCH formula question | Excel Discussion (Misc queries) | |||
Question about the "Match" formula | Excel Discussion (Misc queries) | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
MATCH Question | Excel Worksheet Functions |