If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outlook Search Folders | Alan Davies | General Discussion | 10 | October 31st, 2005 08:28 PM |
Getting table info to automatically display in a form | Kiki | Using Forms | 1 | October 13th, 2005 04:00 PM |
Macro to display full screen automatically | jh3016 | General Discussion | 4 | September 25th, 2005 01:51 AM |
Display current date as month xx, yyyy automatically in new docu | SunTaxMan | General Discussion | 9 | September 15th, 2005 03:19 AM |
how do I display the name of a worksheet automatically in a cell? | Archie N. | General Discussion | 2 | August 22nd, 2005 07:01 PM |