Dynamically filter data from one excel worksheet to display on another
Is there a straight forward way to have data stored in one worksheet automatically filtered and displayed on a different spreadsheet. I want to be able to update the data in one sheet and have the filtered view on the other worksheet update automatically.
microsoft-excel
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
asked Jul 14 '16 at 6:33
akashic
62137
add a comment |
Is there a straight forward way to have data stored in one worksheet automatically filtered and displayed on a different spreadsheet. I want to be able to update the data in one sheet and have the filtered view on the other worksheet update automatically.
microsoft-excel
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
asked Jul 14 '16 at 6:33
akashic
62137
What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)?
– jehad
Jul 15 '16 at 1:12
add a comment |
Is there a straight forward way to have data stored in one worksheet automatically filtered and displayed on a different spreadsheet. I want to be able to update the data in one sheet and have the filtered view on the other worksheet update automatically.
microsoft-excel
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
asked Jul 14 '16 at 6:33
akashic
62137
Is there a straight forward way to have data stored in one worksheet automatically filtered and displayed on a different spreadsheet. I want to be able to update the data in one sheet and have the filtered view on the other worksheet update automatically.
microsoft-excel
microsoft-excel
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
asked Jul 14 '16 at 6:33
akashic
62137
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
asked Jul 14 '16 at 6:33
akashic
62137
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
edited Dec 22 '18 at 17:43
Blackwood
2,89461728
2,89461728
asked Jul 14 '16 at 6:33
akashic
62137
asked Jul 14 '16 at 6:33
akashic
62137
asked Jul 14 '16 at 6:33
akashic
62137
62137
What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)?
– jehad
Jul 15 '16 at 1:12
add a comment |
What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)?
– jehad
Jul 15 '16 at 1:12
What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)?
– jehad
Jul 15 '16 at 1:12
What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)?
– jehad
Jul 15 '16 at 1:12
add a comment |
2 Answers
2
active
oldest
votes
Not sure what you mean by straight forward but you can as shown in my example below use formulas to filter and return matching rows:
From data in my sheet1 I can filter for data with specific text in a column
on Sheet2 add headings and type the text you wish to filter for in B1.
In A5 add the following array formula
=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!A$2:Sheet1!A2)),1), "")
Press Ctrl + Shift +Enter to enter the formula as an array you will see the formula will be encased in curly brackets {}
Drag the formula in A5 across B5 to C5 to populate the row for the number of data columns required.
Ammend the formulas to increment the index column number.
Remember to ensure you re-enter the formula as an array formula by pressing Ctrl + Shift + Enter.
B5 should now show index column number 2
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!B$2:Sheet1!B2)),2), "")}
and C5 with column index 3 as follows
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!C$2:Sheet1!C2)),3), "")}
Drag these formulas down for your expected maximum number of data rows
Type in Animal vegatable or Mineral in Sheet2 cell B1 and the table should auto filter.
This has tested ok for Excel 2010
You can automate this further by adding a Data Validation List for cell B1.
answered Jul 15 '16 at 7:03
Antony
971912
add a comment |
Another method using a data table
Using same Sheet1 Data
On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.
Select cell A5 and A6 and drag across the required number of columns
and edit text to your column headings
In cell A6 add the following formula
=IF(Sheet1!A2="","",Sheet1!A2)
and drag A6 across required columns then down required rows
then use filters as required.
answered Jul 15 '16 at 10:42
Antony
971912
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',
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%2fsuperuser.com%2fquestions%2f1100696%2fdynamically-filter-data-from-one-excel-worksheet-to-display-on-another%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
Not sure what you mean by straight forward but you can as shown in my example below use formulas to filter and return matching rows:
From data in my sheet1 I can filter for data with specific text in a column
on Sheet2 add headings and type the text you wish to filter for in B1.
In A5 add the following array formula
=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!A$2:Sheet1!A2)),1), "")
Press Ctrl + Shift +Enter to enter the formula as an array you will see the formula will be encased in curly brackets {}
Drag the formula in A5 across B5 to C5 to populate the row for the number of data columns required.
Ammend the formulas to increment the index column number.
Remember to ensure you re-enter the formula as an array formula by pressing Ctrl + Shift + Enter.
B5 should now show index column number 2
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!B$2:Sheet1!B2)),2), "")}
and C5 with column index 3 as follows
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!C$2:Sheet1!C2)),3), "")}
Drag these formulas down for your expected maximum number of data rows
Type in Animal vegatable or Mineral in Sheet2 cell B1 and the table should auto filter.
This has tested ok for Excel 2010
You can automate this further by adding a Data Validation List for cell B1.
answered Jul 15 '16 at 7:03
Antony
971912
add a comment |
Not sure what you mean by straight forward but you can as shown in my example below use formulas to filter and return matching rows:
From data in my sheet1 I can filter for data with specific text in a column
on Sheet2 add headings and type the text you wish to filter for in B1.
In A5 add the following array formula
=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!A$2:Sheet1!A2)),1), "")
Press Ctrl + Shift +Enter to enter the formula as an array you will see the formula will be encased in curly brackets {}
Drag the formula in A5 across B5 to C5 to populate the row for the number of data columns required.
Ammend the formulas to increment the index column number.
Remember to ensure you re-enter the formula as an array formula by pressing Ctrl + Shift + Enter.
B5 should now show index column number 2
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!B$2:Sheet1!B2)),2), "")}
and C5 with column index 3 as follows
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!C$2:Sheet1!C2)),3), "")}
Drag these formulas down for your expected maximum number of data rows
Type in Animal vegatable or Mineral in Sheet2 cell B1 and the table should auto filter.
This has tested ok for Excel 2010
You can automate this further by adding a Data Validation List for cell B1.
answered Jul 15 '16 at 7:03
Antony
971912
add a comment |
Not sure what you mean by straight forward but you can as shown in my example below use formulas to filter and return matching rows:
From data in my sheet1 I can filter for data with specific text in a column
on Sheet2 add headings and type the text you wish to filter for in B1.
In A5 add the following array formula
=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!A$2:Sheet1!A2)),1), "")
Press Ctrl + Shift +Enter to enter the formula as an array you will see the formula will be encased in curly brackets {}
Drag the formula in A5 across B5 to C5 to populate the row for the number of data columns required.
Ammend the formulas to increment the index column number.
Remember to ensure you re-enter the formula as an array formula by pressing Ctrl + Shift + Enter.
B5 should now show index column number 2
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!B$2:Sheet1!B2)),2), "")}
and C5 with column index 3 as follows
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!C$2:Sheet1!C2)),3), "")}
Drag these formulas down for your expected maximum number of data rows
Type in Animal vegatable or Mineral in Sheet2 cell B1 and the table should auto filter.
This has tested ok for Excel 2010
You can automate this further by adding a Data Validation List for cell B1.
answered Jul 15 '16 at 7:03
Antony
971912
Not sure what you mean by straight forward but you can as shown in my example below use formulas to filter and return matching rows:
From data in my sheet1 I can filter for data with specific text in a column
on Sheet2 add headings and type the text you wish to filter for in B1.
In A5 add the following array formula
=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!A$2:Sheet1!A2)),1), "")
Press Ctrl + Shift +Enter to enter the formula as an array you will see the formula will be encased in curly brackets {}
Drag the formula in A5 across B5 to C5 to populate the row for the number of data columns required.
Ammend the formulas to increment the index column number.
Remember to ensure you re-enter the formula as an array formula by pressing Ctrl + Shift + Enter.
B5 should now show index column number 2
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!B$2:Sheet1!B2)),2), "")}
and C5 with column index 3 as follows
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=$B$1,ROW(Sheet1!$B$2:$B$7)-ROW(Sheet1!$B$2)+1),ROWS(Sheet1!C$2:Sheet1!C2)),3), "")}
Drag these formulas down for your expected maximum number of data rows
Type in Animal vegatable or Mineral in Sheet2 cell B1 and the table should auto filter.
This has tested ok for Excel 2010
You can automate this further by adding a Data Validation List for cell B1.
answered Jul 15 '16 at 7:03
Antony
971912
edited Jul 15 '16 at 7:22
answered Jul 15 '16 at 7:03
Antony
971912
answered Jul 15 '16 at 7:03
Antony
971912
answered Jul 15 '16 at 7:03
Antony
971912
971912
add a comment |
add a comment |
Another method using a data table
Using same Sheet1 Data
On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.
Select cell A5 and A6 and drag across the required number of columns
and edit text to your column headings
In cell A6 add the following formula
=IF(Sheet1!A2="","",Sheet1!A2)
and drag A6 across required columns then down required rows
then use filters as required.
answered Jul 15 '16 at 10:42
Antony
971912
add a comment |
Another method using a data table
Using same Sheet1 Data
On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.
Select cell A5 and A6 and drag across the required number of columns
and edit text to your column headings
In cell A6 add the following formula
=IF(Sheet1!A2="","",Sheet1!A2)
and drag A6 across required columns then down required rows
then use filters as required.
answered Jul 15 '16 at 10:42
Antony
971912
add a comment |
Another method using a data table
Using same Sheet1 Data
On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.
Select cell A5 and A6 and drag across the required number of columns
and edit text to your column headings
In cell A6 add the following formula
=IF(Sheet1!A2="","",Sheet1!A2)
and drag A6 across required columns then down required rows
then use filters as required.
answered Jul 15 '16 at 10:42
Antony
971912
Another method using a data table
Using same Sheet1 Data
On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.
Select cell A5 and A6 and drag across the required number of columns
and edit text to your column headings
In cell A6 add the following formula
=IF(Sheet1!A2="","",Sheet1!A2)
and drag A6 across required columns then down required rows
then use filters as required.
answered Jul 15 '16 at 10:42
Antony
971912
answered Jul 15 '16 at 10:42
Antony
971912
answered Jul 15 '16 at 10:42
Antony
971912
answered Jul 15 '16 at 10:42
Antony
971912
971912
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%2f1100696%2fdynamically-filter-data-from-one-excel-worksheet-to-display-on-another%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
What kind of filtering? Can you give some examples (some made up data and filtering, doesn't have to be anything real)?
– jehad
Jul 15 '16 at 1:12