Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
building a value for a filter
i need to achieve a value 28/08/2009
to achieve this i have a field where the user enters the date they wish to filter on this is translated to the filter criteria by the following formula = "" & B3 the problem is i get a result of 40053 if i put "" around the date i get "28/08/2009" this does not work with the advanced filter. help what formula do i need to achieve my goal |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
building a value for a filter
Try something like this:
=A8B$3 Where A8 is the first cell of the range to filter. -- Biff Microsoft Excel MVP "Joseph Atie" wrote in message ... i need to achieve a value 28/08/2009 to achieve this i have a field where the user enters the date they wish to filter on this is translated to the filter criteria by the following formula = "" & B3 the problem is i get a result of 40053 if i put "" around the date i get "28/08/2009" this does not work with the advanced filter. help what formula do i need to achieve my goal |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
building a value for a filter
Are you saying i should make that the critera for the filter?
i tried this Data!$E$5Data!$E$2 as the critera as well as Data!$F$2Interface!$B$3 (its across sheets) all i get from the advanced filter screen is reference is not valid can you give me more info "T. Valko" wrote: Try something like this: =A8B$3 Where A8 is the first cell of the range to filter. -- Biff Microsoft Excel MVP "Joseph Atie" wrote in message ... i need to achieve a value 28/08/2009 to achieve this i have a field where the user enters the date they wish to filter on this is translated to the filter criteria by the following formula = "" & B3 the problem is i get a result of 40053 if i put "" around the date i get "28/08/2009" this does not work with the advanced filter. help what formula do i need to achieve my goal |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
building a value for a filter
Hi,
Use =""&TEXT(B3,"d/mm/yyyy") If this helps, please click the Yes button Cheers, Shane Devenshire "Joseph Atie" wrote: i need to achieve a value 28/08/2009 to achieve this i have a field where the user enters the date they wish to filter on this is translated to the filter criteria by the following formula = "" & B3 the problem is i get a result of 40053 if i put "" around the date i get "28/08/2009" this does not work with the advanced filter. help what formula do i need to achieve my goal |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
building a value for a filter
Try this to see how it works....
A7 = header = Date A8 = 8/25/2009 A9 = 8/26/2009 A10 = 8/27/2009 A11 = 8/28/2009 A12 = 8/29/2009 A13 = 8/30/2009 User enters a date in cell B3. B3 = user entered date = 8/28/2009 (I use m/d/y format) Enter this formula in C3: =A8B$3 Make the reference to the first cell of the range to filter a relative reference. Select the range A7:A13 Goto the menu DataFilterAdvanced Filter The List Range will already be entered. As the Criteria Range use: C$2:C$3 Note that we use C2 and C3. Leave C2 as an empty cell. Click OK -- Biff Microsoft Excel MVP "Joseph Atie" wrote in message ... Are you saying i should make that the critera for the filter? i tried this Data!$E$5Data!$E$2 as the critera as well as Data!$F$2Interface!$B$3 (its across sheets) all i get from the advanced filter screen is reference is not valid can you give me more info "T. Valko" wrote: Try something like this: =A8B$3 Where A8 is the first cell of the range to filter. -- Biff Microsoft Excel MVP "Joseph Atie" wrote in message ... i need to achieve a value 28/08/2009 to achieve this i have a field where the user enters the date they wish to filter on this is translated to the filter criteria by the following formula = "" & B3 the problem is i get a result of 40053 if i put "" around the date i get "28/08/2009" this does not work with the advanced filter. help what formula do i need to achieve my goal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building criteria string for Advanced Filter variable not resolvin | Excel Discussion (Misc queries) | |||
building list | Excel Worksheet Functions | |||
Building a TOC | Excel Discussion (Misc queries) | |||
building a web site | New Users to Excel | |||
Building Formulas | Excel Worksheet Functions |