Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting Data Table Formula instead of Value (Excel XP)

    I need to go to any cell within a 2-input data table and get the row input cell address and the column input cell address. Problem is, I have only been able to get the result of the formula and not the formula itself. So, for example, if the table formulas are:

    {=TABLE(G6,G7)}

    I would need to get the two cell addresses within the formula (G6 and G7). But all I have been able to do is get the result of the formula instead.

    I would sincerely appreciate any insights you talented people might be able to offer.

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    Using VBA, rngCell.Formula will return the formula as string. Does this help?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    I think it would help if I knew how to use it. Here is the line of code I used:

    Worksheets("Data Table").Range("A5").Formula = Worksheets("Data Table").Range("D15")

    This gives me the value from D15 in A5, still not the formula as string. What am I doing wrong?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    [Edited]

    I think you have the assignment backwards, if the formula is in D15 to put the string in A5 it should be:

    Worksheets("Data Table").Range("A5").NumberFormat = "@"
    Worksheets("Data Table").Range("A5") = Worksheets("Data Table").Range("D15").Formula

    But what you want to do with the table input cells when you get them? Here's something very crude that may help you to work from:

    Sub GetDTCellRefs()
    Dim rngCell As Range
    For Each rngCell In Selection
    If rngCell.HasFormula Then _
    rngCell.Offset(10, 0) = _
    Mid(rngCell.Formula, 8, Len(rngCell.Formula) - 8)
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    John,

    Thank you for your posts, your interest and your really nifty suggestions.

    Here's my assignment. I have some users who have a large model that contains numerous data tables that take some time to calculate. What they want me to do is a two-step operation. The first is to give them a macro button that when clicked will select each table in the model and convert each table to values, thereby speeding up their model appreciably. I've got that much done so far.

    However, step 2 is more challenging, at least for me. What they want there is to be able to click a second macro button to convert the tables back to actual tables, that is with formulas and not values. So it occurred to me that I could get the row and column input cells in the first step (before converting the table to values) and create a lookup table elsewhere on the sheet that would store the row and column input cell addresses for each table. Then the macro to convert the tables back to formulas could use the lookup table to get the appropriate row and column input cells for each table and go ahead and regenerate the tables.

    So in this step I need to capture the table formula in order to extract the row and column addresses within the formula. I just used A5 and A15 as sample addresses for now in order to figure out both the logic and the actual coding.

    That's when I turned to Woody's Lounge (once again).

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    Without having created and tested an actual model, wouldn't it be simpler to range name each table (say "Name1", "Name2", etc), and range name each corresponding Input cell to the respective table (say "Name1_inputRow", etc), and recreate each table (data back to formulas) using a series of the macro equivalent of Data, Table, like this:

    Range("Name1").Table RowInput:=Range("Name1_inputRow")
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    Hi George,
    Rather than setting up a separate table with the formulae, how about converting them to comments attached to the cells concerned. See my <post#=188709>post 188709</post#> on how to do this. Then you could store just the values in the underlying cells, as you want. If a re-calc is needed, run a related macro over the same range to evaluate the comments and store the resulting values (or formulae) in the cells again.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    Another option:
    I have found tables notoriously SLOOOW to calculate. You can get the same data in nearly identical form, by setting up ARRAY formulas to calculate the same things. This is much faster and is still "live".
    Pivot tables are also an option, but would not be "live"

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    That's a great idea and I will try it. It is less klutzy than creating and hiding a lookup table somewhere.

    Let me thank you again for your interest and your terrific suggestions.

    Regards,

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    I read your other post and it sounds intriguing. It is creative and rather a departure from the norm. I will think about it and discuss it with the users, but I also like John's idea of using names. Even if the users don't like it, I will probably give it a try on my own time, just to see how it works.

    Thanks for the suggestion.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    I agree with you about the slowness of data tables, but I have no control over that issue. This is a business model that a number of users have developed and continue to work on. They are far more familiar with data tables than with array formulas and the culture of the place is not such that change comes easily.

    But thanks for the suggestion. I certainly agree with the philosophy of the idea.

    Regards,

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting Data Table Formula instead of Value (Excel XP)

    Again, without me actually testing it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, you could put all your Table Range Names, Input Column Range Names,and Input Row Range Names into three arrays at the start of the code, and the Table (re)creation code will just be a simple loop incrementing through the array of ranges, something like this (untested "air code"):

    Sub SetTheTable()
    Dim TableArray As Variant, TblRowArray As Variant, TblColArray As Variant
    Dim intC As Integer
    TableArray = Array("TableName1", "TableName2", "TableName3")
    TblRowArray = Array("Table_InputRow1", "Table_InputRow2", "Table_InputRow3")
    TblColArray = Array("Table_InputCol1", "Table_InputCol2", "Table_InputCol3")
    For intC = 0 To UBound(TableArray)
    Range(TableArray(intC)).Table _
    RowInput:=Range(TblRowArray(intC)), ColumnInput:=Range(TblColArray(intC))
    Next intC
    End Sub

    You'd have to be careful maintaining this, a better way might be to create dynamic string arrays and read the ranges into the array from a lookup area on the spreadsheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •