Google表格公式可根据其他单元格值更改单元格值 [英] Google Sheets formula to change a cells value based on another cells value
问题描述
我目前在试图在Google表格中创建一个公式,基本上说:
$ b 如果单元格E10的值等于 然后将E11单元格的值更改为 Monthly ,如果E10等于Levy 将E11的值更改为菜单 Triannual 和
这是迄今为止我已经完成的第一部分的公式,我只是坚持创建公式来创建另外两个选项的下拉菜单,如果E10 =指定值以外的东西。
= IF(E10 =Levy,Monthly,Triannual)
还需要添加什么?
最好的问候。
这应该做到这一点,把它复制到你的脚本编辑器并保存它:
functi onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var s = ss.getActiveSheet()
var nota = e.range.getA1Notation()
if( nota ==E10){
var val = e.range.getValue()
if(val ==Levy){
s.getRange(E11)。setDataValidation (null)
s.getRange(E11).setValue(Monthly)
}
else {
s.getRange('E11')。clearContent()
var cell = s.getRange('E11');
var rule = SpreadsheetApp.newDataValidation()。requireValueInList(['Triannual','Quarterly'])。build();
cell.setDataValidation(rule);
}}}
I am currently muddled while trying to create a formula within Google Sheets that basically says:
"If the value of cell E10 equals "Levy" then change the value of E11 cell to "Monthly", if E10 equals anything other than "Levy" change the value of E11 to a drop down of menu "Triannual" and "Quarterly"*
This is the formula I have so far which gets the first part done. I'm just stuck with creating the formula to create the drop down of the two other options if E10 = something other than the specified value.
= IF(E10 = "Levy","Monthly","Triannual")
What else would I need to add?
Best Regards.
This should do it. Copy this to your script editor and save it:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var s=ss.getActiveSheet()
var nota=e.range.getA1Notation()
if(nota=="E10"){
var val=e.range.getValue()
if(val=="Levy"){
s.getRange("E11").setDataValidation(null)
s.getRange("E11").setValue("Monthly")
}
else{
s.getRange('E11').clearContent()
var cell = s.getRange('E11');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Triannual', 'Quarterly']).build();
cell.setDataValidation(rule);
}}}
这篇关于Google表格公式可根据其他单元格值更改单元格值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!