Extracting Data Table Formula instead of Value (Excel XP)

Topic

New Reply

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,

Reply | Quote

Replies

Using VBA, rngCell.Formula will return the formula as string. Does this help?

Reply | Quote

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?

Reply | Quote

[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

Reply | Quote

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).

Reply | Quote

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”)

Reply | Quote

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,

Reply | Quote

Again, without me actually testing it , 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.

Reply | Quote

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 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
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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,

Reply | Quote