Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interesting problem in excel, producing a list for aspecific date
Hi probably a silly one but i want to make a list of people to be seen on a
specific date, my data is in a table where column one is a date list and subsequent columns are headed with clients names. Data is recorded against a date as the number of reviewsthatclient has had so the sheet look something like this AB CD EF GH IJ 01/01 1 3 1 01/02 2 4 01/03 4 2 01/04 2 3 01/05 5 It would be great ro be able to type in a date and have a listof those to be seen and whatnumber review it is produced to print off: to look alittle like this: Date Client Review No 01/04 AB 2 01/04 CD 3 Cananyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interesting problem in excel, producing a list for aspecific date
since no one else has answered...............
could you use auto filter on the dates? it wouldn't produce results in the same format that you mentioned in your 2nd example, it would be the same format as the original list. just an idea. :) susan On Aug 17, 8:56*am, mark wrote: Hi probably a silly one but i want to make a list of people to be seen on a specific date, my data is in a table where column one is a date list and subsequent columns are headed with clients names. Data is recorded against a date as the number of reviewsthatclient has had so the sheet look *something like this * * * * * *AB * *CD * * *EF * * GH * * *IJ 01/01 * *1 * * * * * * * *3 * * * * * * * *1 01/02 * * * * * *2 * * * * * * * * 4 01/03 * * * * * * * * * * *4 * * * * * * * *2 01/04 * *2 * * *3 * * * * * * * * * * * * 01/05 * * * * * * * * * * * * * * * 5 It would be great ro be able to type in a date and have a listof those to be seen and whatnumber review it is produced to print off: to look alittle like this: Date * * Client * * * Review No 01/04 * * AB * * * * * * *2 01/04 * * CD * * * * * * *3 Cananyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interesting problem in excel, producing a list for aspecific date
Try this...
Data in the range A1:F6 Names used in the formulas: Dates: refers to $A$1:$A$6 Client: refers to $B$1:$F$1 Review: refers to $B$2:$F$6 A10 = some date Enter this formula in B10. This will return the count of records for the date entered in A10: =COUNT(INDEX(Review,MATCH(A10,Dates,0),0)) Enter this array formula** in C10. This will return the client names: =IF(ROWS(C$10:C10)B$10,"",INDEX(Client,SMALL(IF(I NDEX(Review,MATCH(A$10,Dates,0),0)<"",COLUMN(Clie nt)),ROWS(C$10:C10))-MIN(COLUMN(Client))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Enter this formula in D10. This will return the review numbers: =IF(C10="","",INDEX(Review,MATCH(A$10,Dates,0),MAT CH(C10,Client,0))) Select both C10 and D10 and copy down until you get blanks. You'll have to copy down to a number of rows that is at least equal to max number of reviews for any date. -- Biff Microsoft Excel MVP "mark" wrote in message ... Hi probably a silly one but i want to make a list of people to be seen on a specific date, my data is in a table where column one is a date list and subsequent columns are headed with clients names. Data is recorded against a date as the number of reviewsthatclient has had so the sheet look something like this AB CD EF GH IJ 01/01 1 3 1 01/02 2 4 01/03 4 2 01/04 2 3 01/05 5 It would be great ro be able to type in a date and have a listof those to be seen and whatnumber review it is produced to print off: to look alittle like this: Date Client Review No 01/04 AB 2 01/04 CD 3 Cananyone help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interesting problem in excel, producing a list for aspecific date
Excel 2007 PivotTable
Interestinglynoformulasrequired! http://www.mediafire.com/file/xmyztm2mr0y/08_17_09.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interesting Custom Format Problem | Excel Discussion (Misc queries) | |||
Interesting Formula Problem | Excel Worksheet Functions | |||
Very interesting problem that should be a snap to figure out! | Excel Discussion (Misc queries) | |||
Interesting Workbook formula problem | Excel Worksheet Functions | |||
Interesting TIF file problem | Excel Discussion (Misc queries) |