How can I extract rows of information from multiple sheets by typing in a date
I need to extract data from multiple sheets in excel. I have made one excel workbook with different repair schedules for different properties. On the first sheet I need to be able to type in a date for example 12/21/2011 and then have it pull each row of information for that date from all the properties. I need to be able to pull each person's information for that specific date so that I can see each repair and that needs to be done on that day. This would be so that it can be printed and then taken for repairs to see the location, numbers, times, etc. Or to be able to see what repairs where done on specific dates.
The colum headings are
date-- time --resident name--phone number--address--reason for visit---visual meter read--MTU meter read--MTU ID--Account number--Action Taken--
I have figured out how to record a macr, but I can't figure out how to pull the information. I did try to use Vlookup but it doesn't work for different sheets. If anyone could help I would really appreciate it
excel-2007
add a comment |
I need to extract data from multiple sheets in excel. I have made one excel workbook with different repair schedules for different properties. On the first sheet I need to be able to type in a date for example 12/21/2011 and then have it pull each row of information for that date from all the properties. I need to be able to pull each person's information for that specific date so that I can see each repair and that needs to be done on that day. This would be so that it can be printed and then taken for repairs to see the location, numbers, times, etc. Or to be able to see what repairs where done on specific dates.
The colum headings are
date-- time --resident name--phone number--address--reason for visit---visual meter read--MTU meter read--MTU ID--Account number--Action Taken--
I have figured out how to record a macr, but I can't figure out how to pull the information. I did try to use Vlookup but it doesn't work for different sheets. If anyone could help I would really appreciate it
excel-2007
Where are your 'properties' held? On different worksheets?
– Alex P
Dec 30 '11 at 17:18
add a comment |
I need to extract data from multiple sheets in excel. I have made one excel workbook with different repair schedules for different properties. On the first sheet I need to be able to type in a date for example 12/21/2011 and then have it pull each row of information for that date from all the properties. I need to be able to pull each person's information for that specific date so that I can see each repair and that needs to be done on that day. This would be so that it can be printed and then taken for repairs to see the location, numbers, times, etc. Or to be able to see what repairs where done on specific dates.
The colum headings are
date-- time --resident name--phone number--address--reason for visit---visual meter read--MTU meter read--MTU ID--Account number--Action Taken--
I have figured out how to record a macr, but I can't figure out how to pull the information. I did try to use Vlookup but it doesn't work for different sheets. If anyone could help I would really appreciate it
excel-2007
I need to extract data from multiple sheets in excel. I have made one excel workbook with different repair schedules for different properties. On the first sheet I need to be able to type in a date for example 12/21/2011 and then have it pull each row of information for that date from all the properties. I need to be able to pull each person's information for that specific date so that I can see each repair and that needs to be done on that day. This would be so that it can be printed and then taken for repairs to see the location, numbers, times, etc. Or to be able to see what repairs where done on specific dates.
The colum headings are
date-- time --resident name--phone number--address--reason for visit---visual meter read--MTU meter read--MTU ID--Account number--Action Taken--
I have figured out how to record a macr, but I can't figure out how to pull the information. I did try to use Vlookup but it doesn't work for different sheets. If anyone could help I would really appreciate it
excel-2007
excel-2007
asked Dec 30 '11 at 16:44
user1123210user1123210
412
412
Where are your 'properties' held? On different worksheets?
– Alex P
Dec 30 '11 at 17:18
add a comment |
Where are your 'properties' held? On different worksheets?
– Alex P
Dec 30 '11 at 17:18
Where are your 'properties' held? On different worksheets?
– Alex P
Dec 30 '11 at 17:18
Where are your 'properties' held? On different worksheets?
– Alex P
Dec 30 '11 at 17:18
add a comment |
1 Answer
1
active
oldest
votes
Advanced filter (AF) would be one way to do this. It does require a small bit of manual work, however, and the data would all be on one sheet.
This example assumes your data starts in column A
on a sheet named Sheet1
with the headers running from A1:K1
(based on information from your question).
1- Name your input range Database
like this:
=OFFSET(Sheet1!$A$1,0,0,11,COUNTA(Sheet1!$A:$A))
This will ensure that each time you run AF, it will always pick up the latest data added to the bottom of the existing list.
2- Copy the headers from the data sheet to other columns in the same sheet. Copy only the headers. Ex: if your columns spanned from A1:K1
, copy A1:K1
to M1:W1
.
3- Hide columns N
through W
.
4- In cell M2
, put the date you want to search for.
5- See below for filter criteria.
Example:
Columns A
through K
have sample meter data:
Columns M
through W
contain the criteria range, with columns N
through W
hidden:
In cell M2
, put the date you want to return values for:
Go to Data
» Filter
» Advanced Filter
(or however you do it in your version of Excel, mine is 2003) and use the following criteria:
List Range should always beDatabase
to capture any new records that may have been added since the last time AF was run.
Criteria Range should always be$M$1:$W$2
although you can put this anywhere and even name it if you prefer.
Copy to should be wherever you want to place the matching records, however it must be on the same sheet (sorry, I did say this was only one possible solution)
When you click 'OK' you should see the matching records for that date put into columns Z:AJ
. Change the date and go to Data
» Filter
» Advanced Filter
again to filter records based on the new date. If you add new records you should see them in the filter because we used a dynamic range name (Database
) to grab them.
HTH
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%2f8681777%2fhow-can-i-extract-rows-of-information-from-multiple-sheets-by-typing-in-a-date%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
Advanced filter (AF) would be one way to do this. It does require a small bit of manual work, however, and the data would all be on one sheet.
This example assumes your data starts in column A
on a sheet named Sheet1
with the headers running from A1:K1
(based on information from your question).
1- Name your input range Database
like this:
=OFFSET(Sheet1!$A$1,0,0,11,COUNTA(Sheet1!$A:$A))
This will ensure that each time you run AF, it will always pick up the latest data added to the bottom of the existing list.
2- Copy the headers from the data sheet to other columns in the same sheet. Copy only the headers. Ex: if your columns spanned from A1:K1
, copy A1:K1
to M1:W1
.
3- Hide columns N
through W
.
4- In cell M2
, put the date you want to search for.
5- See below for filter criteria.
Example:
Columns A
through K
have sample meter data:
Columns M
through W
contain the criteria range, with columns N
through W
hidden:
In cell M2
, put the date you want to return values for:
Go to Data
» Filter
» Advanced Filter
(or however you do it in your version of Excel, mine is 2003) and use the following criteria:
List Range should always beDatabase
to capture any new records that may have been added since the last time AF was run.
Criteria Range should always be$M$1:$W$2
although you can put this anywhere and even name it if you prefer.
Copy to should be wherever you want to place the matching records, however it must be on the same sheet (sorry, I did say this was only one possible solution)
When you click 'OK' you should see the matching records for that date put into columns Z:AJ
. Change the date and go to Data
» Filter
» Advanced Filter
again to filter records based on the new date. If you add new records you should see them in the filter because we used a dynamic range name (Database
) to grab them.
HTH
add a comment |
Advanced filter (AF) would be one way to do this. It does require a small bit of manual work, however, and the data would all be on one sheet.
This example assumes your data starts in column A
on a sheet named Sheet1
with the headers running from A1:K1
(based on information from your question).
1- Name your input range Database
like this:
=OFFSET(Sheet1!$A$1,0,0,11,COUNTA(Sheet1!$A:$A))
This will ensure that each time you run AF, it will always pick up the latest data added to the bottom of the existing list.
2- Copy the headers from the data sheet to other columns in the same sheet. Copy only the headers. Ex: if your columns spanned from A1:K1
, copy A1:K1
to M1:W1
.
3- Hide columns N
through W
.
4- In cell M2
, put the date you want to search for.
5- See below for filter criteria.
Example:
Columns A
through K
have sample meter data:
Columns M
through W
contain the criteria range, with columns N
through W
hidden:
In cell M2
, put the date you want to return values for:
Go to Data
» Filter
» Advanced Filter
(or however you do it in your version of Excel, mine is 2003) and use the following criteria:
List Range should always beDatabase
to capture any new records that may have been added since the last time AF was run.
Criteria Range should always be$M$1:$W$2
although you can put this anywhere and even name it if you prefer.
Copy to should be wherever you want to place the matching records, however it must be on the same sheet (sorry, I did say this was only one possible solution)
When you click 'OK' you should see the matching records for that date put into columns Z:AJ
. Change the date and go to Data
» Filter
» Advanced Filter
again to filter records based on the new date. If you add new records you should see them in the filter because we used a dynamic range name (Database
) to grab them.
HTH
add a comment |
Advanced filter (AF) would be one way to do this. It does require a small bit of manual work, however, and the data would all be on one sheet.
This example assumes your data starts in column A
on a sheet named Sheet1
with the headers running from A1:K1
(based on information from your question).
1- Name your input range Database
like this:
=OFFSET(Sheet1!$A$1,0,0,11,COUNTA(Sheet1!$A:$A))
This will ensure that each time you run AF, it will always pick up the latest data added to the bottom of the existing list.
2- Copy the headers from the data sheet to other columns in the same sheet. Copy only the headers. Ex: if your columns spanned from A1:K1
, copy A1:K1
to M1:W1
.
3- Hide columns N
through W
.
4- In cell M2
, put the date you want to search for.
5- See below for filter criteria.
Example:
Columns A
through K
have sample meter data:
Columns M
through W
contain the criteria range, with columns N
through W
hidden:
In cell M2
, put the date you want to return values for:
Go to Data
» Filter
» Advanced Filter
(or however you do it in your version of Excel, mine is 2003) and use the following criteria:
List Range should always beDatabase
to capture any new records that may have been added since the last time AF was run.
Criteria Range should always be$M$1:$W$2
although you can put this anywhere and even name it if you prefer.
Copy to should be wherever you want to place the matching records, however it must be on the same sheet (sorry, I did say this was only one possible solution)
When you click 'OK' you should see the matching records for that date put into columns Z:AJ
. Change the date and go to Data
» Filter
» Advanced Filter
again to filter records based on the new date. If you add new records you should see them in the filter because we used a dynamic range name (Database
) to grab them.
HTH
Advanced filter (AF) would be one way to do this. It does require a small bit of manual work, however, and the data would all be on one sheet.
This example assumes your data starts in column A
on a sheet named Sheet1
with the headers running from A1:K1
(based on information from your question).
1- Name your input range Database
like this:
=OFFSET(Sheet1!$A$1,0,0,11,COUNTA(Sheet1!$A:$A))
This will ensure that each time you run AF, it will always pick up the latest data added to the bottom of the existing list.
2- Copy the headers from the data sheet to other columns in the same sheet. Copy only the headers. Ex: if your columns spanned from A1:K1
, copy A1:K1
to M1:W1
.
3- Hide columns N
through W
.
4- In cell M2
, put the date you want to search for.
5- See below for filter criteria.
Example:
Columns A
through K
have sample meter data:
Columns M
through W
contain the criteria range, with columns N
through W
hidden:
In cell M2
, put the date you want to return values for:
Go to Data
» Filter
» Advanced Filter
(or however you do it in your version of Excel, mine is 2003) and use the following criteria:
List Range should always beDatabase
to capture any new records that may have been added since the last time AF was run.
Criteria Range should always be$M$1:$W$2
although you can put this anywhere and even name it if you prefer.
Copy to should be wherever you want to place the matching records, however it must be on the same sheet (sorry, I did say this was only one possible solution)
When you click 'OK' you should see the matching records for that date put into columns Z:AJ
. Change the date and go to Data
» Filter
» Advanced Filter
again to filter records based on the new date. If you add new records you should see them in the filter because we used a dynamic range name (Database
) to grab them.
HTH
answered Jan 6 '12 at 17:34
JimmyPenaJimmyPena
7,68553656
7,68553656
add a comment |
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.
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%2fstackoverflow.com%2fquestions%2f8681777%2fhow-can-i-extract-rows-of-information-from-multiple-sheets-by-typing-in-a-date%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
Where are your 'properties' held? On different worksheets?
– Alex P
Dec 30 '11 at 17:18