 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Display current monthly Data
Ok, I want to have Sheet1 display Employee Name (A1), Date of Hire (B1) and Years of Service (C1). then in each row show the info like this example: Employee Name, Date of Hire, Years of service Mike shmo, 4/10/2000, 8 I have this Sheet1 done and the code im using to get the "Years of service" is =DATEDIF(B2,TODAY(),"m")/12. Now what i cant figure out is Sheet2 that will auto populate whos aniverseries from Sheet1 that are in the current month and display them just like they are in Sheet1. Thanks, Chad Source table in Sheet1 as posted, data from row2 down with hire dates in B2 down In Sheet2, In A2: =IF(Sheet1!B2="","",IF(MONTH(Sheet1!B2)=MONTH(TODAY()),ROW(),"")) Leave A1 blank In B2: =IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1)))) Copy B2 to D2. Select A2:D2, fill down to cover the max expected extent of data in Sheet1, eg down to D200? Format col C as dates to taste. Hide away/minimize col A. Cols B to D returns the required results, all neatly bunched at the top. As an aside (it doesn't affect the extracts above), in Sheet1's col C, why not just use: =DATEDIF(B2,TODAY(),"y")  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Ok, I want to have Sheet1 display Employee Name (A1), Date of Hire (B1) and > Years of Service (C1). then in each row show the info like this example: [quoted text clipped - 12 lines] > Thanks, > Chad Max, I just wanted to thank you for taking the time to figure this out! It worked perfectly.... SignatureNewbies need extra loven......... > Source table in Sheet1 as posted, data from row2 down > with hire dates in B2 down [quoted text clipped - 30 lines] > > Thanks, > > Chad You're welcome, Chad. Thanks for feeding back. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Max, I just wanted to thank you for taking the time to figure this out! It > worked perfectly.... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.