Automatically display sentence.
Sir,
First I want to thank Mr Max who responded me and tried to solve my problem. I m also very sorry that I have not good knowledge of English. If someone can understand my problem and can solve this I will be very grateful. I have a worksheet which have columns in this manner €“ A B C D Concession Form Received 6 List of C or D Form Received. 7 Form Form No. Date Amount 8 C Form 9 D Form 10 C Form 11 C Form 12 D Form 13 D Form 14 Full Tax 15 C Form I want to do that a sentence €śList of C Form Received€ť or List of C Form Due€ť, or List of [ blank ] Form€ť automatically display in a6 in A6. only in that case when in b8 in front of Form; Form No is Filled. Otherwise it should be returned blank. This A6 content sentence should be change with Auto Filter filtering with specific C or D Form selection. Like when I select blank and C Form it should be €śList of C Form not Recd€ť, when I selct not blank and C Form it should be display €śList of C Form Recd.€ť Regards RRS |
Automatically display sentence.
Perhaps you might want to try this UDF from a previous post
by Tom Ogilvy in microsoft.public.excel.programming [Subject: Read AutoFilter Criteria] UDF = User defined function Tom's UDF will display the autofilter criteria selected in a cell A revised sample with Tom Ogilvy's UDF implemented is available at: http://www.savefile.com/files/4473648 Display_AutoComposed_Sentence_V2_Rao_newusers.xls To implement the UDF: Press Alt+F11 to go to VBE Click Insert Module Copy and paste the UDF* into the white space on the right *everything within the dotted lines ("begin vba" to "end vba") Alt+Q to get back to Excel Then in Excel, we could use Tom's UDF by putting in a cell, say B3: =showfilter(A:A) B3 will return the filter criteria effected in col A If you choose "C Form" from the autofilter droplist in A7, B3 will return: "=C Form" Since we want to auto-compose the sentence in A6 by capture the autofilter criteria effected in cols A and B, we could try in A6 something like: ="List of "&TRIM(SUBSTITUTE(showfilter(A:A),"=","")&" "&SUBSTITUTE(showfilter(B:B),"=",""))&" Received." You'd need to tinker around with all the possible autofilter criteria selected in A7 and B7 in your *actual* file, and see the returns from Tom's UDF. Then refine the formula in A6 further, possibly by using more nested SUBSTITUTE(...) so that each combo-selection will give the required "sentence" in A6. -- begin vba -- Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- end vba -- Hope this takes you a little closer to your goal .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rao Ratan Singh" wrote in message ... Sir, First I want to thank Mr Max who responded me and tried to solve my problem. I m also very sorry that I have not good knowledge of English. If someone can understand my problem and can solve this I will be very grateful. I have a worksheet which have columns in this manner - A B C D Concession Form Received 6 List of C or D Form Received. 7 Form Form No. Date Amount 8 C Form 9 D Form 10 C Form 11 C Form 12 D Form 13 D Form 14 Full Tax 15 C Form I want to do that a sentence "List of C Form Received" or List of C Form Due", or List of [ blank ] Form" automatically display in a6 in A6. only in that case when in b8 in front of Form; Form No is Filled. Otherwise it should be returned blank. This A6 content sentence should be change with Auto Filter filtering with specific C or D Form selection. Like when I select blank and C Form it should be "List of C Form not Recd", when I selct not blank and C Form it should be display "List of C Form Recd." Regards RRS |
Automatically display sentence.
Perhaps you might want to try this UDF from a previous post
by Tom Ogilvy in microsoft.public.excel.programming [Subject: Read AutoFilter Criteria] UDF = User defined function Tom's UDF will display the autofilter criteria selected in a cell A revised sample with Tom Ogilvy's UDF implemented is available at: http://www.savefile.com/files/4473648 Display_AutoComposed_Sentence_V2_Rao_newusers.xls To implement the UDF: Press Alt+F11 to go to VBE Click Insert Module Copy and paste the UDF* into the white space on the right *everything within the dotted lines ("begin vba" to "end vba") Alt+Q to get back to Excel Then in Excel, we could use Tom's UDF by putting in a cell, say B3: =showfilter(A:A) B3 will return the filter criteria effected in col A If you choose "C Form" from the autofilter droplist in A7, B3 will return: "=C Form" Since we want to auto-compose the sentence in A6 by capture the autofilter criteria effected in cols A and B, we could try in A6 something like: ="List of "&TRIM(SUBSTITUTE(showfilter(A:A),"=","")&" "&SUBSTITUTE(showfilter(B:B),"=",""))&" Received." You'd need to tinker around with all the possible autofilter criteria selected in A7 and B7 in your *actual* file, and see the returns from Tom's UDF. Then refine the formula in A6 further, possibly by using more nested SUBSTITUTE(...) so that each combo-selection will give the required "sentence" in A6. -- begin vba -- Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- end vba -- Hope this takes you a little closer to your goal .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rao Ratan Singh" wrote in message ... Sir, First I want to thank Mr Max who responded me and tried to solve my problem. I m also very sorry that I have not good knowledge of English. If someone can understand my problem and can solve this I will be very grateful. I have a worksheet which have columns in this manner - A B C D Concession Form Received 6 List of C or D Form Received. 7 Form Form No. Date Amount 8 C Form 9 D Form 10 C Form 11 C Form 12 D Form 13 D Form 14 Full Tax 15 C Form I want to do that a sentence "List of C Form Received" or List of C Form Due", or List of [ blank ] Form" automatically display in a6 in A6. only in that case when in b8 in front of Form; Form No is Filled. Otherwise it should be returned blank. This A6 content sentence should be change with Auto Filter filtering with specific C or D Form selection. Like when I select blank and C Form it should be "List of C Form not Recd", when I selct not blank and C Form it should be display "List of C Form Recd." Regards RRS |
All times are GMT +1. The time now is 05:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com