+ Reply to Thread
Results 1 to 10 of 10

COUNTIF formula looking for value in the cell rather than reference to that cell.

  1. 05-20-2009, 08:13 AM #1
    barksmith
    barksmith is offline
    Registered User
    Join Date
    07-09-2008
    Location
    Brighton, UK
    Posts
    79

    COUNTIF formula looking for value in the cell rather than reference to that cell.

    Hello,

    I've hit a problem when re-working some of my spreadsheets. Basically I have this bit of code:

    Please Login or Register  to view this content.
    This code is quite self explanitory but I need to link the bit that says 
    Please Login or Register  to view this content.
    to show the contents of a cell, which will be the name of a range I want it to reference.

    The cell I want it to show the contents of, is K4.

    Cell K4 has a data validation list, in it which consists of the headings in the 'Call Report' Sheet. Each Column in the call report is labelled as a range (the range will be what the header is called.

    So, What I need for example is...

    If 'Agent' is selected in the data validation cell, the code should point to the range Agent represents, rather than just pointing at that cell.

    Thanks for your help!

    Matt
    Last edited by barksmith; 05-20-2009 at 09:06 AM.
    Reply With QuoteRegister To Reply
  2. 05-20-2009, 08:24 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Not sure exactly what you mean.. do you just want to replace "John" with a reference to K4:

    e.g. 
    Please Login or Register  to view this content.
    or simplified, non CSE formula:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 05-20-2009, 08:38 AM #3
    barksmith
    barksmith is offline
    Registered User
    Join Date
    07-09-2008
    Location
    Brighton, UK
    Posts
    79

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Sorry, reading that back it wasn't very clear.

    I need that formula to reference different named ranges dependant upon what is in cell K4. Cell K4 is a drop down box based on all the named ranges in my workbook.

    So if I chose Agents in the drop down box the code would then read:

    Please Login or Register  to view this content.
    Then if I chose 'Calldate' from the drop down the code would read:

    Please Login or Register  to view this content.
    at the moment it says:

    Please Login or Register  to view this content.
    So I want this drop down box to define the range which the formula is looking for. I hope that makes it clearer.
    Reply With QuoteRegister To Reply
  4. 05-20-2009, 08:45 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Try:

    Please Login or Register  to view this content.
    confirmed with CSE keys

    or

    Please Login or Register  to view this content.
    confirmed with just ENTER
    Reply With QuoteRegister To Reply
  5. 05-20-2009, 09:01 AM #5
    barksmith
    barksmith is offline
    Registered User
    Join Date
    07-09-2008
    Location
    Brighton, UK
    Posts
    79

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Hmmm, unfortunately not.

    This is the code I'm using then:

    Please Login or Register  to view this content.
    But it just returns a value of 0, when I type Agent in Manually it show a value of 12...

    Is it something to do with formatting maybe?
    Reply With QuoteRegister To Reply
  6. 05-20-2009, 09:04 AM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Sorry.. i didn't take out part of it

    Please Login or Register  to view this content.
    try also the Sumproduct formula... easier to manage.
    Reply With QuoteRegister To Reply
  7. 05-20-2009, 09:04 AM #7
    barksmith
    barksmith is offline
    Registered User
    Join Date
    07-09-2008
    Location
    Brighton, UK
    Posts
    79

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Scrap that... I see what I was doing wrong. 

    Please Login or Register  to view this content.
    Your formula works great now I've stopped being stupid!

    Thanks you very much.
    Reply With QuoteRegister To Reply
  8. 05-20-2009, 09:05 AM #8
    barksmith
    barksmith is offline
    Registered User
    Join Date
    07-09-2008
    Location
    Brighton, UK
    Posts
    79

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    haha, we got there.

    Thanks mate.
    Reply With QuoteRegister To Reply
  9. 05-20-2009, 09:15 AM #9
    Phil_V
    Phil_V is offline
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    Now that it is solved, to hijack the thread slightly, what is the "CSE code" that is mentioned repeatedly here? I've never seen it mentioned before, nor seen that way of writing the formulae.

    Thanks
    Reply With QuoteRegister To Reply
  10. 05-20-2009, 09:33 AM #10
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIF formula looking for value in the cell rather than reference to that cell.

    CSE means CTRL+SHIFT+ENTER.. which is the key combination you have to enter for special "Array Formulas"

    The formula presented here is referred to as a multi-conditional sum formula.. it is like a SUMIF() with multiple conditions...

    The SUMPRODUCT() formula is an alternate way of getting the same results.. but it does not require the special key combination to evaluate it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1