+ Reply to Thread
Results 1 to 6 of 6

Showing value of linked cells from another sheet without losing calculation in cell

  1. #1
    Registered User
    Join Date
    08-13-2022
    Location
    London, England
    MS-Off Ver
    Office365
    Posts
    2

    Showing value of linked cells from another sheet without losing calculation in cell

    Hello,

    Can anyone help with this scenario:

    Sheet 1 CellA1 has a value of 10 and CellA2 has a value of 3
    Sheet 2 CellA1 has the calculation: =Sheet1A1*Sheet1A3

    I would like to move Sheet 2 to a separate document and break the links to Sheet 1, but showing the formula in the formula ribbon as =10*3 and not the calculated value 30.

    I have multiple cells with these links to different sheets and calculations and will not be able to use F9 function to manually change the link to a value. I would prefer not to use a macro.

    Thank you for your help.

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

    Re: Showing value of linked cells from another sheet without losing calculation in cell

    Sheet 2 CellA1=TEXT(INDIRECT("Sheet1!A1"),"0")&"*"&TEXT(INDIRECT("Sheet1!A2"),"0")
    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.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Showing value of linked cells from another sheet without losing calculation in cell

    You will need ..

    =VALUE(Sheet2!A1)

    so VBA may be your only option
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    08-13-2022
    Location
    London, England
    MS-Off Ver
    Office365
    Posts
    2

    Re: Showing value of linked cells from another sheet without losing calculation in cell

    Thanks for your suggestions.

    Unfortunately:

    Sheet 2 CellA1=TEXT(INDIRECT("Sheet1!A1"),"0")&"*"&TEXT(INDIRECT("Sheet1!A2"),"0")

    returns the following in Sheet 2A1: 10*0

    And when breaking the links between Sheet1 and Sheet2, Sheet2 returns: =VALUE(Sheet2!A1) in the formula ribbon and 0 in the cell.

    The result I am seeking (for clarity) is:

    Formula Ribbon: =10*3
    Value shown in cell: 30

    Essentially I want to replace the links with their values but not lose the calculations in Sheet 2.

    If the answer is that this can only be done with VBA, would also be helpful!

    Thanks!

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Showing value of linked cells from another sheet without losing calculation in cell

    You cannot have both formula and value in the same cell.

    and Formula Ribbpn will never show "=10*3"

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

    Re: Showing value of linked cells from another sheet without losing calculation in cell

    Why not copy the worksheet to another workbook?

    The formula will stay intact.

+ 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. [SOLVED] Cell is showing negative calculation even if cells are not filled.
    By sickreto in forum Excel General
    Replies: 5
    Last Post: 03-02-2022, 03:53 AM
  2. [SOLVED] Linked Cells are not showing exactly the data linked
    By mpinkie182 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2022, 07:52 AM
  3. Linked cells showing #REF when source file is not open
    By charleswc in forum Excel General
    Replies: 3
    Last Post: 05-08-2014, 08:12 PM
  4. Replies: 3
    Last Post: 04-07-2012, 08:47 PM
  5. Replies: 3
    Last Post: 10-01-2009, 07:01 PM
  6. Linked cells between worksheets showing 0-Jan
    By bmunoz64 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-16-2009, 10:17 AM
  7. Linked cell value showing 0 if empty
    By softice11 in forum Excel General
    Replies: 1
    Last Post: 09-17-2006, 08:00 AM

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