morning all,

I have two worksheets. One worksheet is a comprehensive listing of all my
components ( it's called sheet A), and the second is a summary of the first
sheet, called Summary.

In the verification of my sheet A data, I have a series of sumproduct, and
if equations on the summary sheet to test, and analyze.
On the summary sheet, my total is short, and so I'm doing an analysis to
find out what I'm missing.
I.e., the summary sheet is short by 0.36 units, and I cannot readily id why.
On sheet A, I did a count using sumproduct, and everything appears to be
there-- i.e., I have 63 "ownerships" on sheet A, and 63 "ownerships" on my
Summary sheet.
Sumproduct is listing all of the totals fine, and my if eq's all show true
values.
So, on my sheet A, I decided to try Match to see if anything was missing.
At first I just did a plain Match, and it returned all #N/A errors, telling
me it couldn't find any of the terms I was looking for, but since I know that
at least 62 of the 63 exist, I'm thinking I have a data-type mismatch between
my source cell, and my search array. So, I then tried placing &"" in each
component--
=match(f9&"",'Summary'!$C$8:$C$69&"",0)
After this failed, I removed the &"" from my search array.
This did not work either.
I then tried an if equation as follows.
=if(AND(Match(f9,Summary!$C$8:$C$69,0),Match(E9,Su mmary!$A$8:$A$69,0)),"ok")
Hoping that it'd "force" something to work.
I've used match frequently enough to understand that if it cannot find the
terms sought in the array, from the choice in f9, or e9, it returns an N/A
error.
And in my case, while it appears to not find the terms, I know the terms are
there.
So, after all that-- sorry for those with tired eyes--
1- why is this not catching terms I know exist?
2- what would I use to find the missing elements that I mentioned above?
3- can someone please hand me a shotgun that works on making this computer
do what I'm asking it to? I know, sorry; it just needs to be one that
actually works for making a computer do its job-- sigh..... And yes, I'm
aware that it's most likely operator error.....
sorry, I had to toss a bit of sarcasm in there to lighten my mood a little.
:-)|

scratch my request.
It appears that match does not like blank cells, and I'd never run across
that aspect of it before now.
I guess I wasn't prepared for that one.
amazing what a single post will do to get the mental juices/electrons
flowing.......
turns out that my missing element was a misspelling of a user's name.
Everyone's there now.




"SteveDB1" wrote:

morning all,

I have two worksheets. One worksheet is a comprehensive listing of all my
components ( it's called sheet A), and the second is a summary of the first
sheet, called Summary.

In the verification of my sheet A data, I have a series of sumproduct, and
if equations on the summary sheet to test, and analyze.
On the summary sheet, my total is short, and so I'm doing an analysis to
find out what I'm missing.
I.e., the summary sheet is short by 0.36 units, and I cannot readily id why.
On sheet A, I did a count using sumproduct, and everything appears to be
there-- i.e., I have 63 "ownerships" on sheet A, and 63 "ownerships" on my
Summary sheet.
Sumproduct is listing all of the totals fine, and my if eq's all show true
values.
So, on my sheet A, I decided to try Match to see if anything was missing.
At first I just did a plain Match, and it returned all #N/A errors, telling
me it couldn't find any of the terms I was looking for, but since I know that
at least 62 of the 63 exist, I'm thinking I have a data-type mismatch between
my source cell, and my search array. So, I then tried placing &"" in each
component--
=match(f9&"",'Summary'!$C$8:$C$69&"",0)
After this failed, I removed the &"" from my search array.
This did not work either.
I then tried an if equation as follows.
=if(AND(Match(f9,Summary!$C$8:$C$69,0),Match(E9,Su mmary!$A$8:$A$69,0)),"ok")
Hoping that it'd "force" something to work.
I've used match frequently enough to understand that if it cannot find the
terms sought in the array, from the choice in f9, or e9, it returns an N/A
error.
And in my case, while it appears to not find the terms, I know the terms are
there.
So, after all that-- sorry for those with tired eyes--
1- why is this not catching terms I know exist?
2- what would I use to find the missing elements that I mentioned above?
3- can someone please hand me a shotgun that works on making this computer
do what I'm asking it to? I know, sorry; it just needs to be one that
actually works for making a computer do its job-- sigh..... And yes, I'm
aware that it's most likely operator error.....
sorry, I had to toss a bit of sarcasm in there to lighten my mood a little.
:-)|