select a range clicking a combo value in google sheets
I need to do a drop down list in a google sheets with months listed, when I click on one of the month, pointer should go at a specific range in the same sheet, for example if one clicks on November, focus should go in cells(8,233).
Thank you
google-apps-script google-sheets
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
add a comment |
I need to do a drop down list in a google sheets with months listed, when I click on one of the month, pointer should go at a specific range in the same sheet, for example if one clicks on November, focus should go in cells(8,233).
Thank you
google-apps-script google-sheets
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
add a comment |
I need to do a drop down list in a google sheets with months listed, when I click on one of the month, pointer should go at a specific range in the same sheet, for example if one clicks on November, focus should go in cells(8,233).
Thank you
google-apps-script google-sheets
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
I need to do a drop down list in a google sheets with months listed, when I click on one of the month, pointer should go at a specific range in the same sheet, for example if one clicks on November, focus should go in cells(8,233).
Thank you
google-apps-script google-sheets
google-apps-script google-sheets
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
asked Nov 14 '18 at 17:48
Nico RossiNico Rossi
62
62
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
One way to do it is to install an onEdit script like below. This is a bit brute force, but is very easy to understand and change.
In the example below I assumed the drop down is in B7 (picked at random). This is row 7, col 2.
Depending on which month "jan" - "Dec" focus is switched to B21 to B32 respectively.
You can edit to code for wherever you put your drop-down, and wherever you want to change focus to.
function onEdit(event)
var sheet = SpreadsheetApp.getActiveSheet();
var map ='Jan':'B21',
'Feb':'B22',
'Mar':'B23',
'Apr':'B24',
'May':'B25',
'Jun':'B26',
'Jul':'B27',
'Aug':'B28',
'Sep':'B29',
'Oct':'B30',
'Nov':'B31',
'Dec':'B32'
;
// make sure event is coming from the drop-down
if (event.range.getRow() == 7 && event.range.getColumn() == 2 )
var value = event.range.getValue();
range=sheet.getRange(map[value]);
sheet.setActiveRange(range);
EDIT: Code updated from if else construct to "associative array" construct (implemented as a JS object initialised with key:value pairs) based on excellent comments from the appropriately named "TheMaster".
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
1
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53306059%2fselect-a-range-clicking-a-combo-value-in-google-sheets%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
One way to do it is to install an onEdit script like below. This is a bit brute force, but is very easy to understand and change.
In the example below I assumed the drop down is in B7 (picked at random). This is row 7, col 2.
Depending on which month "jan" - "Dec" focus is switched to B21 to B32 respectively.
You can edit to code for wherever you put your drop-down, and wherever you want to change focus to.
function onEdit(event)
var sheet = SpreadsheetApp.getActiveSheet();
var map ='Jan':'B21',
'Feb':'B22',
'Mar':'B23',
'Apr':'B24',
'May':'B25',
'Jun':'B26',
'Jul':'B27',
'Aug':'B28',
'Sep':'B29',
'Oct':'B30',
'Nov':'B31',
'Dec':'B32'
;
// make sure event is coming from the drop-down
if (event.range.getRow() == 7 && event.range.getColumn() == 2 )
var value = event.range.getValue();
range=sheet.getRange(map[value]);
sheet.setActiveRange(range);
EDIT: Code updated from if else construct to "associative array" construct (implemented as a JS object initialised with key:value pairs) based on excellent comments from the appropriately named "TheMaster".
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
1
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
add a comment |
One way to do it is to install an onEdit script like below. This is a bit brute force, but is very easy to understand and change.
In the example below I assumed the drop down is in B7 (picked at random). This is row 7, col 2.
Depending on which month "jan" - "Dec" focus is switched to B21 to B32 respectively.
You can edit to code for wherever you put your drop-down, and wherever you want to change focus to.
function onEdit(event)
var sheet = SpreadsheetApp.getActiveSheet();
var map ='Jan':'B21',
'Feb':'B22',
'Mar':'B23',
'Apr':'B24',
'May':'B25',
'Jun':'B26',
'Jul':'B27',
'Aug':'B28',
'Sep':'B29',
'Oct':'B30',
'Nov':'B31',
'Dec':'B32'
;
// make sure event is coming from the drop-down
if (event.range.getRow() == 7 && event.range.getColumn() == 2 )
var value = event.range.getValue();
range=sheet.getRange(map[value]);
sheet.setActiveRange(range);
EDIT: Code updated from if else construct to "associative array" construct (implemented as a JS object initialised with key:value pairs) based on excellent comments from the appropriately named "TheMaster".
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
1
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
add a comment |
One way to do it is to install an onEdit script like below. This is a bit brute force, but is very easy to understand and change.
In the example below I assumed the drop down is in B7 (picked at random). This is row 7, col 2.
Depending on which month "jan" - "Dec" focus is switched to B21 to B32 respectively.
You can edit to code for wherever you put your drop-down, and wherever you want to change focus to.
function onEdit(event)
var sheet = SpreadsheetApp.getActiveSheet();
var map ='Jan':'B21',
'Feb':'B22',
'Mar':'B23',
'Apr':'B24',
'May':'B25',
'Jun':'B26',
'Jul':'B27',
'Aug':'B28',
'Sep':'B29',
'Oct':'B30',
'Nov':'B31',
'Dec':'B32'
;
// make sure event is coming from the drop-down
if (event.range.getRow() == 7 && event.range.getColumn() == 2 )
var value = event.range.getValue();
range=sheet.getRange(map[value]);
sheet.setActiveRange(range);
EDIT: Code updated from if else construct to "associative array" construct (implemented as a JS object initialised with key:value pairs) based on excellent comments from the appropriately named "TheMaster".
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
One way to do it is to install an onEdit script like below. This is a bit brute force, but is very easy to understand and change.
In the example below I assumed the drop down is in B7 (picked at random). This is row 7, col 2.
Depending on which month "jan" - "Dec" focus is switched to B21 to B32 respectively.
You can edit to code for wherever you put your drop-down, and wherever you want to change focus to.
function onEdit(event)
var sheet = SpreadsheetApp.getActiveSheet();
var map ='Jan':'B21',
'Feb':'B22',
'Mar':'B23',
'Apr':'B24',
'May':'B25',
'Jun':'B26',
'Jul':'B27',
'Aug':'B28',
'Sep':'B29',
'Oct':'B30',
'Nov':'B31',
'Dec':'B32'
;
// make sure event is coming from the drop-down
if (event.range.getRow() == 7 && event.range.getColumn() == 2 )
var value = event.range.getValue();
range=sheet.getRange(map[value]);
sheet.setActiveRange(range);
EDIT: Code updated from if else construct to "associative array" construct (implemented as a JS object initialised with key:value pairs) based on excellent comments from the appropriately named "TheMaster".
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
edited Nov 18 '18 at 8:55
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
answered Nov 15 '18 at 0:24
bcperthbcperth
2,0351614
2,0351614
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
1
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
add a comment |
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
1
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
Let me know if this works.
– bcperth
Nov 15 '18 at 0:25
1
1
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
@theMaster looks neater now, thank you.
– bcperth
Nov 15 '18 at 1:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53306059%2fselect-a-range-clicking-a-combo-value-in-google-sheets%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
