+ Reply to Thread
Results 1 to 3 of 3

Relative Indirect Formula Referencing?

  1. #1
    Damian
    Guest

    Relative Indirect Formula Referencing?

    I have many types of data that need tracking, but I want a universal
    log/report to distribute, so I created a workbook to accomplish this:

    The workbook contains 13 worksheets. The first one is named "Settings".
    The other twelve are named "MMMM YYYY" for the 12 months of the year. The
    monthly worksheets reference the "Settings" worksheet for everything except
    the data the users enter: the report headers, column names, summary labels,
    and validation tables.

    But is something Driving me crazy: Referencing Formulas!

    ===REFERENCING FORMULAS===
    I have created "Master Formulas" area in cells A20:H24 with columns A~B
    merged & C~H merged to create two fields. In the first I have the formula
    names, which are relative to the column names the user chooses for their log.
    In the second field I have formulas (sans the "=" so they are interpreted as
    TEXT). For simplicity let's just focus on cell C20, which contains the first
    formula.

    I want to be able to use the formula for calculations on the monthly
    worksheets for data in their respective rows. I cannot figure out how to
    make Excel SEE my formula nor have I figured out how to make the cells
    referencing the formula use their own address for the calculations (instead
    of the address of the master formula cells).

    I've tried using the INDIRECT function in tandem with the ADDRESS, ROW, and
    COLUMN functions but to no avail. No matter what the formula is in cell
    Settings!C20, I cannot come up with a formula in Column H of "January 2005"
    that can grab the formula in C20, change the relative address values to match
    its location, and use it to calculate/manipulate data in columns A~I.

    The only pathetic thing I've been able to come up with all day has been
    variants of the following:

    Settings!C20 Equals:

    "IF(ISNUMBER(ADDRESS(ROW(),COLUMN(),4,1)),"ADDRESS(ROW(),COLUMN(),4,1)*OFFSET(ADDRESS(ROW(),COLUMN(),4,1),0,1))",(IF(ISBLANK(ADDRESS(ROW(),COLUMN(),4,1)),"",(IF(ADDRESS(ROW(),COLUMN(),4,1)="ALL DAY",10,"?")))))"

    January2005!H8 Equals:

    CONCATENATE("=",Settings!C20)

    I've seen a lot of geniuses on this board so I'm hoping someone has an idea
    to make this work. Thanks!

    Damian


  2. #2
    Dave R.
    Guest

    Re: Relative Indirect Formula Referencing?

    I don't think it's possible to have a calculation get a function name from
    some other cell.. (?)

    Indirect is how you would grab letters and numbers to be used in cell/range
    references, but you probably already know that.

    It might be easier if you tested what you were trying to do on a very small
    range, one piece at a time. It would also make it easier to interpret what
    you're trying to do for those reading this NG, if you explained it on that
    scale. If you don't get anywhere with your current formula, try it on a very
    very small scale and post it here and people will probably know very quickly
    what you're trying to do.










  3. #3
    Registered User
    Join Date
    01-07-2005
    Location
    Seattle, WA
    Posts
    18
    The following code will create a user defined function that retreives the formula from the cell it is passed:

    Function GetFormula(Rng As Range)
    Application.Volatile
    GetFormula = Right(Rng.Formula, Len(Rng.Formula) - 1)
    End Function

    If you still want C20 NOT to contain the "=" then you don't need the above function.

    The cell on the worksheet you want to execute the master formulas contains:
    =Eval(GetFormula('Master Formulas'!C20))

    It is possible you'll also have to create the Eval function that evaluates the text:

    Function Eval(Txt As String)
    Application.Volatile
    Eval = Evaluate(Txt)
    End Function

    I thought this was a standard Excel function however it is possible it is not.

    Will that do what you want to do?
    Regards,
    Sean
    Last edited by SPDavern; 01-07-2005 at 03:58 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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