Prev: why is the feature "share workbook" unavailable to use
Next: IF formula question
From: Steve on 1 Jun 2010 15:04
Howdee all.
I received some help from T. Valko back in January on a complicated
worksheet function. It's been working really great, and today I found that I
wanted to make a modification to it.
So, Biff, if you'd be so kind as to one again help me on this, I'd
really...... appreciate it.

Original discussion. Dates back to January 4, 2010.

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6

My goal today-- if posible-- is to get the names on 3 worksheets, into this
formula, so that I can verify that all are there.
I did try the following, and it did not work.
'=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99+'476APN'!$F$5:$F$14+'478APN'!$F$5:$F$102&"")

The complaint that it came up with was that there was simply an error in the
formula. And instead of selecting the part where the error was, the whole
formula was selected. (it occasionally will select just the part where the
error is.)

What I was hoping to do was to get 3 worksheet's ranges into the Match()
function's portion.

MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0)

I tried placing a comma between each worksheet's ranges, but that just tells
Match to do the next part of its function-- not good.
I then tried plus signs-- still no good.
Lastly, as you can see, I've placed ampersand symbols, hoping that it'd see
that as more data to the function-- also no good.

as I was writing this, I realized that some other aspect might be causing
the failure. I was correct.
However, I still am not getting what I'd thought.

This is my correction for the present.
=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99)-COUNTBLANK('476APN'!$F$5:$F$14)-COUNTBLANK('478APN'!$F$5:$F$102)
(and yes, I am doing the ctrl+shift+enter to activate the array function)

Thoughts, ideas?
I've created a single worksheet with all of the 3 ownerships, from 3
different worksheets. I'd now like to take the previous general equation and
apply it to all 3 of the worksheets.

=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99)


=IF(ROWS(H$3:H3)>G$3,"All Names Accounted
For",INDEX('477APN'!F$5:F$99,SMALL(IF(ISNA(MATCH('477APN'!F$5:F$99&"",C$12:C$51,0)),ROW('477APN'!F$5:F$99)),ROWS(H$3:H3))-MIN(ROW('477APN'!F$5:F$99))+1))


From: Max on 1 Jun 2010 15:51
Think there's probably a simpler way to get whatever you want going with a
helper col or two. Try re-explaining your basic sheet set-up & objectives,
show some sample data and expected results.
--
Max
Singapore
---

From: Bob Phillips on 1 Jun 2010 15:57
Haven't read through that thread, far too long, b ut maybe

=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))
-COUNTBLANK('477APN'!$F$5:$F$99)-COUNTBLANK('476APN'!$F$5:$F$99)-COUNTBLANK('478APN'!$F$5:$F$99)

--

HTH

Bob

"Steve" <Steve(a)discussions.microsoft.com> wrote in message
news:D87C6B43-8C6B-42C1-819B-3D4671812BB9(a)microsoft.com...
> Howdee all.
> I received some help from T. Valko back in January on a complicated
> worksheet function. It's been working really great, and today I found that
> I
> wanted to make a modification to it.
> So, Biff, if you'd be so kind as to one again help me on this, I'd
> really...... appreciate it.
>
> Original discussion. Dates back to January 4, 2010.
>
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6
>
> My goal today-- if posible-- is to get the names on 3 worksheets, into
> this
> formula, so that I can verify that all are there.
> I did try the following, and it did not work.
> '=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99+'476APN'!$F$5:$F$14+'478APN'!$F$5:$F$102&"")
>
> The complaint that it came up with was that there was simply an error in
> the
> formula. And instead of selecting the part where the error was, the whole
> formula was selected. (it occasionally will select just the part where the
> error is.)
>
> What I was hoping to do was to get 3 worksheet's ranges into the Match()
> function's portion.
>
> MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0)
>
> I tried placing a comma between each worksheet's ranges, but that just
> tells
> Match to do the next part of its function-- not good.
> I then tried plus signs-- still no good.
> Lastly, as you can see, I've placed ampersand symbols, hoping that it'd
> see
> that as more data to the function-- also no good.
>
> as I was writing this, I realized that some other aspect might be causing
> the failure. I was correct.
> However, I still am not getting what I'd thought.
>
> This is my correction for the present.
> =SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99)-COUNTBLANK('476APN'!$F$5:$F$14)-COUNTBLANK('478APN'!$F$5:$F$102)
> (and yes, I am doing the ctrl+shift+enter to activate the array function)
>
> Thoughts, ideas?
> I've created a single worksheet with all of the 3 ownerships, from 3
> different worksheets. I'd now like to take the previous general equation
> and
> apply it to all 3 of the worksheets.
>
> =SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99)
>
>
> =IF(ROWS(H$3:H3)>G$3,"All Names Accounted
> For",INDEX('477APN'!F$5:F$99,SMALL(IF(ISNA(MATCH('477APN'!F$5:F$99&"",C$12:C$51,0)),ROW('477APN'!F$5:F$99)),ROWS(H$3:H3))-MIN(ROW('477APN'!F$5:F$99))+1))
>
>


From: Steve on 1 Jun 2010 16:12
hi Max,
You can read the link back to the original discussion to get an idea on what
I was originally trying to accomplish. it's quite detailed, and provided Biff
with enough to make up the two formulas.
This modification is simply an expansion on that original idea.
I.e., I had two worksheets- a source sheet, and a criteria sheet.
Now I have three source sheets, and still a single criteria sheet.

I'm looking at the source sheet's list of names, and comparing that to the
criteria sheet to see if all the names are on both. If I'm missing names, I
get a numeric response for the first formula. The second formula reads the
numeric value of the first, and gives me the names that are missing.

Let me know if you require further clarification.

Thanks for your reply.

"Max" wrote:

> Think there's probably a simpler way to get whatever you want going with a
> helper col or two. Try re-explaining your basic sheet set-up & objectives,
> show some sample data and expected results.
> --
> Max
> Singapore
> ---
>
From: Max on 1 Jun 2010 16:24
Sorry I'm out, think its too complex for me.
 |  Next  |  Last
Pages: 1 2
Prev: why is the feature "share workbook" unavailable to use
Next: IF formula question