+ Reply to Thread
Results 1 to 13 of 13

Assemble formula from texts in other cells

Hybrid View

  1. 12-19-2014, 03:41 AM #1
    13gentj
    13gentj is offline
    Registered User
    Join Date
    12-14-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    12

    Assemble formula from texts in other cells

    So I'd prefer to do this with just formula's if at all possible. Here's the situation. In one cell, the user can input the name of an employee. Another cell refers to that cell and removes the space. Cells around that contain "=", "countif(D20:E25", and "). I am using "=C5&C6&C7&C8" To assemble these all into a single formula "=countif(D20:E25,"JohnSmith") but the cell that those are assembled in only displays the text and won't use the text as a formula. I was trying to copy it and past it in another cell as just "value" but it still is only read as the formula "=C5&C6&C7&C8". How can I get the cell to read it as a formula?

    Thank you,
    Joel
    Reply With QuoteRegister To Reply
  2. 12-19-2014, 03:51 AM #2
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: Assemble formula from texts in other cells

    Attach sample excel workbook with desired result
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  3. 12-19-2014, 03:57 AM #3
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Assemble formula from texts in other cells

    This removes spaces

    =SUBSTITUTE(C5," ","")
    This counts the text
    =COUNTIF(d20:e25,D5)
    Last edited by mehmetcik; 12-19-2014 at 04:05 AM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  4. 12-19-2014, 04:04 AM #4
    13gentj
    13gentj is offline
    Registered User
    Join Date
    12-14-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    12

    Re: Assemble formula from texts in other cells

    Yeah. Here you go.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 12-19-2014, 04:07 AM #5
    13gentj
    13gentj is offline
    Registered User
    Join Date
    12-14-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    12

    Re: Assemble formula from texts in other cells

    Thank you guys for being so quick to help. I thoroughly appreciate it.
    Reply With QuoteRegister To Reply
  6. 12-19-2014, 04:43 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: Assemble formula from texts in other cells

    =CONCATENATE(C3,D3,E3,F3)
    Why you are using this formula
    Concatenate will Joins the selected cells as text only
    Reply With QuoteRegister To Reply
  7. 12-19-2014, 01:59 PM #7
    13gentj
    13gentj is offline
    Registered User
    Join Date
    12-14-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    12

    Re: Assemble formula from texts in other cells

    I originally used = & to join them but it did the same thing. Is there a way I can do it that doesn't select the text only?
    Reply With QuoteRegister To Reply
  8. 12-19-2014, 05:25 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Assemble formula from texts in other cells

    Removed by FR
    Last edited by FlameRetired; 12-19-2014 at 05:31 PM.
    Reply With QuoteRegister To Reply
  9. 12-19-2014, 09:57 PM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assemble formula from texts in other cells

    You can't "build" a formula in this manner.

    You can still do it with an added step.

    Let's assume this formula in cell A2: =C5&C6&C7&C8

    Returns the TEXT string: =countif(D20:E25,"JohnSmith")

    With the cell selected do:

    Right click>copy
    Right click>Paste Special>Values

    CTRL H (this opens the Find/Replace userform)
    Find what: =
    Replace with: =
    Replace all

    The cell should now contain the formula: =COUNTIF(D20:E25,"JohnSmith")

    However, this is kind of going around in circles when you could have just typed in the COUNTIF formula.

    What you need is a UDF (User Defned Function).

    Put this code into a general module:

    Function EVAL(Equation As String) As Variant
  Application.Volatile
  EVAL = Evaluate(Equation)
End Function
    Then, this formula entered in some cell:

    =EVAL(A2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  10. 12-19-2014, 10:18 PM #10
    13gentj
    13gentj is offline
    Registered User
    Join Date
    12-14-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    12

    Re: Assemble formula from texts in other cells

    Awesome. That UDF works well, the only thing is that I need the formula that is constructed in the cell to be able to be copied and pasted in multiple places throught the workbook, not simply refering to those cells because when those cells change, this copied formula will change too. :/ The reason I can't just construct it myself is that my fellow supervisors don't know excel as well and would have to learn a lot to go through the process of adding new employees to this multi-sheet, semi-complex document (not the one attached but part of it is similar). Also, it'd consume a lot of time to input one employee into the whole workbook at a time.
    Reply With QuoteRegister To Reply
  11. 12-19-2014, 10:28 PM #11
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assemble formula from texts in other cells

    Not sure I'm following you.

    Do you mean...

    If this is the formula: =C5&C6&C7&C8

    And when you copy/paste it, you want it to ALWAYS refer to the same cells: =C5&C6&C7&C8 ?
    Reply With QuoteRegister To Reply
  12. 12-19-2014, 10:52 PM #12
    13gentj
    13gentj is offline
    Registered User
    Join Date
    12-14-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    12

    Re: Assemble formula from texts in other cells

    I'm sorry for not being clear. So when I use =C5&C6&C7&C8 to assemble a formula, the components of that formula are continuously dependent on the cells that the assemble formula (=C5&C6&C7&C8) refers to. I didn't want that. I wanted the assembly to be completely independent. But I just tried the search and replace idea replaceing "=" with "=" and it worked like a charm. I'm just using a simple macro to do that. So thank you so much for helping me, all, especially you Tony Valko.

    Thank you,
    Joel
    Reply With QuoteRegister To Reply
  13. 12-19-2014, 11:00 PM #13
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assemble formula from texts in other cells

    Good deal. Thanks for the feedback!
    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. [SOLVED] To average cells included texts - how to ignore texts in those cells - help please
    By PrayoonD in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-23-2014, 12:32 PM
  2. [SOLVED] Array formula to count cells matching various texts
    By pdanes in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-17-2014, 12:46 PM
  3. Append texts into one string from 4 cells that each cell has a formula
    By Rocky2013 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-28-2013, 09:57 AM
  4. Assemble table in new w/s from rows selected in other w/s
    By prawer in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-27-2009, 03:46 PM
  5. assemble link from different cells and return value
    By tastic96 in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 12:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1