Posts by mramazan

    Thanks for the reply. Simmilar SUMPRODUCT solution was suggested by Tom and I have implemented it in my project but now I am interesting in VBA solution (my interest for learning VBA has been increased !!!).

    So, please provide me guidlines for using VBA. I will be much obliged.

    Once again I will thank for all the generous contributors of this forum.


    Originally posted by mramazan
    I am not much familiar with VBA programming (I can just record & edit SIMPLE macros) but now I am seriously thinking over learning it.

    Thanks guys for your help. Now, I have started taking interest in VBA.

    So, the solution provided by Javy Dreamer worked well and solved purpose But I have more than 100 different cell in which I will use this function by changing arguments but my first argument E1 which is a range B3 is common at all places.

    I want to do somethis like this:

    getMatch(E2, A2:A21,B2:B21)

    But when I use this function, I get error message in VBA "invalid outside procedure"

    Please guide to a new student of VBA.



    Originally posted by Pesky Weasel
    The link may well be in one of the named ranges within the book. Have a look at Insert - Name - Define and check out the refernces any names.

    Weasel !

    You are absolutely right. There were certain (un-necessary) named ranges which were referring to external links !!!.

    I deleted the names and now the links have been vanished.

    Thanks for the help.

    My excel file shows links to my old/deleted files. I have searched the whole worksheet to find some formula having these links but could not succeed.
    Even I have converted the entire contents of worksheet into to VALUE but links are being shown in > EDIT > LINKS menu.

    How can I trace where these links exist ? and how can I remove these links ?.


    Dear Tom & Dave,

    Thanks for your replies.

    It will take me sometime to check and implement your suggestions because I have my file at my office computer and due to weekend, I will not reach office before Monday morning.

    Then I will get back with the results.


    Originally posted by thomach
    As a side comment, your columns E and F are redundant. Column E already provides that info in column F -- just use a 1 or 0 or an M or F in column E.

    Infact, my actual database/worksheet has many columns which have logical data (1 or 0) and it is a bit complex also. So, I created this example just to simplify my question.

    Thanks for your help.

    My Excell worksheet has data in 6 columns (A, B, C, D, E, F) and 20 rows.

    Header row contains Name, City, Age, Young, Male, Female

    Columns D, E, & F contain numeric data only consisting 1 or 0 (to show YES or NO)

    Now, I want to make summary of this database based upon the following conditions (for example):

    Count number of rows if:
    --- column A (range) = "Name" AND (column D (range) = 1 OR column F (range) = 1)

    (I want to know how many persons of same name have "1" in any of D, E columns i.e. number persons of same name who are either young or male or both )

    --- Also number of persons of same name who are "young" but NOT male

    I want to use SUMPRODUCT but do not know how to use it to get result similar to AND, OR, NOT.

    Thanks for your help.

    Dear Tom & Javy Dreamer,

    Many thanks for your quick replies. As advised by you, my problem has been solved by using SUMPRODUCT function.

    Also thanks for guiding me regarding VBA module but at this stage, I am not much familiar with VBA programming (I can just record & edit SIMPLE macros) but now I am seriously thinking over learning it.

    I am a new member on this forum. I searched it through google (to solve my problem) and now I have found the forum members very cooperative and helpful. I will continue visiting this forum for my learning (may be only as a reader).

    Once again, thanks to every member who is helping others.


    I want to count number of rows which satisfy the conditions on two columns.

    My Excell worksheet has data in 3 columns (A, B, C) and 20 rows.

    First row is header/label row i.e. Name, City, Age .

    Other rows contains data for several persons.

    Now, I want to make summary of this database based upon the following condions (for example):

    - count number of rows in column A if data in range A1:A20 contains/equal to "specific-name" AND data in range B1:B20 contains/equal to "specific-city"

    I want to enter this "specific-name" in Cell E1 and "specific-city" in Cell E2. I want to get result in Cell E3.

    Can anyone help me making this formula.