+ Reply to Thread
Results 1 to 5 of 5

Function like MID, but for formula, not result

  1. #1
    Registered User
    Join Date
    05-31-2012
    Location
    Groenlo, Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    62

    Function like MID, but for formula, not result

    Following situation occurs with an export File used for calculations. Calculations may contain up to 2000 rows.

    I want to determine from each row (preferable with a function) whether the row is for a main-title or subtitle. All Positions are Named "NORMAL" in Column A, Both Main-title and Subtitles are named "Bereich" in Column A. The Subtitle-cells have in common that the Function SUM is being used in Column B, For Main Titles Additions are being used.
    Column A /Column B
    BEREICH /=AU4+AU36
    BEREICH /=SOM(AU7:AU35)
    NORMAL /=D7*F7
    NORMAL /=D8*F8

    I am looking for something like =IF(MID(B2;1;3)="SUM";"SUBTITLE";"") But this just works for text, not for searching within a formula.
    Apart from pasting the formula as text, is there another way to solve this?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Function like MID, but for formula, not result

    Why don't you just look to see if the previous cell contains BEREICH? If your data starts on row 1 then you can't do this for that row, but put this formula in a cell on row 2:

    =IF(AND(A2="Bereich",A3="Bereich"),"Main Title",IF(AND(A2="Bereich",A1="Bereich"),"Sub Title",""))

    then copy down.

    Hope this helps.

    Pete

    EDIT: Just noticed that your profile states that you are in Holland - you will probably need to change the commas to semicolons.

    Pete

  3. #3
    Registered User
    Join Date
    05-31-2012
    Location
    Groenlo, Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    62

    Re: Function like MID, but for formula, not result

    @Pete Unfortunately, by exemption there might be an explanation Row in between (sometimes even more). In that case it wouldn't work, or the code would contain quite a few exemptions.

    In the mean time I found this website http://dmcritchie.mvps.org/excel/formula.htm

    the following code is all I needed:
    Please Login or Register  to view this content.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Function like MID, but for formula, not result

    Okay, glad you found something that works for you.

    Pete

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Function like MID, but for formula, not result

    For information, you can do it without using a UDF, I can think of at least 3 ways.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If the way that the sheet is created could be changed then you could use the fact that the subtotal function will not evaluate the total of another subtotal with something like.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Although that would be prone to inaccuracy from empty / zero value cells.

    The 3rd way would be to invoke GET.CELL from a named range, but I don't have a reference list for the parameters, if I find one later I'll update.

+ 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. Right function for formula not result
    By Sc0tt1e in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-05-2014, 04:55 AM
  2. Error: A function in this formula causes the result to change ...
    By Sally27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2011, 09:36 AM
  3. What is the best formula/function to get this result?
    By Pyrex238 in forum Excel General
    Replies: 11
    Last Post: 07-12-2007, 03:47 AM
  4. IF Function returns result of formula
    By AKlein in forum Excel General
    Replies: 2
    Last Post: 03-02-2006, 12:30 PM
  5. Replies: 0
    Last Post: 01-24-2006, 09:10 AM
  6. How to replace a function with its result or resulting reference in a formula?
    By Dmitry Kopnichev in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-13-2005, 08:05 AM
  7. [SOLVED] How to replace a function with its result or resulting reference in a formula?
    By Dmitry Kopnichev in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-13-2005, 08:05 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