Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
This seems simple but I guess I'm more simple than the problem today. I have a basic list with a column of dates. The dates are the
results of formulas and are formatted as yyyymm. Some of the formulas don't yield a date and so those cells display nothing. The dates that are displayed might actually be any date within a month, just formatted as described. So far so good. However, I need my advanced filter to only show the rows which contain a date that is in the same month as a cell that is not in the table. That cell is formatted like the ones in the table and also may be any day of any month. I was able to tinker with the criteria enough to get it to show only rows in April 06 when my control cell has an April 06 date, but if I changed the control date to a different day in a different month it wouldn't show any rows at all. Then if I changed it back to the original control date in April 06 it still wouldn't show any rows. This is messed up. Plz help. Richard -- RMC,CPA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Say your dates are in column A, and your control date is in D1
Criterion header: empty Criterion: =MONTH(A2)=MONTH(D1) NB: The formatting of your cells has no incidence on the operation of advanced filter Cheers -- AP "R. Choate" a écrit dans le message de news: ... This seems simple but I guess I'm more simple than the problem today. I have a basic list with a column of dates. The dates are the results of formulas and are formatted as yyyymm. Some of the formulas don't yield a date and so those cells display nothing. The dates that are displayed might actually be any date within a month, just formatted as described. So far so good. However, I need my advanced filter to only show the rows which contain a date that is in the same month as a cell that is not in the table. That cell is formatted like the ones in the table and also may be any day of any month. I was able to tinker with the criteria enough to get it to show only rows in April 06 when my control cell has an April 06 date, but if I changed the control date to a different day in a different month it wouldn't show any rows at all. Then if I changed it back to the original control date in April 06 it still wouldn't show any rows. This is messed up. Plz help. Richard -- RMC,CPA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Hi, You can use this criterion. Assuming your control date is in B2 and your column header is Date Then C1:E1 : Date C2: ="="&DATE(YEAR(B2),MONTH(B2),1) D2: ="<"&DATE(YEAR(B2),MONTH(B2) + 1, 1) E2: ="<"&B2 Then specify C1:E2 as the criterion. -- KellTainer ------------------------------------------------------------------------ KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322 View this thread: http://www.excelforum.com/showthread...hreadid=546282 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
In the criteria range, leave the heading cell blank.
In the cell below, enter a formula that refers to the control date, e.g.: =AND(MONTH(A2)=MONTH($K$1),YEAR(A2)=YEAR($K$1)) where the control date is in cell K1, and the first date in the table is in cell A2. When you run the advanced filter, select the blank heading cell, and the cell with the formula, as the criteria range. R. Choate wrote: This seems simple but I guess I'm more simple than the problem today. I have a basic list with a column of dates. The dates are the results of formulas and are formatted as yyyymm. Some of the formulas don't yield a date and so those cells display nothing. The dates that are displayed might actually be any date within a month, just formatted as described. So far so good. However, I need my advanced filter to only show the rows which contain a date that is in the same month as a cell that is not in the table. That cell is formatted like the ones in the table and also may be any day of any month. I was able to tinker with the criteria enough to get it to show only rows in April 06 when my control cell has an April 06 date, but if I changed the control date to a different day in a different month it wouldn't show any rows at all. Then if I changed it back to the original control date in April 06 it still wouldn't show any rows. This is messed up. Plz help. Richard -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Hi Debra,
That didn't work. The formula returned a "FALSE" and no rows were shown. Also, yesterday when I tried to use the AND function while working on my own solution, it never worked. I did everything like you instructed. Any ideas? Thanks, Richard -- RMC,CPA "Debra Dalgleish" wrote in message ... In the criteria range, leave the heading cell blank. In the cell below, enter a formula that refers to the control date, e.g.: =AND(MONTH(A2)=MONTH($K$1),YEAR(A2)=YEAR($K$1)) where the control date is in cell K1, and the first date in the table is in cell A2. When you run the advanced filter, select the blank heading cell, and the cell with the formula, as the criteria range. R. Choate wrote: This seems simple but I guess I'm more simple than the problem today. I have a basic list with a column of dates. The dates are the results of formulas and are formatted as yyyymm. Some of the formulas don't yield a date and so those cells display nothing. The dates that are displayed might actually be any date within a month, just formatted as described. So far so good. However, I need my advanced filter to only show the rows which contain a date that is in the same month as a cell that is not in the table. That cell is formatted like the ones in the table and also may be any day of any month. I was able to tinker with the criteria enough to get it to show only rows in April 06 when my control cell has an April 06 date, but if I changed the control date to a different day in a different month it wouldn't show any rows at all. Then if I changed it back to the original control date in April 06 it still wouldn't show any rows. This is messed up. Plz help. Richard -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Do the formulas return real dates?
What is entered in the control date cell? A real date, or text? Can you give an example of a few rows of data, and what you'd expect if you filtered them? R. Choate wrote: Hi Debra, That didn't work. The formula returned a "FALSE" and no rows were shown. Also, yesterday when I tried to use the AND function while working on my own solution, it never worked. I did everything like you instructed. Any ideas? Thanks, Richard -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
I'm not sure how this will come through but we'll see. The dates are real but they are returned by formulas, eg
=IF(ISBLANK(J11),"",J11). each cell in that column has a copy of that formula. I used the date format yyyymm to make it look like this (200403). It is not text. There are rows below this which say 200404. I would expect only those rows to show when filtered. By the way, this is only one column of many in the table. Collection Month 200403 200403 200403 200403 -- RMC,CPA "Debra Dalgleish" wrote in message ... Do the formulas return real dates? What is entered in the control date cell? A real date, or text? Can you give an example of a few rows of data, and what you'd expect if you filtered them? R. Choate wrote: Hi Debra, That didn't work. The formula returned a "FALSE" and no rows were shown. Also, yesterday when I tried to use the AND function while working on my own solution, it never worked. I did everything like you instructed. Any ideas? Thanks, Richard -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Oh, I forgot, the control date cell also has a real date. A user enters it as eg 04/09/04 to indicate the date an invoice was paid.
The format for the cell re-formats it to look like all of the other dates. -- RMC,CPA "Debra Dalgleish" wrote in message ... Do the formulas return real dates? What is entered in the control date cell? A real date, or text? Can you give an example of a few rows of data, and what you'd expect if you filtered them? R. Choate wrote: Hi Debra, That didn't work. The formula returned a "FALSE" and no rows were shown. Also, yesterday when I tried to use the AND function while working on my own solution, it never worked. I did everything like you instructed. Any ideas? Thanks, Richard -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Somewhere on the worksheet, enter a couple of formulas to test the
control date, e.g.: =Month($K$1) =Year($K$1) Do those formulas show the result you expected? Then, test the date in a record with 200403 in the date column, using the Month and Year functions, as above. Do those formulas show the result you expected? In the formula that I previously suggested for the criteria area: =AND(MONTH(A2)=MONTH($K$1),YEAR(A2)=YEAR($K$1)) make sure that the reference to the control date cell is absolute: $K$1 and the reference to the data in the table is relative: A2 The formula will return the result for the first data row, so FALSE result wouldn't affect the filter outcome. R. Choate wrote: I'm not sure how this will come through but we'll see. The dates are real but they are returned by formulas, eg =IF(ISBLANK(J11),"",J11). each cell in that column has a copy of that formula. I used the date format yyyymm to make it look like this (200403). It is not text. There are rows below this which say 200404. I would expect only those rows to show when filtered. By the way, this is only one column of many in the table. Collection Month 200403 200403 200403 200403 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
It tested as a legitimate date. I perhaps should mention that some of the cells in the column returned zero-length text because the
formula calls for that if the precedent cell is blank. I don't know if that affects anything. -- RMC,CPA "Debra Dalgleish" wrote in message ... Somewhere on the worksheet, enter a couple of formulas to test the control date, e.g.: =Month($K$1) =Year($K$1) Do those formulas show the result you expected? Then, test the date in a record with 200403 in the date column, using the Month and Year functions, as above. Do those formulas show the result you expected? In the formula that I previously suggested for the criteria area: =AND(MONTH(A2)=MONTH($K$1),YEAR(A2)=YEAR($K$1)) make sure that the reference to the control date cell is absolute: $K$1 and the reference to the data in the table is relative: A2 The formula will return the result for the first data row, so FALSE result wouldn't affect the filter outcome. R. Choate wrote: I'm not sure how this will come through but we'll see. The dates are real but they are returned by formulas, eg =IF(ISBLANK(J11),"",J11). each cell in that column has a copy of that formula. I used the date format yyyymm to make it look like this (200403). It is not text. There are rows below this which say 200404. I would expect only those rows to show when filtered. By the way, this is only one column of many in the table. Collection Month 200403 200403 200403 200403 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
No, the empty string won't affect the filter.
Did you test both the control date, and a date in a record? Did you use an absolute reference to the control date in the criteria formula? R. Choate wrote: It tested as a legitimate date. I perhaps should mention that some of the cells in the column returned zero-length text because the formula calls for that if the precedent cell is blank. I don't know if that affects anything. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
I did all of those things. Nothing worked. Result was no records shown.
-- RMC,CPA "Debra Dalgleish" wrote in message ... No, the empty string won't affect the filter. Did you test both the control date, and a date in a record? Did you use an absolute reference to the control date in the criteria formula? R. Choate wrote: It tested as a legitimate date. I perhaps should mention that some of the cells in the column returned zero-length text because the formula calls for that if the precedent cell is blank. I don't know if that affects anything. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
Can you send me a sample file to test?
R. Choate wrote: I did all of those things. Nothing worked. Result was no records shown. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
OK, I assume that I just trim out the "XSPAM" from your email to send?
-- RMC,CPA "Debra Dalgleish" wrote in message ... Can you send me a sample file to test? R. Choate wrote: I did all of those things. Nothing worked. Result was no records shown. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter problem
That's right.
R. Choate wrote: OK, I assume that I just trim out the "XSPAM" from your email to send? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding advanced filter | Excel Discussion (Misc queries) | |||
using advanced filter in excel | Excel Discussion (Misc queries) | |||
Need Advanced Filter with NOT equal string and OR criteria | Excel Discussion (Misc queries) | |||
Delete rows based on multiple criterias | Excel Discussion (Misc queries) | |||
Advanced Filter using Date represented as text | Excel Worksheet Functions |