Sorry, I've just realised that I gave you some wrong advice -
CTRL<colon> will put the current time into a cell. You should use
CTRL<semi-colon> to get the current date.
Pete
On Sep 3, 1:12*pm, Pete_UK <pashu...@auditel.net> wrote:
> Hi Kevin,
>
> I think you will need one sheet for employees' details (which is
> largely static data) and then another sheet for the tank requests. In
> the Employees sheet you would have columns for Clock card number (a
> unique reference), Name, Department etc., and you would have to supply
> this data yourself. In addition, you could have another column which
> keeps track of the number of tanks taken to date, as a kind of summary
> for each employee - you will need a formula for this (see later).
>
> In the Requests sheet you would have a column for clock card number
> (assume column A), which is validated against the numbers in the
> Employees sheet (you can use Data | Data Validation for this), and
> then a formula can bring across the Name and Department from the
> Employees sheet - something like:
>
> B2: * =IF(A2="","-",VLOOKUP(A2,Employees!A:C,2,0))
>
> to bring the name across, and:
>
> C2: * =IF(A2="","-",VLOOKUP(A2,Employees!A:C,3,0))
>
> for the department. These formulae can be copied down as far as you
> like, and the hyphen will indicate how far you have copied them.
>
> You will need to enter the date requested yourself into column D - an
> easy way is to select the cell, hold the CTRL key and then SHIFT with
> semi-colon (i.e. CTRL<colon>) and this will enter the current date
> into that cell. Column E could be used for cumulative number of tanks
> requested for each employee, and you can do that with this formula in
> E2:
>
> =COUNTIF($A2:A2,A2)
>
> Use column F for date collected, and again use CTRL<colon> to enter
> that date (if today), or just enter the date as and when you know it.
>
> Back to the Employees sheet, the formula for tracking the number of
> collected tanks will be:
>
> =SUMPRODUCT((Requests!F$2:F$200<=TODAY())*(Requests!A$2:A$200=A2))
>
> (i.e. the number collected before today's date for that employee -
> I've assumed up to 200 requests, so adjust the ranges to suit).
>
> I think that just about sums it up.
>
> Hope this helps.
>
> Pete
>
> On Sep 3, 11:56*am, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
>
>
>
> > I would like to start a fresh new workbook. Currently *we have columns for
> > name, employee *department, date requested, Date received. Tank
> > number for *this person.
> > I am just after a usable way to *keep track of this data.Idealy i would
> > enter the clock card number and it would enter their name, department and
> > the date that the entry was made. It would also count how many other entries
> > have been made for that clock card number - this would be the number of
> > tanks taken in the past.
>
> > I see it working like this...
> > Column A would be blank until Column C was entered.
> > Column B would be the date of the entry. So as entry is made in Column C it
> > enters that date in Column B
> > Column C would be clock card number and it would probably use count to
> > establish how many times that number was used and would enter it in Column
> > A.
> > Column D would be name - maybe use lookup to get this.
> > Couumn E would be Department also use lookup
> > Column F would be the date tank was collected.
>
> > Does this help? It all sounds so complex but I am sure that it isn't that
> > difficult.
>
> > Kevin
>
> > "Pete_UK" <pashu...@auditel.net> wrote in message
>
> >news:311848a6-3652-4041-9cc6-(E-Mail Removed)....
> > Kevin,
>
> > you'll need to supply some details about what data you have, how it is
> > organised, what columns you use etc., plus examples of what you want
> > to get out of it. It is usually better to give an example of the data
> > layout that you use.
>
> > Hope this helps.
>
> > Pete
>
> > On Sep 3, 6:47 am, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
>
> > > Hi all,
>
> > > I am currently using a spreadsheet that was created by my predecessorand
> > > it
> > > is close to useless. The problem is that I can't think of a way to doit
> > > better. Can anyone help?
>
> > > We have Tanks that we sell to our employees at a small cost. The hierarchy
> > > system for receiving a tank works as follows
>
> > > Employees getting their first tank,
> > > then by the date requested.
> > > Then employees getting their second tank,
> > > Then by the date requested.
>
> > > So the overall concept is that if you are wanting your first tank andyou
> > > requested it earlier than anyone else who wants their first tank, youare
> > > at
> > > the top of the list.
> > > If you want your fourth tank then you are ahead of anyone who wants their
> > > fifth or more and ahead of anyone who requested their fourth after you.
>
> > > We use clock card numbers to identify employee names. We are currently
> > > having to manually go through the spreadsheet to find what number tank the
> > > person is up to. I am sure excel can do this for us as well as sort the
> > > current order.
>
> > > Does anyone have any ideas and can you understand what I am asking?
>
> > > Thanks in advance
>
> > > Kevin- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Similar Threads
