LOOKUP PROBLEM

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

 

[David]

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

 

[Mike H]

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

 

[Norbert]

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

 

[Mike H]

Hi,

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

Mike

 

[Norbert]

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!

 

[Mike H]

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

 

[Norbert]

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