Any ideas?

  • Thread starter Thread starter Roger Dodger
  • Start date Start date
R

Roger Dodger

Hi all,

I am currently using a spreadsheet that was created by my predecessor and it
is close to useless. The problem is that I can't think of a way to do it
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 and you
requested it earlier than anyone else who wants their first tank, you are 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
 
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
 
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


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
 
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


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











- Show quoted text -
Click to expand...

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
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
 
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
 
Back
Top