Microsoft Excel: display realtime filtering data from another worksheet
I want to display all rows that has Gender column equal to M inside sheet2. The data is retrieved from sheet1. The filtered data also has to be real time, meaning whenever I make change in gender column in sheet1, sheet2 data should also change accordingly. How could I achieve this?
sheet 1
sheet 1 image link
sheet 2
sheet 2 image link
Thank for helping me out!
microsoft-excel worksheet-function spreadsheet
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
add a comment |
I want to display all rows that has Gender column equal to M inside sheet2. The data is retrieved from sheet1. The filtered data also has to be real time, meaning whenever I make change in gender column in sheet1, sheet2 data should also change accordingly. How could I achieve this?
sheet 1
sheet 1 image link
sheet 2
sheet 2 image link
Thank for helping me out!
microsoft-excel worksheet-function spreadsheet
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
add a comment |
I want to display all rows that has Gender column equal to M inside sheet2. The data is retrieved from sheet1. The filtered data also has to be real time, meaning whenever I make change in gender column in sheet1, sheet2 data should also change accordingly. How could I achieve this?
sheet 1
sheet 1 image link
sheet 2
sheet 2 image link
Thank for helping me out!
microsoft-excel worksheet-function spreadsheet
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
I want to display all rows that has Gender column equal to M inside sheet2. The data is retrieved from sheet1. The filtered data also has to be real time, meaning whenever I make change in gender column in sheet1, sheet2 data should also change accordingly. How could I achieve this?
sheet 1
sheet 1 image link
sheet 2
sheet 2 image link
Thank for helping me out!
microsoft-excel worksheet-function spreadsheet
microsoft-excel worksheet-function spreadsheet
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
asked Jan 1 at 7:48
SokunthanethSokunthaneth
85
85
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Try to use Power Query to get the result:
Select Range- go to Data- select From Range/Table- Enter Power Query Editor:
Filter Gender Column- Close and Load to New WorkSheet:
If you update data in Sheet1 Table, the data in Sheet2 will update after refreshing.
answered Jan 2 at 8:22
LeeLee
89927
add a comment |
To solve the issue you need to create a Helper Column in Sheet 1.
Formula in Cell D2 & fill it Down.
=IF(C2="M",1+MAX($D$1:D1),"")
Write this Formula in Cell A2 of Sheet 2 & fill it Right then Down.
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")
How it works:
This simple INDEX & MATCH combination did the magic & Filters all Rows have Gender M.
Where MATCH(Rows($1:1), returns 1 and as soon drag down it completes, 1, 2, 3, 4 , 5 & 6 and so on, and the Formula uses these values to Match & Filter Rows for M.
Note:
- You can find New Records (Red Color) in the 2nd Screen Shot been filtered also in Sheet 2.
- Formula in Sheet 2 will reflect modifications in Gender also.
Name I's gender (Filled with Yellow color) been modified, excluded by the formula in Sheet 2.- Adjust Cell references in the Formula as needed.
answered Jan 2 at 6:36
Rajesh SRajesh S
1
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%2f1389474%2fmicrosoft-excel-display-realtime-filtering-data-from-another-worksheet%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
Try to use Power Query to get the result:
Select Range- go to Data- select From Range/Table- Enter Power Query Editor:
Filter Gender Column- Close and Load to New WorkSheet:
If you update data in Sheet1 Table, the data in Sheet2 will update after refreshing.
answered Jan 2 at 8:22
LeeLee
89927
add a comment |
Try to use Power Query to get the result:
Select Range- go to Data- select From Range/Table- Enter Power Query Editor:
Filter Gender Column- Close and Load to New WorkSheet:
If you update data in Sheet1 Table, the data in Sheet2 will update after refreshing.
answered Jan 2 at 8:22
LeeLee
89927
add a comment |
Try to use Power Query to get the result:
Select Range- go to Data- select From Range/Table- Enter Power Query Editor:
Filter Gender Column- Close and Load to New WorkSheet:
If you update data in Sheet1 Table, the data in Sheet2 will update after refreshing.
answered Jan 2 at 8:22
LeeLee
89927
Try to use Power Query to get the result:
Select Range- go to Data- select From Range/Table- Enter Power Query Editor:
Filter Gender Column- Close and Load to New WorkSheet:
If you update data in Sheet1 Table, the data in Sheet2 will update after refreshing.
answered Jan 2 at 8:22
LeeLee
89927
answered Jan 2 at 8:22
LeeLee
89927
answered Jan 2 at 8:22
LeeLee
89927
answered Jan 2 at 8:22
LeeLee
89927
89927
add a comment |
add a comment |
To solve the issue you need to create a Helper Column in Sheet 1.
Formula in Cell D2 & fill it Down.
=IF(C2="M",1+MAX($D$1:D1),"")
Write this Formula in Cell A2 of Sheet 2 & fill it Right then Down.
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")
How it works:
This simple INDEX & MATCH combination did the magic & Filters all Rows have Gender M.
Where MATCH(Rows($1:1), returns 1 and as soon drag down it completes, 1, 2, 3, 4 , 5 & 6 and so on, and the Formula uses these values to Match & Filter Rows for M.
Note:
- You can find New Records (Red Color) in the 2nd Screen Shot been filtered also in Sheet 2.
- Formula in Sheet 2 will reflect modifications in Gender also.
Name I's gender (Filled with Yellow color) been modified, excluded by the formula in Sheet 2.- Adjust Cell references in the Formula as needed.
answered Jan 2 at 6:36
Rajesh SRajesh S
1
add a comment |
To solve the issue you need to create a Helper Column in Sheet 1.
Formula in Cell D2 & fill it Down.
=IF(C2="M",1+MAX($D$1:D1),"")
Write this Formula in Cell A2 of Sheet 2 & fill it Right then Down.
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")
How it works:
This simple INDEX & MATCH combination did the magic & Filters all Rows have Gender M.
Where MATCH(Rows($1:1), returns 1 and as soon drag down it completes, 1, 2, 3, 4 , 5 & 6 and so on, and the Formula uses these values to Match & Filter Rows for M.
Note:
- You can find New Records (Red Color) in the 2nd Screen Shot been filtered also in Sheet 2.
- Formula in Sheet 2 will reflect modifications in Gender also.
Name I's gender (Filled with Yellow color) been modified, excluded by the formula in Sheet 2.- Adjust Cell references in the Formula as needed.
answered Jan 2 at 6:36
Rajesh SRajesh S
1
add a comment |
To solve the issue you need to create a Helper Column in Sheet 1.
Formula in Cell D2 & fill it Down.
=IF(C2="M",1+MAX($D$1:D1),"")
Write this Formula in Cell A2 of Sheet 2 & fill it Right then Down.
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")
How it works:
This simple INDEX & MATCH combination did the magic & Filters all Rows have Gender M.
Where MATCH(Rows($1:1), returns 1 and as soon drag down it completes, 1, 2, 3, 4 , 5 & 6 and so on, and the Formula uses these values to Match & Filter Rows for M.
Note:
- You can find New Records (Red Color) in the 2nd Screen Shot been filtered also in Sheet 2.
- Formula in Sheet 2 will reflect modifications in Gender also.
Name I's gender (Filled with Yellow color) been modified, excluded by the formula in Sheet 2.- Adjust Cell references in the Formula as needed.
answered Jan 2 at 6:36
Rajesh SRajesh S
1
To solve the issue you need to create a Helper Column in Sheet 1.
Formula in Cell D2 & fill it Down.
=IF(C2="M",1+MAX($D$1:D1),"")
Write this Formula in Cell A2 of Sheet 2 & fill it Right then Down.
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")
How it works:
This simple INDEX & MATCH combination did the magic & Filters all Rows have Gender M.
Where MATCH(Rows($1:1), returns 1 and as soon drag down it completes, 1, 2, 3, 4 , 5 & 6 and so on, and the Formula uses these values to Match & Filter Rows for M.
Note:
- You can find New Records (Red Color) in the 2nd Screen Shot been filtered also in Sheet 2.
- Formula in Sheet 2 will reflect modifications in Gender also.
Name I's gender (Filled with Yellow color) been modified, excluded by the formula in Sheet 2.- Adjust Cell references in the Formula as needed.
answered Jan 2 at 6:36
Rajesh SRajesh S
1
edited Jan 3 at 6:03
answered Jan 2 at 6:36
Rajesh SRajesh S
1
answered Jan 2 at 6:36
Rajesh SRajesh S
1
answered Jan 2 at 6:36
Rajesh SRajesh S
1
1
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.
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%2f1389474%2fmicrosoft-excel-display-realtime-filtering-data-from-another-worksheet%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
