|
LOOKUP PROBLEM
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. |
LOOKUP PROBLEM
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 -- Thanks for your help "Norbert" wrote: 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. |
LOOKUP PROBLEM
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" wrote: 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. |
LOOKUP PROBLEM
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 wrote: 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" wrote: 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. |
LOOKUP PROBLEM
Hi,
You can't use full columns woth sumproduct. Shorten to A1:A2000 or something less than a full column. Mike "Norbert" wrote: 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 wrote: 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" wrote: 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. |
LOOKUP PROBLEM
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 wrote: Hi, You can't use full columns woth sumproduct. Shorten to A1:A2000 or something less than a full column. Mike "Norbert" wrote: 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 wrote: 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" wrote: 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. |
LOOKUP PROBLEM
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" wrote: 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 wrote: Hi, You can't use full columns woth sumproduct. Shorten to A1:A2000 or something less than a full column. Mike "Norbert" wrote: 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 wrote: 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" wrote: 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. |
LOOKUP PROBLEM
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 Mike H wrote: 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" wrote: 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 wrote: Hi, You can't use full columns woth sumproduct. Shorten to A1:A2000 or something less than a full column. Mike "Norbert" wrote: 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 wrote: 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" wrote: 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. |
| All times are GMT +1. The time now is 07:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com