+ Reply to Thread
Results 1 to 5 of 5

Creates renewal list based on values.

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    10

    Creates renewal list based on values.

    Hi,

    I have been racking my brain to figure a way to do this - and any advice would be greatly appreciated.

    Basically I have a spreadsheet of client accounts - The accounts have various lengths: 1 year, 2 year & 3 year. Currently I am working on 2018 accounts, and I would like to copy data from '2018 Spreadsheet' to '2019 Spreadsheet' based on the expiry date as follows:

    IF (END DATE = 2019, COPY COLUMN B : COLUMN C) & COPY END DATE + 1 (To create the new 2019 start date)

    I have tried to use VLOOKUP however when the expiry date is not 2019, the rows remains blank.

    Kind regards
    Greg
    Attached Files Attached Files

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Creates renewal list based on values.

    Hi B2,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    drag it down and across...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Click just below left if it helps, Boo?ath?

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Creates renewal list based on values.

    Do also with helper column (F) in "2018 Spreadsheet" =year(E2)
    In "2019 Spreadsheet" change "A1" as 2019

    In "2019 Spreadsheet" cell "B2" :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy paste across change formatting as per requirement.
    Ensure press [SHIFT+CTRL+ENTER]
    Refer attach file.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Registered User
    Join Date
    11-10-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    10

    Re: Creates renewal list based on values.

    Thanks for your helpful formulas - both work a treat!.

    Is it possible to amend this formula to extend to other tabs? So that it pulls information from 2/3 different cells?

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Creates renewal list based on values.

    Yes possible. You may manually input desired result in respective cell.

+ 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. List of extracted unique values creates duplicates (?!)
    By excelboo7891 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-17-2018, 05:12 PM
  2. Replies: 8
    Last Post: 06-13-2017, 08:32 AM
  3. Replies: 7
    Last Post: 09-14-2014, 05:02 AM
  4. [SOLVED] Macro that creates template based on list
    By nofzinger in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-08-2013, 05:20 PM
  5. [SOLVED] Macro that creates sheets based on list
    By chicagoland8 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-08-2012, 06:43 PM
  6. Replies: 11
    Last Post: 02-13-2012, 06:10 PM
  7. Replies: 2
    Last Post: 06-17-2010, 08:36 PM

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