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 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 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
