Re: $$Excel-Macros$$ Formula to Display Latest PO number Using date and emp ID
Hi Van, Excellent sample, Thanks. Eugene On Wed, Jul 22, 2015 at 12:47 AM, Bé Trần Văn betnmtdongna...@gmail.com wrote: B3 selected in the drop down list, View Results. 2015-07-21 19:08 GMT+07:00 Paul Schreiner schreiner_p...@att.net: First: Your EMP ID field is mixed with text and numbers. Probably because this list was imported from another source. (generated a report as a text file and opened it in Excel?) then, if you click in one of the cells, or remove spaces, or interact with the cell in several other ways, Excel reinterprets the cell contents as numeric. (that's why some of your cells have a green triangle in the top-left. It indicates that the cell format is different than the previous) this is significant because when you type the EMP ID in Sheet9, Excel will automatically treat it as a NUMBER. then, when you look up and compare the values in Data with the value in Sheet9, a text 123 does NOT equal a NUMBER 123.. So you need to make them the same format. What *I* did was select column B and replace 2 with 2. Excel then converted the entire column to a numeric. NEXT: the =VLOOKUP() function has the format: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) In this, the [range_lookup] flag indicates if you want an EXACT MATCH or APPROXIMATE MATCH. To use this properly, your Data needs to be sorted by EMP ID and then by Period To. Then, in Sheet9 use the formula: =VLOOKUP(E2,Data!B:E,4,TRUE) Interestingly enough, if you use Range_Lookup = FALSE, Vlookup will return the FIRST line that exactly matches the EMP_ID. If you set it to TRUE, it'll return the LAST record. So, if you sort the data by EMP_ID and Newest-to-oldest of the Period To, you would use Range_Lookup = FALSE. if you sorted by EMP_ID and oldest-to-newest of Period To, you would use Range_Lookup = TRUE. I also added an indicator to the Data sheet to see which PRF is selected (for testing purposes) Hope this helps. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - -- *From:* SridharBL sridhar@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Tuesday, July 21, 2015 7:39 AM *Subject:* $$Excel-Macros$$ Formula to Display Latest PO number Using date and emp ID Dear Friends, I need help in creating the formula in attached file. In attached sheet. I have Sheet9 and Data Sheets. In Sheep 9, If I type the Emp ID in the cell, its should look for data in Data sheet for the Emp ID and Latest Date for in column Period To and get the New PO number that will be displayed in next cell. Attache file is self explanatory. Request you help. Thanks Sridhar BL -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any
Re: $$Excel-Macros$$ Formula to Display Latest PO number Using date and emp ID
B3 selected in the drop down list, View Results. 2015-07-21 19:08 GMT+07:00 Paul Schreiner schreiner_p...@att.net: First: Your EMP ID field is mixed with text and numbers. Probably because this list was imported from another source. (generated a report as a text file and opened it in Excel?) then, if you click in one of the cells, or remove spaces, or interact with the cell in several other ways, Excel reinterprets the cell contents as numeric. (that's why some of your cells have a green triangle in the top-left. It indicates that the cell format is different than the previous) this is significant because when you type the EMP ID in Sheet9, Excel will automatically treat it as a NUMBER. then, when you look up and compare the values in Data with the value in Sheet9, a text 123 does NOT equal a NUMBER 123.. So you need to make them the same format. What *I* did was select column B and replace 2 with 2. Excel then converted the entire column to a numeric. NEXT: the =VLOOKUP() function has the format: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) In this, the [range_lookup] flag indicates if you want an EXACT MATCH or APPROXIMATE MATCH. To use this properly, your Data needs to be sorted by EMP ID and then by Period To. Then, in Sheet9 use the formula: =VLOOKUP(E2,Data!B:E,4,TRUE) Interestingly enough, if you use Range_Lookup = FALSE, Vlookup will return the FIRST line that exactly matches the EMP_ID. If you set it to TRUE, it'll return the LAST record. So, if you sort the data by EMP_ID and Newest-to-oldest of the Period To, you would use Range_Lookup = FALSE. if you sorted by EMP_ID and oldest-to-newest of Period To, you would use Range_Lookup = TRUE. I also added an indicator to the Data sheet to see which PRF is selected (for testing purposes) Hope this helps. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - -- *From:* SridharBL sridhar@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Tuesday, July 21, 2015 7:39 AM *Subject:* $$Excel-Macros$$ Formula to Display Latest PO number Using date and emp ID Dear Friends, I need help in creating the formula in attached file. In attached sheet. I have Sheet9 and Data Sheets. In Sheep 9, If I type the Emp ID in the cell, its should look for data in Data sheet for the Emp ID and Latest Date for in column Period To and get the New PO number that will be displayed in next cell. Attache file is self explanatory. Request you help. Thanks Sridhar BL -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To
Re: $$Excel-Macros$$ Formula to Display Latest PO number Using date and emp ID
First:Your EMP ID field is mixed with text and numbers.Probably because this list was imported from another source.(generated a report as a text file and opened it in Excel?) then, if you click in one of the cells, or remove spaces, or interact with the cell in several other ways, Excel reinterprets the cell contents as numeric. (that's why some of your cells have a green triangle in the top-left.It indicates that the cell format is different than the previous) this is significant because when you type the EMP ID in Sheet9, Excel will automatically treat it as a NUMBER.then, when you look up and compare the values in Data with the value in Sheet9, a text 123 does NOT equal a NUMBER 123.. So you need to make them the same format. What *I* did was select column B and replace 2 with 2.Excel then converted the entire column to a numeric. NEXT: the =VLOOKUP() function has the format:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) In this, the [range_lookup] flag indicates if you want an EXACT MATCH or APPROXIMATE MATCH. To use this properly, your Data needs to be sorted by EMP ID and then by Period To. Then, in Sheet9 use the formula:=VLOOKUP(E2,Data!B:E,4,TRUE) Interestingly enough, if you use Range_Lookup = FALSE, Vlookup will return the FIRST line that exactly matches the EMP_ID.If you set it to TRUE, it'll return the LAST record. So, if you sort the data by EMP_ID and Newest-to-oldest of the Period To, you would use Range_Lookup = FALSE.if you sorted by EMP_ID and oldest-to-newest of Period To, you would use Range_Lookup = TRUE. I also added an indicator to the Data sheet to see which PRF is selected (for testing purposes) Hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: SridharBL sridhar@gmail.com To: excel-macros@googlegroups.com Sent: Tuesday, July 21, 2015 7:39 AM Subject: $$Excel-Macros$$ Formula to Display Latest PO number Using date and emp ID Dear Friends, I need help in creating the formula in attached file. In attached sheet. I have Sheet9 and Data Sheets. In Sheep 9, If I type the Emp ID in the cell, its should look for data in Data sheet for the Emp ID and Latest Date for in column Period To and get the New PO number that will be displayed in next cell. Attache file is self explanatory. Request you help. ThanksSridhar BL-- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at
