A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

modifying T. Valko ws function



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 08:04 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default modifying T. Valko ws function

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...d-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))


  #2  
Old June 1st, 2010, 08:51 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default modifying T. Valko ws function

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

  #3  
Old June 1st, 2010, 08:57 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default modifying T. Valko ws function

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" wrote in message
news
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...d-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))




  #4  
Old June 1st, 2010, 09:12 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default modifying T. Valko ws function

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

  #5  
Old June 1st, 2010, 09:24 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default modifying T. Valko ws function

Sorry I'm out, think its too complex for me.
  #6  
Old June 1st, 2010, 09:30 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default modifying T. Valko ws function

lol....
got it.
That's why I'm back posting again.
have a good one, and thanks for at least looking at it.

"Max" wrote:

Sorry I'm out, think its too complex for me.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.