Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing lookups and comparisons
its a bit more then I normally do. heres the set up.
I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation The first page is the MAIN sheet. Each Preceding page is from a different data set. I'm trying to (on the Main Sheet) get the most recent date from each preceding sheet of the Renovation date based on the Property Number. some sort of Match and lookup formula, but I cant figure it out. thank you in advance for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing lookups and comparisons
Something along the lines of
=MAX(Sheet1!$A$3:$A$15) You don't say what the sheets are named. HTH, Bernie MS Excel MVP "ahern79" wrote in message ... its a bit more then I normally do. heres the set up. I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation The first page is the MAIN sheet. Each Preceding page is from a different data set. I'm trying to (on the Main Sheet) get the most recent date from each preceding sheet of the Renovation date based on the Property Number. some sort of Match and lookup formula, but I cant figure it out. thank you in advance for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing lookups and comparisons
I have that as part of the formula, however, I need a way to have the formula
automatically look for the Property Number and match up all the same property numbers and then look at the most recent date in all the sheets and input the "MAX" number in the Row it finds the Property number in. Also, if theres a way to verify that the main page has all the prorty numbers in the preceding sheets that would be helpful as well. I know this should be done in Access but the company wont allow out. Thank you, John "Bernie Deitrick" wrote: Something along the lines of =MAX(Sheet1!$A$3:$A$15) You don't say what the sheets are named. HTH, Bernie MS Excel MVP "ahern79" wrote in message ... its a bit more then I normally do. heres the set up. I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation The first page is the MAIN sheet. Each Preceding page is from a different data set. I'm trying to (on the Main Sheet) get the most recent date from each preceding sheet of the Renovation date based on the Property Number. some sort of Match and lookup formula, but I cant figure it out. thank you in advance for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing lookups and comparisons
But, didn't you say:
I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) Then I'm confused: match up all the same property numbers and then look at the most recent date in all the sheets If the Property number is unique, why do you need to look across multiple sheets for it? SO: If you have mulitple sheets with the same property number, then you really need to change the structure of your workbook. MAKE it a database - you don't need to use Access to have a database. Use one sheet with a column for all important items: Property number, date of renovation, description of renovation. Every renovation should have ALL required information entered on the same row. Then you can use data filters, pivot tables, etc. on the database, and find your information without formulas, without duplication, without multiple sheets (which can be a bad idea, despite the ability to use them). HTH, Bernie MS Excel MVP "ahern79" wrote in message ... I have that as part of the formula, however, I need a way to have the formula automatically look for the Property Number and match up all the same property numbers and then look at the most recent date in all the sheets and input the "MAX" number in the Row it finds the Property number in. Also, if theres a way to verify that the main page has all the prorty numbers in the preceding sheets that would be helpful as well. I know this should be done in Access but the company wont allow out. Thank you, John "Bernie Deitrick" wrote: Something along the lines of =MAX(Sheet1!$A$3:$A$15) You don't say what the sheets are named. HTH, Bernie MS Excel MVP "ahern79" wrote in message ... its a bit more then I normally do. heres the set up. I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation The first page is the MAIN sheet. Each Preceding page is from a different data set. I'm trying to (on the Main Sheet) get the most recent date from each preceding sheet of the Renovation date based on the Property Number. some sort of Match and lookup formula, but I cant figure it out. thank you in advance for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing lookups and comparisons
Thank you Bernie for you help however, the way the bosses want this to happen
is: I will eventually have MORE then three sheets. I am datamining for the most recent information I can find on the properties. To complicate this further, every week they send down an UPDATED spreadsheet with more current property ID's. I have to look through and keep track of all the various datamining sources and what dates I find pertaining to those Property IDs. SO, I will have SEVERAL worksheets each for a different datamining source, and I want ONE master sheet that compiles all the different sources (and potential different property ID's depending on the time that I get the updates; I'm not required to go back to the source once I finish the list...just have to update the next source and go from there) and the most recent dates from the different source sheets. A small version for help sake would be: the sheets all have the same headers: (but of course more then just the three renovation dates; in total I have 33 dates to look for for each propertyID) A1 - Property ID B1 - Construction year C1 - Mechanical Renovation Date D1 - Electrical Renovation Date E1 - Plumbing Renovation Date The "Import" sheet starting on Row "2" I need to be able to combine the data from "Data1" and "Data2" in decending value by the Property ID, and with the most recent dates from the Data# sheets. Data1: A B C D E Property ID Construction Date Mechanical Electrical Plumbing 2 14265 1956 3 14266 1980 1999 4 14267 1620 1990 1990 1990 5 14269 1890 Data2 is vertually identical but the dates are different: A B C D E Property ID Construction Date Mechanical Electrical Plumbing 2 14265 1950 3 14266 1978 1999 1998 1997 4 14268 1990 5 14270 2000 I need to be able to combine these property ID's while keeping the most recent years in the B:E columns. Any idea how to do this?? is it a Macro button? is it a sheet filled with formulas?? "Bernie Deitrick" wrote: But, didn't you say: I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) Then I'm confused: match up all the same property numbers and then look at the most recent date in all the sheets If the Property number is unique, why do you need to look across multiple sheets for it? SO: If you have mulitple sheets with the same property number, then you really need to change the structure of your workbook. MAKE it a database - you don't need to use Access to have a database. Use one sheet with a column for all important items: Property number, date of renovation, description of renovation. Every renovation should have ALL required information entered on the same row. Then you can use data filters, pivot tables, etc. on the database, and find your information without formulas, without duplication, without multiple sheets (which can be a bad idea, despite the ability to use them). HTH, Bernie MS Excel MVP "ahern79" wrote in message ... I have that as part of the formula, however, I need a way to have the formula automatically look for the Property Number and match up all the same property numbers and then look at the most recent date in all the sheets and input the "MAX" number in the Row it finds the Property number in. Also, if theres a way to verify that the main page has all the prorty numbers in the preceding sheets that would be helpful as well. I know this should be done in Access but the company wont allow out. Thank you, John "Bernie Deitrick" wrote: Something along the lines of =MAX(Sheet1!$A$3:$A$15) You don't say what the sheets are named. HTH, Bernie MS Excel MVP "ahern79" wrote in message ... its a bit more then I normally do. heres the set up. I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation The first page is the MAIN sheet. Each Preceding page is from a different data set. I'm trying to (on the Main Sheet) get the most recent date from each preceding sheet of the Renovation date based on the Property Number. some sort of Match and lookup formula, but I cant figure it out. thank you in advance for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advancing lookups and comparisons
Well, you describe your layout entirely differently:
First, it was: A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation Now it is: A1 - Property ID B1 - Construction year C1 - Mechanical Renovation Date D1 - Electrical Renovation Date E1 - Plumbing Renovation Date with multiple Property IDs on each sheet. It's hard to come up with a coherent solution when the target is ill-defined and moving. Still, I would copy the data (just data - no headers) from the new sheet and append it to the bottom of my existing list. Then I would use data filters / pivot tables to look at specific properties, dates, etc. HTH, Bernie MS Excel MVP "ahern79" wrote in message ... Thank you Bernie for you help however, the way the bosses want this to happen is: I will eventually have MORE then three sheets. I am datamining for the most recent information I can find on the properties. To complicate this further, every week they send down an UPDATED spreadsheet with more current property ID's. I have to look through and keep track of all the various datamining sources and what dates I find pertaining to those Property IDs. SO, I will have SEVERAL worksheets each for a different datamining source, and I want ONE master sheet that compiles all the different sources (and potential different property ID's depending on the time that I get the updates; I'm not required to go back to the source once I finish the list...just have to update the next source and go from there) and the most recent dates from the different source sheets. A small version for help sake would be: the sheets all have the same headers: (but of course more then just the three renovation dates; in total I have 33 dates to look for for each propertyID) A1 - Property ID B1 - Construction year C1 - Mechanical Renovation Date D1 - Electrical Renovation Date E1 - Plumbing Renovation Date The "Import" sheet starting on Row "2" I need to be able to combine the data from "Data1" and "Data2" in decending value by the Property ID, and with the most recent dates from the Data# sheets. Data1: A B C D E Property ID Construction Date Mechanical Electrical Plumbing 2 14265 1956 3 14266 1980 1999 4 14267 1620 1990 1990 1990 5 14269 1890 Data2 is vertually identical but the dates are different: A B C D E Property ID Construction Date Mechanical Electrical Plumbing 2 14265 1950 3 14266 1978 1999 1998 1997 4 14268 1990 5 14270 2000 I need to be able to combine these property ID's while keeping the most recent years in the B:E columns. Any idea how to do this?? is it a Macro button? is it a sheet filled with formulas?? "Bernie Deitrick" wrote: But, didn't you say: I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) Then I'm confused: match up all the same property numbers and then look at the most recent date in all the sheets If the Property number is unique, why do you need to look across multiple sheets for it? SO: If you have mulitple sheets with the same property number, then you really need to change the structure of your workbook. MAKE it a database - you don't need to use Access to have a database. Use one sheet with a column for all important items: Property number, date of renovation, description of renovation. Every renovation should have ALL required information entered on the same row. Then you can use data filters, pivot tables, etc. on the database, and find your information without formulas, without duplication, without multiple sheets (which can be a bad idea, despite the ability to use them). HTH, Bernie MS Excel MVP "ahern79" wrote in message ... I have that as part of the formula, however, I need a way to have the formula automatically look for the Property Number and match up all the same property numbers and then look at the most recent date in all the sheets and input the "MAX" number in the Row it finds the Property number in. Also, if theres a way to verify that the main page has all the prorty numbers in the preceding sheets that would be helpful as well. I know this should be done in Access but the company wont allow out. Thank you, John "Bernie Deitrick" wrote: Something along the lines of =MAX(Sheet1!$A$3:$A$15) You don't say what the sheets are named. HTH, Bernie MS Excel MVP "ahern79" wrote in message ... its a bit more then I normally do. heres the set up. I have a book with 3 pages. Each page is set the same way. A1 - Property Number (Unique identifier) A2 - Property Name (can be the same) A3 through A15 - Dates of Renovation The first page is the MAIN sheet. Each Preceding page is from a different data set. I'm trying to (on the Main Sheet) get the most recent date from each preceding sheet of the Renovation date based on the Property Number. some sort of Match and lookup formula, but I cant figure it out. thank you in advance for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing One row without enter | Excel Discussion (Misc queries) | |||
Advancing formula | Excel Worksheet Functions | |||
Advancing Timing | Excel Discussion (Misc queries) | |||
Advancing Down A List | Excel Worksheet Functions | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |