+ Reply to Thread
Results 1 to 26 of 26

Help with formula to return a value based on if that expiry date is within year

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Red face Help with formula to return a value based on if that expiry date is within year

    Hi guys,

    Not sure if this can be done in a formula or if it will need a macro but I've got the following happening on a sheet:

    Column A: List of entries
    Column B-L: future dates for each of the values in column A

    What I'd like to happen is a formula where if the current year and a year in one of the dates match then the value from column A will be displayed.

    So if I look at a sheet it automatically lists the different entries in column A that are 'due' this year..

    Hope this makes sense. Any further clarification please do get in touch!

    Thanks a lot!
    Last edited by mw91; 01-08-2015 at 12:01 PM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

    Remember to desensitize the data.

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

    View Pic
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Hi, thanks for your reply SKTNEER.

    I have attached (I hope) an example that may help visualise what I'm trying to do.

    Appreciate your assistance on this!

    Kind regards,
    M
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to return a value based on if that expiry date is within year

    with an filter on column E (and select the year 2015).

    See the attached file.

    I noticed you have merged cells in your file.

    Don't work with merged cells, you get in trouble with it sooner or later.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    Or place the below code on ThisWorkbook module.
    Please Login or Register  to view this content.
    After placing the above code on ThisWorkbook module, save your workbook as Excel-Macro Enabled workbook and then close your workbook. Next time when you open the workbook you will only show the record that have a date which belongs to the current year.

  6. #6
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Thank you for your reply oeldere but that is not what I'm trying to do.


    I may be explaining it incorrectly but what I want is for cell A18, A19 -> Awhatever to change and produce lists based on what is due in the current year

    So lets say I have

    Assignment A due in 2015
    Assignment B due in 2016

    Then in cell A18 it would say Assignment A and that's it

    In the year 2016 cell A18 would then say Assignment B.

    Essentially it is so I do not have to scan the dates in the rows to find what I need for particular years, it just automatically tells me based on the dates.

    Hope this makes sense?

  7. #7
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Appreciate the code sktneer, I see what you have done. I would definitely like to still be able to see the colum with the list though as it is quite critical and there is some conditional formatting in terms of something is coming up in a so many days and is highlighted but the ones this year are red and need to be sorted.

    The only reason is that the columns of dates can be so long and I'd have to scroll far to the right (up and down etc.) to find the date that is highlighted. Whereas if there is a small box that just tells me immediately what needs to be done this year then it helps so much!

  8. #8
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    I understand it's very difficult considering you aren't able to see the sheet in question but it is medically related confidential information and thus am unable to share. I definitely do appreciate the help though.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to return a value based on if that expiry date is within year

    With some helpcolumns and after that index and match.

    See the green cells in the attached file.

  10. #10
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    oeldere, amazing! Thank you so much.

    Works perfectly and from this I can customise to suit my needs.

    Brilliant!

    Thanks!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to return a value based on if that expiry date is within year

    Much thanks for the compliments.

    Glad I could help.

    But I want to say that if I had to work with that file, I would just go for the filteroption (option 1).

  12. #12
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Just a quick one,

    what happens if for instance the 2015 date is not in the E column..?

    How do I get the filter column to pick up on the date and put in a 'yes' to enable it to show up in the results?

    Cheers

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to return a value based on if that expiry date is within year

    For your first question => then there will be no actions found, and that's exactly what you want to know.

    1) Or do you mean if the data is in another column?

    for the second question:

    See the formula in F6 and G6,

  14. #14
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    @mw

    Though oeldere has provided you a working solution and I joined after a while so can I ask a question?
    Is it always col. E where date belonging to the current year exists or it may by in any column from col. B to unknown number of columns?

  15. #15
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    @ both

    It will not always be column E that will have the current year which is the annoying bit as Column E for variable A could have 2015 but Column C for variable B could have 2015 and this would be discounted if the formula only looks at column E...

  16. #16
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Thus, using the file oeldere sent I have screenshotted the issue. I would like the IF function that says 'Yes' for if it is currently equal to the same year to look at the whole row if possible...and if there is a correlation for it to flag up.


    Hm doesn't seem to like my png file. Will upload the Excel again.
    Last edited by mw91; 01-08-2015 at 10:16 AM. Reason: Image re upload

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to return a value based on if that expiry date is within year

    Please post your file in #16 again, because I can't open it.

  18. #18
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    BTW you may try this. The below code will display the desired entries starting from A18 every time once you open your workbook.
    Implement this code as I explained earlier.

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    sktneer, it works perfectly!

    Thank you so much

    Does this look at the entire sheet of dates or a certain parameter? And if only a certain parameter - where in the code would I look to change this to suit? For instance if the dates were actually in a different column in my sheet than in the example.

    Again, thanks!

  20. #20
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to return a value based on if that expiry date is within year

    See the attached file (the formula is changed in the orange cells).

  21. #21
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Thanks oeldere, definitely works but I suppose the issue would then be if the number of columns were in the hundreds. We would not be able to continue adding extra lines in the formula past a certain point? The macro that sktneer has written works, just trying to figure out how to edit some of the variables at the minute.

    Thanks for continuing to support though!

  22. #22
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    Here is the code with the comments.....

    Please Login or Register  to view this content.
    As per your sample workbook, since you want to show the result on row 18 onwards, so you can have max 16 rows for actual data.
    The code looks for last column used in a row with date, so it will check the entire row for the dates.

    Please make the changes in the code as per your requirement if any.

  23. #23
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    Duplicate post..

  24. #24
    Registered User
    Join Date
    11-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Help with formula to return a value based on if that expiry date is within year

    Oh wow, I didn't expect you to input that much detail into explaining the variables! Thank you very much, very helpful indeed

    Wish I could add more rep!

    Thank you sktneer and thank you oeldere for both your contributions.

  25. #25
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    You're welcome mw and thanks for the feedback as well.

  26. #26
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with formula to return a value based on if that expiry date is within year

    Also if you are happy with the solution, edit your thread title and change the smiley used there.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Year To Date Return Formula - automation needed
    By brains in forum Excel General
    Replies: 7
    Last Post: 02-05-2015, 03:04 PM
  2. Need to send email to user on expiry date to return book
    By saleha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2012, 06:03 AM
  3. formula to return a year based on a date range
    By bankster in forum Excel General
    Replies: 12
    Last Post: 03-30-2012, 04:24 AM
  4. Formula to return year of date in a cell
    By mikeburg in forum Excel General
    Replies: 5
    Last Post: 03-25-2007, 05:35 PM
  5. Replies: 7
    Last Post: 05-11-2005, 04:06 PM

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