+ Reply to Thread
Results 1 to 9 of 9

Move Formulas and Data from 1st Sheet to 2nd Sheet

  1. #1
    Registered User
    Join Date
    06-15-2015
    Location
    Palmyra, Indiana
    MS-Off Ver
    Mac
    Posts
    16

    Move Formulas and Data from 1st Sheet to 2nd Sheet

    Can someone tell me how to move formulas and data from one sheet to another and have the formulas still function on the second sheet?

    I have been using COUNTIF function to calculate totals from columns and rows on a sheet. I want to move the formulas (to include the calculated data) to a second sheet in the same workbook. I want the formula that will now be on the second sheet to keep calculating the data from the rows and columns from the first sheet. I will be adding data daily.

    The block of formulas span 6 rows and 6 columns, with headers across the top of the columns and down the beginning of the rows.

    Thanks for the help!

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    Select the block of formulas, and Cut (Ctrl+X) and Paste it where required.

    It should work..
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    06-15-2015
    Location
    Palmyra, Indiana
    MS-Off Ver
    Mac
    Posts
    16

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    Cutting and pasting does move the formula but #REF appears in the cell, not the actual answer to the calculation of the formula. I need to move the source data and the formula and have the target cell data on the second sheet still formulate from additional entries of criteria that will be added to the source data over time.

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    Please see the file attached, try cutting and pasting..

    If possible attach your workbook sample, so that I can take a look..
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-15-2015
    Location
    Palmyra, Indiana
    MS-Off Ver
    Mac
    Posts
    16

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    Formulas not working on second sheet
    Last edited by ArtDzign; 06-18-2015 at 07:33 AM.

  6. #6
    Registered User
    Join Date
    06-15-2015
    Location
    Palmyra, Indiana
    MS-Off Ver
    Mac
    Posts
    16

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    Nope! The only thing that transferred over was the calculated data, not the formulas. If I add to the source data the numbers on the second sheet do not change!

  7. #7
    Registered User
    Join Date
    06-15-2015
    Location
    Palmyra, Indiana
    MS-Off Ver
    Mac
    Posts
    16

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet


  8. #8
    Registered User
    Join Date
    06-15-2015
    Location
    Palmyra, Indiana
    MS-Off Ver
    Mac
    Posts
    16

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    I managed to use Copy (Ctrl+C) to copy all the calculated data on the source page and then moved to the target location, chose Edit and then Paste Special, All then Paste Link, and it pastes the target data and links the data to the target data on the first page,creates a link to that data and the formulas, but whenever I then delete the first set of target data the target data on page 2 disappears also.

    I have since discovered that I need to rephrase my question.

    I have source data in columns and rows on the source sheet (page 1). I want to create a table that calculates COUNTIF Formulas that includes lots of target data arranged in rows and columns with headers for each row and column on the target sheet (page 2).

    I have already created the target data table that includes the target data on the source sheet (page 1) but I need the target data table to be located on a page 2 in the workbook, not page 1.

    So basically, I want to create a target table on page 2 that contains the source data from page 1.

    I already have already created the target table on page 1, it includes the COUNTIF formulas that are calculating info from the source data on page 1. If the table I have already created can be moved to page 2 that would be great too! I don't want to have to recreate the target table if I don't have to.

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Move Formulas and Data from 1st Sheet to 2nd Sheet

    I told you to Cut, not copy..

+ 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. 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. [SOLVED] Move coulumns data of a sheet to diff other columns of other sheet in same workbook
    By ROHAN999 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-03-2013, 09:22 AM
  3. 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
  4. Move entire row of data from Sheet 1 to sheet 2 when completed date is entered.
    By jt7721 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2013, 11:50 AM
  5. [SOLVED] Move rows of data from one sheet to another based on specific values of sheet
    By buntalan80 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-26-2013, 06:21 AM

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