+ Reply to Thread
Results 1 to 10 of 10

Display expanded formula in cell in stead of cell references

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Display expanded formula in cell in stead of cell references

    Hello, In a cell, instead of displaying:

    =SUM(U30+AF30)

    would it be possible to quickly display what U30 and AF30 are within the cell? like so:

    =(SUM(Database!U3)-SUM(Database!I3,Database!K3,Database!Q3))-(SUM(Database!I3,Database!K3,Database!Q3))


    What I'm trying to do is eliminate the first level of cell references for all the cells in a table and just have them display the databases they are referring too like in the second line. Manually copying and pasting the references over would take a while.

    Thank you.
    V

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Display expanded formula in cell in stead of cell references

    Sorry there isn't an easy way to do that.

    If you press F9 when U30 or AF30 are selected then they will evaluate to the value from the referenced cell, not the formula.

    Perhaps if you explained a little more some other method can be found.

    By the way, you mention a table.

    Is it an actual table or just a range?

    If it was a table then there might be easier ways to reference it.

    http://office.microsoft.com/en-gb/ex...010155686.aspx
    If posting code please use code tags, see here.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Display expanded formula in cell in stead of cell references

    Hi and welcome to the forum

    Im thinking that a bunch of =SUM(U30+AF30) is far more efficient than a bunch of =(SUM(Database!U3)-SUM(Database!I3,Database!K3,Database!Q3))-(SUM(Database!I3,Database!K3,Database!Q3))

    and anyway, if you are just using 1 cell, you cont need to use SUM()...
    =Database!U3-SUM(Database!I3,Database!K3,Database!Q3)-SUM(Database!I3,Database!K3,Database!Q3)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Display expanded formula in cell in stead of cell references

    self-deleted duplicate post

  5. #5
    Registered User
    Join Date
    06-24-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Display expanded formula in cell in stead of cell references

    Hi Norie, basically two tables reference the database directly, a third table takes the difference of those two tables by referencing the cells in them [the =SUM(U30+AF30].

    I want to eliminate the first two database ref tables and bring the database references to the third table and take the difference between them directly in the third table, instead of pointing to two other tables.

    Not sure If i'm explaining it clear enough, hopefully there's a way to quickly do this.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Display expanded formula in cell in stead of cell references

    Can't you just enter the formula with references directly to the third table and then copy down?

  7. #7
    Registered User
    Join Date
    06-24-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Display expanded formula in cell in stead of cell references

    I wish I could but unfortunately the areas references areas of the database aren't consistent, it pulls from different areas down through the two tables

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Display expanded formula in cell in stead of cell references

    Maybe if you explained - in some detail - what you are trying to do, and showed us samples of your data and a few sample answers, we would be better able to offer suggestions?

  9. #9
    Registered User
    Join Date
    06-24-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Display expanded formula in cell in stead of cell references

    Sorry for not elaborating more. So, there are two tables (ignore the div/o etc..) both these tables reference a database and I want to get rid of them. These two tables are subtracted/added from each other in a third table. I want to get rid of the first two tables and move all the equations to the 3rd new table. so right now the 3rd table says =SUM(U28+AF28), as in cell U28 of table one plus cell AF28 of table two. I want table 3 (grey highlighted cell) to just reference and add/subtract the underlying database directly that right now flow to U28 and AF28. Re-referencing the database would be very time consuming. Was wondering if I could just pluck the references from the first two tables and plug them into the 3rd table in a quick way.
    Attached Images Attached Images

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Display expanded formula in cell in stead of cell references

    It will make it far easier if you upload a sample workbook, rather than pictures of it. Pictures are impossible to edit, and no=one wants to retype your data

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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