google sheets
I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example:
I have a cell containing a date 17.02.2014. I need to use that date in a formula within another cell:
='17.02.2014'!$AV$6
However this date needs to be pulled dynamically, for this example from E26. I would have expected it to look something like:
='&E26&'!$AV$6
What is the correct syntax?
The only way to accomplish that, is through a script, Google Apps Script that is.
function getMax() {
var sh = SpreadsheetApp.getActiveSheet();
var cValue = sh.getRange("C2").getValue();
var hRange = sh.getRange("B3"), hValue = hRange.getValue();
if(cValue > hValue) {
hRange.setValue(cValue);
}
}
The script is retrieving the two values, at 1 minute intervals, and compares them. If the "real" value if higher than the highest value, then the highest will be replaced by the "real' value.
Add the script under Tools>Script editor. Press the bug button to authenticate the script (as it need access to the Google Spreadsheet). Select Resources\Current project's triggers in the script editor and set it to the screenshot given.
I've created an example file for you: get max value from formula
=MAX(INDEX(GOOGLEFINANCE("GOOG", "HIGH", "01-01-2011", TEXT(NOW(), "dd-MM-yyyy"), "1"),"", 2))
This formula will show all the highest day values of the Google share, from the beginning of 2011. The INDEX formula will show only the second column and the MAX formula will show only the highest value.
Both parameters of importrange are strings. Strings can include values from other cells, by way of concatenation. For example,
=importrange("Spreadsheet A Key", A1 & "!B1")
means that the name of the sheet to be imported will be taken from cell A1 of the current sheet.
The character & means concatenation: "Names" & "!B1" is "Names!B1".
Best Answer
Please try:
Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of
E26(which could be as here a sheet named17.02.2014), the required"!"and the content ofAV6(anchored with$s so it does not change if the formula is copied around).