+ Reply to Thread
Results 1 to 9 of 9

Formula to move data to another sheet

  1. 05-23-2014, 12:08 PM #1
    utahguy9384
    utahguy9384 is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Utah
    MS-Off Ver
    365 ver. 2210
    Posts
    34

    Formula to move data to another sheet

    Hi, What i'm hoping to do is find a way to move data to another worksheet.

    I have a spreadsheet that has multiple sheets, I have one sheet called "Econ" that has a column title "Completed" that is a "YES" or "NO" column. What I would like to be able to do is anything in that has a "NO" in the cell have it move to the worksheet called "NextMonth" and anything with a "YES" in the cell move it to the worksheet called Completed. I have attached a sample of the spreadsheets

    Thanks in advance for your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-23-2014, 12:25 PM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to move data to another sheet

    Hi

    Try this ARRAY formula in A2 of "NextMonth" sheet.

    Drag down and to the right. Format your date column.

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


    -- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
    Not just Enter.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 05-23-2014, 12:30 PM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,791

    Re: Formula to move data to another sheet

    Does it need to be an .xls file, or can it be .xlsx ? There are formulae which can only be used in later versions of Excel, and as your version is 2010 according to your profile then you can use the later formats.

    Pete
    Reply With QuoteRegister To Reply
  4. 05-23-2014, 12:44 PM #4
    utahguy9384
    utahguy9384 is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Utah
    MS-Off Ver
    365 ver. 2210
    Posts
    34

    Re: Formula to move data to another sheet

    yes it can be .xlsx as well
    Reply With QuoteRegister To Reply
  5. 05-23-2014, 01:02 PM #5
    utahguy9384
    utahguy9384 is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Utah
    MS-Off Ver
    365 ver. 2210
    Posts
    34

    Re: Formula to move data to another sheet

    @fortis1991, thank you for this formula it works but it only copys the first row all the way down, is there something i'm missing?
    Reply With QuoteRegister To Reply
  6. 05-23-2014, 01:10 PM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to move data to another sheet

    YES. You need to confirm it as an ARRAY formula, as i mentioned in my previous post(Gollow that link foe more details).

    Edit:    Sorry. use like this.

    =IFERROR(INDEX(Econ!A$2:A$1000,SMALL(IF(Econ!$H$2:$H$1000="NO",ROW(Econ!A$2:A$1000)-1),ROW(Econ!A1))),"")
    Last edited by Fotis1991; 05-23-2014 at 01:11 PM. Reason: Edit
    Reply With QuoteRegister To Reply
  7. 05-23-2014, 01:13 PM #7
    utahguy9384
    utahguy9384 is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Utah
    MS-Off Ver
    365 ver. 2210
    Posts
    34

    Re: Formula to move data to another sheet

    Never mind figured it out, oh and I see you just posted the answer, thank you for your help I really do appreciate it.
    Reply With QuoteRegister To Reply
  8. 05-23-2014, 01:18 PM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to move data to another sheet

    You are welcome and thanks for the feedback.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  9. 05-23-2014, 01:28 PM #9
    utahguy9384
    utahguy9384 is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Utah
    MS-Off Ver
    365 ver. 2210
    Posts
    34

    Re: Formula to move data to another sheet

    @Fotis1991 thank you for all the help, this place is great
    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)

Similar Threads

  1. VBA code to move data from a entry sheet to a historical sheet by date
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-17-2014, 07:15 PM
  2. Split Data Unique Value From 2 Sheet of A column and Move to 2 Sheet with Splited Values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 04:21 AM
  3. Macro-Move data from Data Entry update master sheet using formula to allocate to details
    By EXCEL0429 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2013, 08:08 AM
  4. [SOLVED] Excel formula to automatically move data to a new sheet when criteria is met
    By ktismename in forum Excel General
    Replies: 12
    Last Post: 07-25-2012, 09:35 AM
  5. Need formula to make row move to another sheet when a particular cell has data
    By jondon in forum Excel General
    Replies: 4
    Last Post: 01-25-2012, 03:18 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1