Excel view data in 1 worksheet based on value in another worksheet
up vote
1
down vote
favorite
Morning
My first post, so here goes!
I have an excel spreadsheet with 2 worksheets
worksheet 1 contains common locations for waste collection areas for blocks of flats.
Worksheet 2 contains the actual properties that use those common locations.
There are 115 locations in worksheet 1 and 1300 in worksheet 2.
Each worksheet has a common ID column.
I'm trying to set worksheet 1 up so that when i click on the ID column, Worksheet 2 only shows the properties that use that location.
I've tried using the HYPERLINK function to open the second worksheet but it displays all the records.
Can anyone help with solving this problem?
edit: Thursday morning
thanks for the responses so far, hopefully you can see the images i'e linked to at the bottom
The common column in the spreadsheets is UPRN in Areas and Cross_reference in Properties
My eventual result would be to be able to click on the UPRN field (in Areas) which would then display all the records in Properties with that same cross_reference.
Using the first record in Areas (28059497) as an example, that would show 12 records in Properties
thanks
Simon Hume
Areas spreadsheet image
Properties spreadsheet image
microsoft-excel
asked Nov 21 at 10:56
Simon Hume
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
|
show 1 more comment
up vote
1
down vote
favorite
Morning
My first post, so here goes!
I have an excel spreadsheet with 2 worksheets
worksheet 1 contains common locations for waste collection areas for blocks of flats.
Worksheet 2 contains the actual properties that use those common locations.
There are 115 locations in worksheet 1 and 1300 in worksheet 2.
Each worksheet has a common ID column.
I'm trying to set worksheet 1 up so that when i click on the ID column, Worksheet 2 only shows the properties that use that location.
I've tried using the HYPERLINK function to open the second worksheet but it displays all the records.
Can anyone help with solving this problem?
edit: Thursday morning
thanks for the responses so far, hopefully you can see the images i'e linked to at the bottom
The common column in the spreadsheets is UPRN in Areas and Cross_reference in Properties
My eventual result would be to be able to click on the UPRN field (in Areas) which would then display all the records in Properties with that same cross_reference.
Using the first record in Areas (28059497) as an example, that would show 12 records in Properties
thanks
Simon Hume
Areas spreadsheet image
Properties spreadsheet image
microsoft-excel
asked Nov 21 at 10:56
Simon Hume
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Welcome to Super User! Can you please edit your question to include screenshots of your problem so it would be easier to understand? Thank you!
– Aulis Ronkainen
Nov 21 at 13:30
Hard to tell without more information, but it sounds like a Pivot Table solution could work for you.
– Rey Juna
Nov 21 at 17:11
Do you mean you want to Worksheet2 filter automatically based on Worksheet 1? Is my understanding correct?
– Lee
Nov 22 at 8:57
@Simon Hume, I can suggest two possible methods to Find related properties when you Click/Select the Common Id (UPRN). One is VBA(Macro) another is Excel Formula. Just confirm through comments which one you need !!
– Rajesh S
Nov 22 at 11:55
@Lee - yes, when the user selects the UPRN in Areas, it filters out the records in Properties
– Simon Hume
Nov 23 at 10:02
|
show 1 more comment
up vote
1
down vote
favorite
up vote
1
down vote
favorite
Morning
My first post, so here goes!
I have an excel spreadsheet with 2 worksheets
worksheet 1 contains common locations for waste collection areas for blocks of flats.
Worksheet 2 contains the actual properties that use those common locations.
There are 115 locations in worksheet 1 and 1300 in worksheet 2.
Each worksheet has a common ID column.
I'm trying to set worksheet 1 up so that when i click on the ID column, Worksheet 2 only shows the properties that use that location.
I've tried using the HYPERLINK function to open the second worksheet but it displays all the records.
Can anyone help with solving this problem?
edit: Thursday morning
thanks for the responses so far, hopefully you can see the images i'e linked to at the bottom
The common column in the spreadsheets is UPRN in Areas and Cross_reference in Properties
My eventual result would be to be able to click on the UPRN field (in Areas) which would then display all the records in Properties with that same cross_reference.
Using the first record in Areas (28059497) as an example, that would show 12 records in Properties
thanks
Simon Hume
Areas spreadsheet image
Properties spreadsheet image
microsoft-excel
asked Nov 21 at 10:56
Simon Hume
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Morning
My first post, so here goes!
I have an excel spreadsheet with 2 worksheets
worksheet 1 contains common locations for waste collection areas for blocks of flats.
Worksheet 2 contains the actual properties that use those common locations.
There are 115 locations in worksheet 1 and 1300 in worksheet 2.
Each worksheet has a common ID column.
I'm trying to set worksheet 1 up so that when i click on the ID column, Worksheet 2 only shows the properties that use that location.
I've tried using the HYPERLINK function to open the second worksheet but it displays all the records.
Can anyone help with solving this problem?
edit: Thursday morning
thanks for the responses so far, hopefully you can see the images i'e linked to at the bottom
The common column in the spreadsheets is UPRN in Areas and Cross_reference in Properties
My eventual result would be to be able to click on the UPRN field (in Areas) which would then display all the records in Properties with that same cross_reference.
Using the first record in Areas (28059497) as an example, that would show 12 records in Properties
thanks
Simon Hume
Areas spreadsheet image
Properties spreadsheet image
microsoft-excel
microsoft-excel
asked Nov 21 at 10:56
Simon Hume
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 21 at 10:56
Simon Hume
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 22 at 8:32
asked Nov 21 at 10:56
Simon Hume
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 21 at 10:56
Simon Hume
62
asked Nov 21 at 10:56
Simon Hume
62
62
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Simon Hume is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Welcome to Super User! Can you please edit your question to include screenshots of your problem so it would be easier to understand? Thank you!
– Aulis Ronkainen
Nov 21 at 13:30
Hard to tell without more information, but it sounds like a Pivot Table solution could work for you.
– Rey Juna
Nov 21 at 17:11
Do you mean you want to Worksheet2 filter automatically based on Worksheet 1? Is my understanding correct?
– Lee
Nov 22 at 8:57
@Simon Hume, I can suggest two possible methods to Find related properties when you Click/Select the Common Id (UPRN). One is VBA(Macro) another is Excel Formula. Just confirm through comments which one you need !!
– Rajesh S
Nov 22 at 11:55
@Lee - yes, when the user selects the UPRN in Areas, it filters out the records in Properties
– Simon Hume
Nov 23 at 10:02
|
show 1 more comment
Welcome to Super User! Can you please edit your question to include screenshots of your problem so it would be easier to understand? Thank you!
– Aulis Ronkainen
Nov 21 at 13:30
Hard to tell without more information, but it sounds like a Pivot Table solution could work for you.
– Rey Juna
Nov 21 at 17:11
Do you mean you want to Worksheet2 filter automatically based on Worksheet 1? Is my understanding correct?
– Lee
Nov 22 at 8:57
@Simon Hume, I can suggest two possible methods to Find related properties when you Click/Select the Common Id (UPRN). One is VBA(Macro) another is Excel Formula. Just confirm through comments which one you need !!
– Rajesh S
Nov 22 at 11:55
@Lee - yes, when the user selects the UPRN in Areas, it filters out the records in Properties
– Simon Hume
Nov 23 at 10:02
Welcome to Super User! Can you please edit your question to include screenshots of your problem so it would be easier to understand? Thank you!
– Aulis Ronkainen
Nov 21 at 13:30
Welcome to Super User! Can you please edit your question to include screenshots of your problem so it would be easier to understand? Thank you!
– Aulis Ronkainen
Nov 21 at 13:30
Hard to tell without more information, but it sounds like a Pivot Table solution could work for you.
– Rey Juna
Nov 21 at 17:11
Hard to tell without more information, but it sounds like a Pivot Table solution could work for you.
– Rey Juna
Nov 21 at 17:11
Do you mean you want to Worksheet2 filter automatically based on Worksheet 1? Is my understanding correct?
– Lee
Nov 22 at 8:57
Do you mean you want to Worksheet2 filter automatically based on Worksheet 1? Is my understanding correct?
– Lee
Nov 22 at 8:57
@Simon Hume, I can suggest two possible methods to Find related properties when you Click/Select the Common Id (UPRN). One is VBA(Macro) another is Excel Formula. Just confirm through comments which one you need !!
– Rajesh S
Nov 22 at 11:55
@Simon Hume, I can suggest two possible methods to Find related properties when you Click/Select the Common Id (UPRN). One is VBA(Macro) another is Excel Formula. Just confirm through comments which one you need !!
– Rajesh S
Nov 22 at 11:55
@Lee - yes, when the user selects the UPRN in Areas, it filters out the records in Properties
– Simon Hume
Nov 23 at 10:02
@Lee - yes, when the user selects the UPRN in Areas, it filters out the records in Properties
– Simon Hume
Nov 23 at 10:02
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
Write this Array Formula in Cell E21 of Sheet1, finish with Ctrl+Shift+Enter then fill right & Down.
{=IFERROR(INDEX(Sheet2!$E$10:$I$17, SMALL(IF(COUNTIF($D$21, Sheet2!$D$10:$D$17), ROW(Sheet2!$E$10:$I$17)-MIN(ROW(Sheet2!$E$10:$I$17))+1), ROW(A1)), COLUMN(A1)),""))}
How it works:
- Write the UPRN in Cell D21. You may create a Drop Down also for faster process.
- Change UPRN or select from Drop Down(if Created) for other match from Sheet2.
- As soon you write/change the UPRN Excel will extract multiple match from Sheet2 for UPRN in Cell D21 (for example 28059497).
Note:
This Formula will work properly if in Sheet 2 it finds similar UPRN.
Remember in attached Screen Shots with the Question by you I've not found duplicate UPRNs in Properties Sheet.
Adjust cell references in the Formula as needed.
answered Nov 24 at 8:05
Rajesh S
3,4531422
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Write this Array Formula in Cell E21 of Sheet1, finish with Ctrl+Shift+Enter then fill right & Down.
{=IFERROR(INDEX(Sheet2!$E$10:$I$17, SMALL(IF(COUNTIF($D$21, Sheet2!$D$10:$D$17), ROW(Sheet2!$E$10:$I$17)-MIN(ROW(Sheet2!$E$10:$I$17))+1), ROW(A1)), COLUMN(A1)),""))}
How it works:
- Write the UPRN in Cell D21. You may create a Drop Down also for faster process.
- Change UPRN or select from Drop Down(if Created) for other match from Sheet2.
- As soon you write/change the UPRN Excel will extract multiple match from Sheet2 for UPRN in Cell D21 (for example 28059497).
Note:
This Formula will work properly if in Sheet 2 it finds similar UPRN.
Remember in attached Screen Shots with the Question by you I've not found duplicate UPRNs in Properties Sheet.
Adjust cell references in the Formula as needed.
answered Nov 24 at 8:05
Rajesh S
3,4531422
add a comment |
up vote
0
down vote
Write this Array Formula in Cell E21 of Sheet1, finish with Ctrl+Shift+Enter then fill right & Down.
{=IFERROR(INDEX(Sheet2!$E$10:$I$17, SMALL(IF(COUNTIF($D$21, Sheet2!$D$10:$D$17), ROW(Sheet2!$E$10:$I$17)-MIN(ROW(Sheet2!$E$10:$I$17))+1), ROW(A1)), COLUMN(A1)),""))}
How it works:
- Write the UPRN in Cell D21. You may create a Drop Down also for faster process.
- Change UPRN or select from Drop Down(if Created) for other match from Sheet2.
- As soon you write/change the UPRN Excel will extract multiple match from Sheet2 for UPRN in Cell D21 (for example 28059497).
Note:
This Formula will work properly if in Sheet 2 it finds similar UPRN.
Remember in attached Screen Shots with the Question by you I've not found duplicate UPRNs in Properties Sheet.
Adjust cell references in the Formula as needed.
answered Nov 24 at 8:05
Rajesh S
3,4531422
add a comment |
up vote
0
down vote
up vote
0
down vote
Write this Array Formula in Cell E21 of Sheet1, finish with Ctrl+Shift+Enter then fill right & Down.
{=IFERROR(INDEX(Sheet2!$E$10:$I$17, SMALL(IF(COUNTIF($D$21, Sheet2!$D$10:$D$17), ROW(Sheet2!$E$10:$I$17)-MIN(ROW(Sheet2!$E$10:$I$17))+1), ROW(A1)), COLUMN(A1)),""))}
How it works:
- Write the UPRN in Cell D21. You may create a Drop Down also for faster process.
- Change UPRN or select from Drop Down(if Created) for other match from Sheet2.
- As soon you write/change the UPRN Excel will extract multiple match from Sheet2 for UPRN in Cell D21 (for example 28059497).
Note:
This Formula will work properly if in Sheet 2 it finds similar UPRN.
Remember in attached Screen Shots with the Question by you I've not found duplicate UPRNs in Properties Sheet.
Adjust cell references in the Formula as needed.
answered Nov 24 at 8:05
Rajesh S
3,4531422
Write this Array Formula in Cell E21 of Sheet1, finish with Ctrl+Shift+Enter then fill right & Down.
{=IFERROR(INDEX(Sheet2!$E$10:$I$17, SMALL(IF(COUNTIF($D$21, Sheet2!$D$10:$D$17), ROW(Sheet2!$E$10:$I$17)-MIN(ROW(Sheet2!$E$10:$I$17))+1), ROW(A1)), COLUMN(A1)),""))}
How it works:
- Write the UPRN in Cell D21. You may create a Drop Down also for faster process.
- Change UPRN or select from Drop Down(if Created) for other match from Sheet2.
- As soon you write/change the UPRN Excel will extract multiple match from Sheet2 for UPRN in Cell D21 (for example 28059497).
Note:
This Formula will work properly if in Sheet 2 it finds similar UPRN.
Remember in attached Screen Shots with the Question by you I've not found duplicate UPRNs in Properties Sheet.
Adjust cell references in the Formula as needed.
answered Nov 24 at 8:05
Rajesh S
3,4531422
answered Nov 24 at 8:05
Rajesh S
3,4531422
answered Nov 24 at 8:05
Rajesh S
3,4531422
answered Nov 24 at 8:05
Rajesh S
3,4531422
3,4531422
add a comment |
add a comment |
Simon Hume is a new contributor. Be nice, and check out our Code of Conduct.
Simon Hume is a new contributor. Be nice, and check out our Code of Conduct.
Simon Hume is a new contributor. Be nice, and check out our Code of Conduct.
Simon Hume is a new contributor. Be nice, and check out our Code of Conduct.
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%2f1377245%2fexcel-view-data-in-1-worksheet-based-on-value-in-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
Welcome to Super User! Can you please edit your question to include screenshots of your problem so it would be easier to understand? Thank you!
– Aulis Ronkainen
Nov 21 at 13:30
Hard to tell without more information, but it sounds like a Pivot Table solution could work for you.
– Rey Juna
Nov 21 at 17:11
Do you mean you want to Worksheet2 filter automatically based on Worksheet 1? Is my understanding correct?
– Lee
Nov 22 at 8:57
@Simon Hume, I can suggest two possible methods to Find related properties when you Click/Select the Common Id (UPRN). One is VBA(Macro) another is Excel Formula. Just confirm through comments which one you need !!
– Rajesh S
Nov 22 at 11:55
@Lee - yes, when the user selects the UPRN in Areas, it filters out the records in Properties
– Simon Hume
Nov 23 at 10:02