+ Reply to Thread
Results 1 to 19 of 19

how to show cells without its formula, only its result

  1. 02-04-2015, 05:22 AM #1
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    how to show cells without its formula, only its result

    Hello,

    i have a workbook. in sheet 1, i have data.
    in other sheets there are calculation formulas with countifs,etc which examines this data in Sheet1.
    when i copy these data in Sheet2, Sheet3, ...... to Notepad and copy them back to these sheets, i m seeing them only as Results, without Formula.
    this is what i want to do.
    i want to write a code for making all these formulated data only as seen results without its formula.

    please have a look to my attached file and u will understand easily.
    how we can a write a code for this?
    Attached Files Attached Files
    Last edited by aaaaa34; 02-04-2015 at 06:05 AM.
    Reply With QuoteRegister To Reply
  2. 02-04-2015, 08:19 AM #2
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: how to show cells without its formula, only its result

    Does this help?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 02-04-2015, 08:52 AM #3
    Brendan_Floyde
    Brendan_Floyde is offline
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: how to show cells without its formula, only its result

    Possible option if you have lots of cells on a sheet - if not many cells probably quicker to do cell by cell as per John above.

    Please Login or Register  to view this content.
    Please consider adding a * if I helped.
    Reply With QuoteRegister To Reply
  4. 02-04-2015, 10:14 AM #4
    buran
    buran is offline
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: how to show cells without its formula, only its result

    Quote Originally Posted by Brendan_Floyde View Post
    Possible option if you have lots of cells on a sheet - if not many cells probably quicker to do cell by cell as per John above.
    Of course it's not necessary to do it cell by cell, e.g. something like:

    Please Login or Register  to view this content.
    Always better than using copy/paste which involves the Clipboard.
    However if you use copy/paste, then use

    Please Login or Register  to view this content.
    at the end
    If you are pleased with a member's answer then use the Star icon to rate it.
    Reply With QuoteRegister To Reply
  5. 02-04-2015, 10:15 AM #5
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    brendan,

    i tried ur code but it didnt work
    can u update ur code for sheet name "over 2.5" please ?
    Reply With QuoteRegister To Reply
  6. 02-04-2015, 10:22 AM #6
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    buran,

    u really shared sth too useful but u didnt write the code full i think
    can u write those 2 codes with full lines , please ?
    Reply With QuoteRegister To Reply
  7. 02-04-2015, 10:25 AM #7
    buran
    buran is offline
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: how to show cells without its formula, only its result 

    Please Login or Register  to view this content.
    Place this macro in a module and run it. It will replace all formulas on the active sheet with respective values.
    Reply With QuoteRegister To Reply
  8. 02-04-2015, 10:41 AM #8
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: how to show cells without its formula, only its result

    Using the entire usedrange.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 02-04-2015, 10:46 AM #9
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    buran why this error happening ?
    please have a look to pictures
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  10. 02-04-2015, 10:53 AM #10
    buran
    buran is offline
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: how to show cells without its formula, only its result

    Sorry, my mistake. It should be

    Please Login or Register  to view this content.

    And This one

    Please Login or Register  to view this content.
    .

    will loop trough each sheet and will convert all formulas to values
    Reply With QuoteRegister To Reply
  11. 02-04-2015, 10:55 AM #11
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    Quote Originally Posted by john h. Davis View Post
    using the entire usedrange.

    Please Login or Register  to view this content.

    john super ya!!! Super!!!
    Perfecto code!!!
    Reply With QuoteRegister To Reply
  12. 02-04-2015, 11:04 AM #12
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    BRAVO BURAN!!!

    Both 2 codes worked super too!!!
    U all my friends really helped me so much!!!
    Thanks a lot!!!
    Reply With QuoteRegister To Reply
  13. 02-04-2015, 11:06 AM #13
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    Quote Originally Posted by aaaaa34 View Post

    john super ya!!! Super!!!
    Perfecto code!!!
    only John,
    sorry but can u edit this code also for all worksheets in that workbook except 2 sheets called "fixture" and "results"
    Reply With QuoteRegister To Reply
  14. 02-04-2015, 11:09 AM #14
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    Quote Originally Posted by buran View Post
    And This one

    Please Login or Register  to view this content.
    .

    will loop trough each sheet and will convert all formulas to values
    also this code Buran,
    how u can edit that , code ll work for all sheets except "fixture" and "results"
    Reply With QuoteRegister To Reply
  15. 02-04-2015, 11:18 AM #15
    buran
    buran is offline
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: how to show cells without its formula, only its result 

    Please Login or Register  to view this content.
    Last edited by buran; 02-05-2015 at 10:48 AM.
    Reply With QuoteRegister To Reply
  16. 02-04-2015, 11:33 AM #16
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: how to show cells without its formula, only its result 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  17. 02-05-2015, 10:33 AM #17
    aaaaa34
    aaaaa34 is offline
    Forum Contributor aaaaa34's Avatar
    Join Date
    12-25-2013
    Location
    Istanbul
    MS-Off Ver
    Office365
    Posts
    388

    Re: how to show cells without its formula, only its result

    Quote Originally Posted by buran View Post 
    Please Login or Register  to view this content.
    buran, my last last last request from you,
    can you modify the code please for all opened active workbooks in that moment? (again it ll not run for "fixture,results" sheets)

    Quote Originally Posted by JOHN H. DAVIS View Post 
    Please Login or Register  to view this content.
    JOHN, please for ur code too, i need ur modify for all opened workbooks (except "fixture, results" sheets)
    Reply With QuoteRegister To Reply
  18. 02-05-2015, 10:45 AM #18
    buran
    buran is offline
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: how to show cells without its formula, only its result

    I didn't test it, but I think it should work

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  19. 02-05-2015, 02:38 PM #19
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: how to show cells without its formula, only its result

    Modified:

    Please Login or Register  to view this content.
    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. Make macro on copied sheet refer to the new sheet?
    By ADJetz in forum Excel General
    Replies: 2
    Last Post: 11-27-2014, 10:57 AM
  2. Make cells in Summary sheet equal SAME cell in DIFFERENT places on other respective sheets
    By AndrewRZ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2013, 11:01 AM
  3. Make cells from sheet 1 automatically delete if duplicate data is entered in other sheets?
    By pbreeze in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2013, 08:01 PM
  4. Template sheet changes carry over into copied sheets
    By jermsalerms in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2007, 05:24 PM
  5. How do I make a 5th sheet to total cells from previous sheets in .
    By Chuck-Baby in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2005, 12:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1