Help File is not much help (for me anyway)
Using Excel 2007
I am trying to filter uniques to another location.
My data range is D19:H480, with Labels
Help says to have at least three blank rows above this, which I do and the Labels again at row 15
I go to Data > Advanced
Copy to another location
List Range: D19:H19 automatically appears
Criterior Range: I put in D19:D480
Copy to: I put in K15
Check - Unique records only
I get a message box saying: slect a range of cells that contains at least two rows of data.
What am I doing wrong?
I have a workbook with multiple worksheets. The first worksheet is linked to a sharepoint list. The other worksheets are pivot tables with a chart. I want to be able to place a filter (date range for ex) on the first worksheet (the table with all the original data) and then have it reflected in the pivot table.
Right now when I put a filter on the table it hides the rows but the pivot tables are still pulling all rows and columns.
Is there any way to do this?
Thanks!
I have code that filters a sheet, copies it to a different workbook, and then clears the filter via the 'ActiveSheet.ShowAllData' command. However, on a few occasions the filter will not actually filter any data out, and the macro crashes at the ActiveSheet.ShowAllData part.
How do i say 'if a filter is active then show all the data, otherwise ignore the showall data part'.
Or is there a better way ?
Thanks in advance
On the spreadsheet below with Data | Filter | Autofilter selected (for the header row - Date, Booking Start... etc.), when I choose either Sort Ascending or Sort Descending from the drop down list there is no sorting action performed on the rows (records). They remain in the (dis) ordered state prior to my selecting either Sort Ascending or Sort Descending.
If I highlight the range that I wish to sort and choose Data | Sort it works but not unless the range is highlighted
When I select one of the entries (i.e. 1-Aug, 2-Aug, 9-Aug, 10-Aug, 22-Aug, 24-Aug) all rows that match the date chosen are selected (including the contiguous data in the adjacent columns and the others are hidden (as I would expect).
Is there a setting I have missed?
Thanks in advance
******** ******************** ************************************************************************>
Microsoft Excel - fields.xls
___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F10
H10
F11
H11
F12
H12
F13
H13
F14
H14
F15
H15
F16
H16
F17
H17
F18
H18
F19
H19
=
A
B
C
D
E
F
G
H
9
Date
Booking*Start
Booked*Finish
Field
Actual*Finish
Booked*Hours
Field*Used?
Y*/*N
Used*Hours
10
9-Aug
8:00*AM
11:00*AM
Dover*1
11:00*AM
3:00
y
3:00
11
10-Aug
9:00*AM
12:00*PM
Dover*2
12:00*PM
3:00
y
3:00
12
2-Aug
11:00*AM
12:30*PM
Dover*5
12:30*PM
1:30
y
1:30
13
24-Aug
7:30*PM
8:00*PM
Dover*2
8:30*PM
0:30
y
0:30
14
10-Aug
7:30*AM
10:00*AM
Dover*5
10:00*AM
2:30
n
*
15
10-Aug
11:30*AM
12:00*PM
Dover*4
12:00*PM
0:30
y
0:30
16
22-Aug
3:30*PM
4:30*PM
Dover*1
4:30*PM
1:00
n
*
17
2-Aug
11:30*AM
12:30*PM
Dover*2
12:30*PM
1:00
y
1:00
18
1-Aug
8:30*AM
9:30*AM
Dover*4
10:00*AM
1:00
y
1:00
19
22-Aug
7:00*PM
9:00*PM
Dover*4
9:00*PM
2:00
y
2:00
April
*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have table for example like this and Im using function Subtotal to count all countries what are selected by filter.
I will for example choose by filter only countries "Asia" and "Europe".
And here is the result, what is ok.
My quesion is:
I want to count not only Total countries selected by filter, but count all countries on every continent selected by filter.
so the resualt should by in this example:
Total countries: 13
Africa: 0
America: 0
Asia: 10
Europe:3
What function or functions do I need to use to get this result? Thank you
Hi, I made a pivot table from the data that I got from a query in excel. And I also want to make a filter in my pivot table. I know that in "Row labels" and "Values" part you can make a filter, but what about the "Report filter" part?
Actually i want to use an addition filter on the Report filter, like any value lower than 80900 and starting with I should not be displayed.
Is this possible with a macro?
I work as a Chemist in a pharmaceutical company and as things get busier we need to average our cycle times for our tests using a more automated system, while still using Excel.
I have a little query. It may not be possible on excel but here goes.
I need to have a formula that will filter two different columns for certain criteria. I need to filter first by date but the date needs to be by week (Monday to Sunday) and then the next criteria is by individual test. When this is filtered i need the average cycle time to be returned for the dates selected and the test selected to be returned on a seperate excel file. The cycle time is already calculated in a cell so I basically need the filtering to eliminate all the cycle times not associated with the test and the dates selected.
At the end on the other excel file I just require a number which will be the Average cycle time for that particular test selected for the dates selected.
If this is not possible I would appreciate if someone could let me know so we can move on to another way of thinking. If it is possible I would appreciate all help
Is it possible to filter data into another column based on a value
inside the sorted data?
To explain, Say I am filtering data in column A to show up in Column B
as just the last digit in column A. Please see below:
Column A column B
-------------- ---------------
12345 5
52459 9
09801 1
I thought maybe using filter this would be possible, but I could not
get it to work. Anyone tried something like this?
hope someone can help.
i am using the data filter to extract data from a certain part of a sheet, however when i try to remove the filter and look and the whole worksheet some of the data is mixed up any ideas why rows of data would move from one row to another. as i have copied and paste the data originally from different worksheets i wonder if it could be to do with formatting
Hello,
I have the below data consisting of some information about the sort of work we do. The spreadsheet enables people to log on their piece of work, get dates for when they need to chaser for outstanding information, up to a maximum of 3 chasers.
The spreadsheet is used as a reminder log for people to do their work. There are additional columns like "Work Being Done By", etc which I've taken out for this purpose.
Sheet3
A
B
C
D
E
F
G
H
I
3
Case Ref
Type
Chaser 1 Due
Chaser 1 Done
Chaser 2 Due
Chaser 2 Done
Chaser 3 Due
Chaser 3 Done
Date Completed
4
1
Letters
01 November 2008
07 November 2008
14 November 2008
5
2
Letters
02 November 2008
07 November 2008
14 November 2008
6
3
Letters
03 November 2008
10 November 2008
17 November 2008
7
4
Letters
04 November 2008
11 November 2008
18 November 2008
8
5
Memos
05 November 2008
12 November 2008
19 November 2008
9
6
Memos
06 November 2008
13 November 2008
20 November 2008
10
7
Memos
07 November 2008
14 November 2008
21 November 2008
What I want to do is, on another sheet, is produce results like the following:
*From now on, I've manually typed in data to show you what I want the end result to look like. The example data below has no relation to the "main table" of data above, even though I want the end result to obviously be based on it. I thought I'd mention in case people get confused, trying to match up my example data below to the real data above*
Sheet1
A
B
C
D
3
CASES DUE TO BE CHASED TODAY
4
5
1st Chasers:
6
Case Ref
Type
Chaser 1 Due
Chaser 1 Done
7
1
Letters
01 November 2008
8
2
Letters
02 November 2008
9
3
Letters
03 November 2008
10
4
Letters
04 November 2008
11
12
2nd Chasers:
13
Case Ref
Type
Chaser 2 Due
Chaser 2 Done
14
5
Memos
12 November 2008
15
6
Memos
13 November 2008
16
7
Letters
14 November 2008
17
8
Letters
15 November 2008
18
19
3rd Chasers:
20
Case Ref
Type
Chaser 3 Due
Chaser 3 Done
21
9
Letters
23 November 2008
22
10
Letters
24 November 2008
23
11
Memos
25 November 2008
24
12
Memos
26 November 2008
25
26
CASES DUE TO BE CHASED TOMORROW
27
etc
The Sheet1 data is just typed in for now, it's not automatically calculated.
I want some formulas (preferably not VBA code) that checks the main list on Sheet3, sees if the 1st, 2nd or 3rd chaser is due out today or tomorrow, and then add it to the relevant list for the relevant date on Sheet1.
For example, if I went into Sheet1 today (2nd November 2008), the data I would see might look something like the following:
Sheet1
A
B
C
D
3
CASES DUE TO BE CHASED TODAY
4
5
1st Chasers:
6
Case Ref
Type
Chaser 1 Due
Chaser 1 Done
7
1
Letters
02 November 2008
8
2
Letters
02 November 2008
9
3
Letters
02 November 2008
10
4
Letters
02 November 2008
11
12
2nd Chasers:
13
Case Ref
Type
Chaser 2 Due
Chaser 2 Done
14
5
Memos
02 November 2008
15
6
Memos
02 November 2008
16
7
Letters
02 November 2008
17
8
Letters
02 November 2008
18
19
3rd Chasers:
20
Case Ref
Type
Chaser 3 Due
Chaser 3 Done
21
9
Letters
02 November 2008
22
10
Letters
02 November 2008
23
11
Memos
02 November 2008
24
12
Memos
02 November 2008
25
26
CASES DUE TO BE CHASED TOMORROW
27
1st Chasers:
28
Case Ref
Type
Chaser 1 Due
Chaser 1 Done
29
13
Letters
03 November 2008
30
14
Memos
03 November 2008
31
32
2nd Chasers:
33
Case Ref
Type
Chaser 2 Due
Chaser 2 Done
34
15
Letters
03 November 2008
35
36
3rd Chasers:
37
Case Ref
Type
Chaser 3 Due
Chaser 3 Done
38
None
I have tried playing about with LOOKUPs but didnt get very far.
I have looked into Excel's Advanced Filter, but that didnt work either and I'd have to keep applying that filter manually, even if I did get it to work.
Please let me know if I wasn't clear enough.
I would appreciate anyone's help on this, even to just point me in the right direction for a formula which I could then go and research, or if someone's feeling really helpful, if you could provide me with 1 or 2 formulas, I can obviously adapt it for the rest of the sheet myself.
Thanks in advance!!
