assuming data is on sheet1 and you want dates in the right format, in your summary table enter the following as an array formula (ie with ctrl-shift-enter) in cell B4 and copy down
=TEXT(MAX(($A4=sheet1!$B$2:$B$13)*sheet1!$A$2:$A$13),"ddmmme")
this has the advantage that it doesn't need refreshing like a pivot table does to show the latest informtion
Discussion on:
View:
Show:
I generally avoid arrays, but don't mind sharing them with others. Thanks!
Just do a custom sort by personnel then by date. Then it is easy to pick out the last (and first if you want) entry for each person.
My project sheet, is soundling similar to yours, but the area I cant seem to get down is this.
Multipule sections/offices/ entities will be updating thier respective colums of information, and what I WANT to happen is an automatic date populate in a predetermined cell. That way you can see that some value has changed.
Let say that HR does daily
personel attendance, they input into cells B5-B10
And Outside plant does the same in C5-C10,
Houskeeping updates cells D5-D10,
Under each cell (say B11 and C11 , D11) the date it was modified appear there.
The formula is what I'm missing. I am not understainfg how to make this happen.
Multipule sections/offices/ entities will be updating thier respective colums of information, and what I WANT to happen is an automatic date populate in a predetermined cell. That way you can see that some value has changed.
Let say that HR does daily
personel attendance, they input into cells B5-B10
And Outside plant does the same in C5-C10,
Houskeeping updates cells D5-D10,
Under each cell (say B11 and C11 , D11) the date it was modified appear there.
The formula is what I'm missing. I am not understainfg how to make this happen.
You could use a circular formula with calculation set to iteration, but that has very limited context. You could use VBA's Worksheet Change event, but that's cumbersome at best. I recommend that you create a data entry user form and let the form do the calculating. Your users might find data entry a lot easier using a form.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
