Excel 2010 Data Connection - Add Filter to Command Text
up vote
0
down vote
favorite
Two relevant workbooks:
Historical Data.xlsxwith a tableGLDATA, which includes a fieldcc
Template.xlsxwith a named rangeCOSTCENTER
Question: How do I modify the Command Text in the Data Connections Properties dialog box (using either SQL or Table syntax) to only pull those records from GLDATA into Template.xlsx where cc = COSTCENTER
What have I tried so far
SQL Syntax: SELECT * FROM GLDATA WHERE GLDATA.cc = COSTCENTER
Closest solution I've found
How to add parameters to an external data query in Excel which can't be displayed graphically?
https://stackoverflow.com/a/11333955/290443
microsoft-excel microsoft-excel-2010
edited May 23 '17 at 12:41
Community♦
1
asked Aug 19 '14 at 20:49
Begbie00
2361515
add a comment |
up vote
0
down vote
favorite
Two relevant workbooks:
Historical Data.xlsxwith a tableGLDATA, which includes a fieldcc
Template.xlsxwith a named rangeCOSTCENTER
Question: How do I modify the Command Text in the Data Connections Properties dialog box (using either SQL or Table syntax) to only pull those records from GLDATA into Template.xlsx where cc = COSTCENTER
What have I tried so far
SQL Syntax: SELECT * FROM GLDATA WHERE GLDATA.cc = COSTCENTER
Closest solution I've found
How to add parameters to an external data query in Excel which can't be displayed graphically?
https://stackoverflow.com/a/11333955/290443
microsoft-excel microsoft-excel-2010
edited May 23 '17 at 12:41
Community♦
1
asked Aug 19 '14 at 20:49
Begbie00
2361515
Its not clear from your question, but I'm guessing that the named range COSTCENTER contains a list of values which exist in the field cc?
– Mike Honey
Aug 19 '14 at 23:32
It refers to just one cell, which has the value of the cost center I want to filter on.
– Begbie00
Aug 20 '14 at 0:21
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Two relevant workbooks:
Historical Data.xlsxwith a tableGLDATA, which includes a fieldcc
Template.xlsxwith a named rangeCOSTCENTER
Question: How do I modify the Command Text in the Data Connections Properties dialog box (using either SQL or Table syntax) to only pull those records from GLDATA into Template.xlsx where cc = COSTCENTER
What have I tried so far
SQL Syntax: SELECT * FROM GLDATA WHERE GLDATA.cc = COSTCENTER
Closest solution I've found
How to add parameters to an external data query in Excel which can't be displayed graphically?
https://stackoverflow.com/a/11333955/290443
microsoft-excel microsoft-excel-2010
edited May 23 '17 at 12:41
Community♦
1
asked Aug 19 '14 at 20:49
Begbie00
2361515
Two relevant workbooks:
Historical Data.xlsxwith a tableGLDATA, which includes a fieldcc
Template.xlsxwith a named rangeCOSTCENTER
Question: How do I modify the Command Text in the Data Connections Properties dialog box (using either SQL or Table syntax) to only pull those records from GLDATA into Template.xlsx where cc = COSTCENTER
What have I tried so far
SQL Syntax: SELECT * FROM GLDATA WHERE GLDATA.cc = COSTCENTER
Closest solution I've found
How to add parameters to an external data query in Excel which can't be displayed graphically?
https://stackoverflow.com/a/11333955/290443
microsoft-excel microsoft-excel-2010
microsoft-excel microsoft-excel-2010
edited May 23 '17 at 12:41
Community♦
1
asked Aug 19 '14 at 20:49
Begbie00
2361515
edited May 23 '17 at 12:41
Community♦
1
asked Aug 19 '14 at 20:49
Begbie00
2361515
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
1
asked Aug 19 '14 at 20:49
Begbie00
2361515
asked Aug 19 '14 at 20:49
Begbie00
2361515
asked Aug 19 '14 at 20:49
Begbie00
2361515
2361515
Its not clear from your question, but I'm guessing that the named range COSTCENTER contains a list of values which exist in the field cc?
– Mike Honey
Aug 19 '14 at 23:32
It refers to just one cell, which has the value of the cost center I want to filter on.
– Begbie00
Aug 20 '14 at 0:21
add a comment |
Its not clear from your question, but I'm guessing that the named range COSTCENTER contains a list of values which exist in the field cc?
– Mike Honey
Aug 19 '14 at 23:32
It refers to just one cell, which has the value of the cost center I want to filter on.
– Begbie00
Aug 20 '14 at 0:21
Its not clear from your question, but I'm guessing that the named range COSTCENTER contains a list of values which exist in the field cc?
– Mike Honey
Aug 19 '14 at 23:32
Its not clear from your question, but I'm guessing that the named range COSTCENTER contains a list of values which exist in the field cc?
– Mike Honey
Aug 19 '14 at 23:32
It refers to just one cell, which has the value of the cost center I want to filter on.
– Begbie00
Aug 20 '14 at 0:21
It refers to just one cell, which has the value of the cost center I want to filter on.
– Begbie00
Aug 20 '14 at 0:21
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
The field contains the string COSTCENTER? If so, try this:
SQL Syntax: SELECT * FROM GLDATA WHERE (GLDATA.cc = 'COSTCENTER')
answered Aug 19 '14 at 23:01
Thomas Moser
12
add a comment |
up vote
0
down vote
I would resolve this with the Power Query Add-In.
First build a simple Query to get the COSTCENTER named range data from the Excel Template file. Note this approach allows multiple COSTCENTER values to be listed.
Then start another Query from a simplified SQL Query e.g. SELECT * FROM GLDATA. Then add a Merge step to connect to the first Query on CC = COSTCENTER, and check the option to Only Keep Matching Rows.
From the Data ribbon, choose Refresh All to refresh the data if the COSTCENTER value or SQL input data changes.
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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',
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%2fsuperuser.com%2fquestions%2f800204%2fexcel-2010-data-connection-add-filter-to-command-text%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The field contains the string COSTCENTER? If so, try this:
SQL Syntax: SELECT * FROM GLDATA WHERE (GLDATA.cc = 'COSTCENTER')
answered Aug 19 '14 at 23:01
Thomas Moser
12
add a comment |
up vote
0
down vote
The field contains the string COSTCENTER? If so, try this:
SQL Syntax: SELECT * FROM GLDATA WHERE (GLDATA.cc = 'COSTCENTER')
answered Aug 19 '14 at 23:01
Thomas Moser
12
add a comment |
up vote
0
down vote
up vote
0
down vote
The field contains the string COSTCENTER? If so, try this:
SQL Syntax: SELECT * FROM GLDATA WHERE (GLDATA.cc = 'COSTCENTER')
answered Aug 19 '14 at 23:01
Thomas Moser
12
The field contains the string COSTCENTER? If so, try this:
SQL Syntax: SELECT * FROM GLDATA WHERE (GLDATA.cc = 'COSTCENTER')
answered Aug 19 '14 at 23:01
Thomas Moser
12
answered Aug 19 '14 at 23:01
Thomas Moser
12
answered Aug 19 '14 at 23:01
Thomas Moser
12
answered Aug 19 '14 at 23:01
Thomas Moser
12
12
add a comment |
add a comment |
up vote
0
down vote
I would resolve this with the Power Query Add-In.
First build a simple Query to get the COSTCENTER named range data from the Excel Template file. Note this approach allows multiple COSTCENTER values to be listed.
Then start another Query from a simplified SQL Query e.g. SELECT * FROM GLDATA. Then add a Merge step to connect to the first Query on CC = COSTCENTER, and check the option to Only Keep Matching Rows.
From the Data ribbon, choose Refresh All to refresh the data if the COSTCENTER value or SQL input data changes.
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
add a comment |
up vote
0
down vote
I would resolve this with the Power Query Add-In.
First build a simple Query to get the COSTCENTER named range data from the Excel Template file. Note this approach allows multiple COSTCENTER values to be listed.
Then start another Query from a simplified SQL Query e.g. SELECT * FROM GLDATA. Then add a Merge step to connect to the first Query on CC = COSTCENTER, and check the option to Only Keep Matching Rows.
From the Data ribbon, choose Refresh All to refresh the data if the COSTCENTER value or SQL input data changes.
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
add a comment |
up vote
0
down vote
up vote
0
down vote
I would resolve this with the Power Query Add-In.
First build a simple Query to get the COSTCENTER named range data from the Excel Template file. Note this approach allows multiple COSTCENTER values to be listed.
Then start another Query from a simplified SQL Query e.g. SELECT * FROM GLDATA. Then add a Merge step to connect to the first Query on CC = COSTCENTER, and check the option to Only Keep Matching Rows.
From the Data ribbon, choose Refresh All to refresh the data if the COSTCENTER value or SQL input data changes.
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
I would resolve this with the Power Query Add-In.
First build a simple Query to get the COSTCENTER named range data from the Excel Template file. Note this approach allows multiple COSTCENTER values to be listed.
Then start another Query from a simplified SQL Query e.g. SELECT * FROM GLDATA. Then add a Merge step to connect to the first Query on CC = COSTCENTER, and check the option to Only Keep Matching Rows.
From the Data ribbon, choose Refresh All to refresh the data if the COSTCENTER value or SQL input data changes.
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
answered Aug 20 '14 at 5:25
Mike Honey
1,7411511
1,7411511
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fsuperuser.com%2fquestions%2f800204%2fexcel-2010-data-connection-add-filter-to-command-text%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
Its not clear from your question, but I'm guessing that the named range COSTCENTER contains a list of values which exist in the field cc?
– Mike Honey
Aug 19 '14 at 23:32
It refers to just one cell, which has the value of the cost center I want to filter on.
– Begbie00
Aug 20 '14 at 0:21