3

I'm trying to develop an interactive spreadsheet that creates a narrative for a budget document. There will be a variety of options. Once the user selects an item, it will help them calculate the total. I want to setup option boxes that they fill in. For example, four cells will be allowed for input B1:B4. I am going to name each of the four cells (i.e. A, B, C, D). In the reference document I want to write various formulas. In some cases I might need "(A+B)*C" in another I might need "A * B * C" or "(A+B+C)/D" ... The spreadsheet would lookup the text formula, and I want to then convert it. So in the case of the lookup finding "(A+B)*C" I want it to convert it to =(indirect(A)+indirect(B))*indirect(C) which would then get the values from A (which is B1), B (which is B2) and so on.

Essentially, I would like to use or create something that is exactly the opposite of Excel's FORMULATEXT() function. I would prefer to do this in Google Sheets but I am willing to use Excel if I need to. I have a very basic understanding of both Google's scripting and VBA, and am willing to create if necessary, but not even sure how to tackle it.

Suggestions?

player0
  • 124,011
  • 12
  • 67
  • 124
Curtis Fuller
  • 115
  • 1
  • 2
  • 10
  • 2
    A quick and dirty way in excel vba would be once a value has been entered into the cell you can do `cell.forumla = "=" & cell.Value`. This has ZERO error checking though. I've never coded in google's spreadsheets, but I would imagine it wouldn't be too much different. – Taelsin Jan 26 '16 at 20:18
  • So that reference document is not a spreadsheet but you need to extract the formulas from it? The formulas would have to be at an easy to find place. e.g. at the start of the nth paragraph. Or right behind a specific key word you can search for. – Tesseract Jan 26 '16 at 20:25
  • btw. In google sheets you can do `cell2.setFormula(cell1.getValue())`, if you have the text formula in a cell. – Tesseract Jan 26 '16 at 20:28

1 Answers1

2

I found a way to do it in Google Apps Script: 

function reCalc() { 
var sheet = SpreadsheetApp.getActiveSheet();  
var src = sheet.getRange("J26");   // The cell which holds the formula
var str = src.getValue(); 
str = str.replace('A', 'indirect("OPTA")').replace('B', 'indirect("OPTB")').replace('C', 'indirect("OPTC")').replace('D', 'indirect("OPTD")').replace('ENR', 'indirect("ENR")'); 
var cell = sheet.getRange("J30");  // The cell where I want the results to be
cell.setFormula(str);              // Setting the formula.
}

Thank you to SpiderPig for giving me the idea!

player0
  • 124,011
  • 12
  • 67
  • 124
Curtis Fuller
  • 115
  • 1
  • 2
  • 10