+ Reply to Thread
Results 1 to 16 of 16

Test if a cell is within a name range and return a text value based on the test

  1. #1
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Test if a cell is within a name range and return a text value based on the test

    Hi,
    I have a simple worksheet containing job review data, the columns relevant to this question are the 'Date Completed', 'Month' and 'Performance Review'

    Column F = Date Completed contains a date (dd/mm/yyyy) that the job was completed.
    Column H = Performance Review contains a percentage that was given for the performance review.
    I also have a seperate sheet containing each date within the month which I have then created name ranges for, refJAN, refFEB, refMAR etc.

    This is the code I have been using in the first row of data (row 2);

    [=IF(F2=refJAN,"JAN",IF(F2=refFEB,"FEB",IF(F2=refMAR,"MAR",IF(refAPR,"APR",IF(F2=refMAY,"MAY",IF(F2=refJUN,"JUN",IF(F2=refJUL,"JUL",
    IF(F2=refAUG,"AUG",IF(F2=refSEP,"SEP",IF(F2=refOCT,"OCT",IF(F2=refNOV,"NOV",IF(F2=refDEC,"DEC","ERROR")))))))))))) + CTRL + SHIFT + ENTER]

    I have placed the word "ERROR" as the last false statement to ensure the entire statement is being run.
    Now if the date in column F is the 1st of the month it will return the correct value, but any dates past the 1st return with "ERROR".

    The name range cells are also in the dd/mm/yyyy format, I obvioulsy have to do some sort of range statment when refering to the name ranges, but not sure how to do this.

    Appreciate the help.

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Test if a cell is within a name range and return a text value based on the test

    Moderator's Note:

    Welcome to the forum.
    Attach a sample workbook to better explain your query.

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Thanks for your reply, see attached a modified version of the spreadsheet.
    I have only removed the sensitive data.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Test if a cell is within a name range and return a text value based on the test

    Are you just displaying Jan. Feb.....with regards to the month in cell F2 down?
    try this first If i understand you correctly without using your table

    =CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

    Tell me if this is it.
    Thanks.

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Test if a cell is within a name range and return a text value based on the test

    Do you need only check how to check a data exits within range or
    below formula is enough to bypass the route..

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  6. #6
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Apologies, the code I entered above has a fault, it should read;

    [=IF(F2=refJAN,"JAN",IF(F2=refFEB,"FEB",IF(F2=refMAR,"MAR",IF(F2=refAPR,"APR",IF(F2=refMAY,"MAY",IF(F2=refJUN,"JUN",IF(F2=refJUL,"JUL",
    IF(F2=refAUG,"AUG",IF(F2=refSEP,"SEP",IF(F2=refOCT,"OCT",IF(F2=refNOV,"NOV",IF(F2=refDEC,"DEC","ERROR")))))))))))) + CTRL + SHIFT + ENTER]

  7. #7
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Quote Originally Posted by Debraj Roy View Post
    Do you need only check how to check a data exits within range or
    below formula is enough to bypass the route..

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hi Debraj Roy, can you please explain your formula and clarify what you mean with your question?
    Thanks

  8. #8
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Test if a cell is within a name range and return a text value based on the test

    however, below formula will do the same..
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Quote Originally Posted by Debraj Roy View Post
    however, below formula will do the same..
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Quote Originally Posted by vlady View Post
    Are you just displaying Jan. Feb.....with regards to the month in cell F2 down?
    try this first If i understand you correctly without using your table

    =CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

    Tell me if this is it.
    Thanks.
    That's great, how simple. Both your formulas work perfectly.
    Thanks so much.

  10. #10
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Test if a cell is within a name range and return a text value based on the test

    Nice to be of help..
    another one is just simply type

    =f2

    then format as "mmmm" this will give you also the month

  11. #11
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Can I ask another issue now that formula is fixed? It is still in relation to this spreadsheet and links with the formula we just fixed.

  12. #12
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Test if a cell is within a name range and return a text value based on the test

    If it is still relevant why not. If it is not you can create another thread to have another discussion.

    and thanks also for the star tap.
    Regards,
    Vladimir

  13. #13
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Ok, on that same spreadsheet I have put this formula in column I to create a numeric value that can be added up.
    [=IF(H2>0,"1","")]

    Then in the section to the right in column J I have numeric values corresponding to the adjacent month. However I can not get the forumula to sum column I where the corresponding cell in column G is the relevant month;
    [=SUMIFS(I:I,G:G,"JAN")]

    This formula consistently returns a value of 0.

    Thanks.

  14. #14
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Test if a cell is within a name range and return a text value based on the test

    first get rid of the quotes on the number 1
    =IF(H2>0,"1","") ->since this will be treated as text.
    second you could try the sumif alone since you used sumifs

  15. #15
    Registered User
    Join Date
    06-23-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Test if a cell is within a name range and return a text value based on the test

    Quote Originally Posted by vlady View Post
    first get rid of the quotes on the number 1
    =IF(H2>0,"1","") ->since this will be treated as text.
    second you could try the sumif alone since you used sumifs
    Hey presto you've solved it again!!
    That was so obvious after you mentioned it.
    Thanks again.

  16. #16
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Test if a cell is within a name range and return a text value based on the test

    Your welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1