LOOKUP PROBLEM

  • Thread starter Thread starter Norbert
  • Start date Start date
N

Norbert

For every day I keep records of machine down-time for 20 machines
on worksheet: "downtime history"

A B C
D
date mach.no. down-time day-shift down-time night-shift
07-10-08 12 03:00 06:00
07-10-08 13 00:00 01:10
07-10-08 14 02:20 00:10
and so on.....


On my analysis sheet "efficiencies" I can change the date in cell: B3
in L3:L26 I have the machine numbers. In M3:M26 I need the downtime
for each machine for the date in B3.

Is that possible to lookup in the database I keep on sheet: "downtime
history"?

To be answered:
What was the <downtime day-shift> for machine 12 on the date in B3?
I basically have to lookup 2 values; the date and the machine and that
is where my problem is.

Thanks.
 
The way I would do it- on your data source sheet insert a column that says
A1&B1- this makes a string unique to that machine for that day.
On you cover report get the Vlookup to look to the field that you insert the
date& the machine no
e.g vlookup($B$5&$A7,Range of data source,0.1) (basic version vlookup)
This will then pull in the info for that day only- need to ensure that if a
machine goes down twice that the data sheet is already consolidated as the
vllokup will pick up the first line for a machine on a day only

Hope that helps
 
Hi,

Put this in M3 on your Effeciencies sheet and drag down.

=SUMPRODUCT(('downtime history'!$A$2:$A$20=$B$3)*('downtime
history'!$B$2:$B$20=L3)*('downtime history'!$C$2:$C$20+'downtime
history'!$D$2:$D$20))


It all goes on one line.

Mike
 
I changed your formula slightly to:
=SUMPRODUCT(('down-time history'!A:A=$B$3)*('down-time
history'!B:B=L7)*('down-time history'!C:C))
and I'm getting a: #NUM!
Why?

'down-time history'!A:A : here are all the dates below each other (20
x 01-01-08, then 20 x 02-01-08 and so on....)
$B$3 is the date (I want to change) on my analysis sheet
'down-time history'!B:B : here are the machine numbers stored
'down-time history'!C:C : here are the down-times for day-shift stored
 
Hi,

You can't use full columns woth sumproduct. Shorten to A1:A2000 or
something less than a full column.

Mike
 
Hi Mike,
I changed to:
=SUMPRODUCT(('down-time history'!A3:A10000=$B$3)*('down-time
history'!B3:B10000=L7)*('down-time history'!C3:C10000))

now I'm getting: #VALUE!
 
Hi,

I suspect that your times aren't really times and are text that look like
times.
Try this

=isnumber(c4) where C4 contains a time

It should evaluate as TRUE but if it's text will evaluate as FALSE

Mike
 
Hi Mike,
no luck! All the cells involved in the formula evaluate as TRUE.

Ok, now I found the problem.
There was a "*" instead of a comma.
=SUMPRODUCT(('down-time history'!A3:A10000=$B$3)*('down-time
history'!B3:B10000=L7) , ('down-time history'!C3:C10000))

Thanks a lot!

Regards,
Norbert
 
Back
Top