Reference function

[monkey_wrench]

Below are two spreadsheets. Spreadsheet 1 contains a date field and Manufacture A, B and C as headers. Under each manufacture are the hours worked and the corresponding dates of the hours that were worked.

Spreadsheet 2 is a table that I would like to reference spreadsheet 1 using “lookup functions”. Referencing the Manufacture and total hours is an easy reference to make. The problem is automating the lookup for the Start date and Finish date. What I would like it to do for the START DATE is to look for the first hours worked and return the corresponding date, I.E. Manufacture A would be 1/12/04, Manufacture B would be 1/13/04, etc. I would also like to do the same thing for the DATE FINISHED I.E Finish date for Manufacture A would be 1/17/04, man B would be 1/16/04 and Man C would be 1/15/04. how can I do this???????

-----------------------------------------------
Spreadsheet 1
------------------------------------------------

_Date _| A | B | C |

1/12/04 | 4 | 0 | 0 |
1/13/04 | 4 | 2 | 0 |
1/14/04 | 2 | 6 | 1 |
1/15/04 | 0 | 6 | 5 |
1/16/04 | 0 | 5 | 0 |
1/17/14 | 4 | 0 | 0 |


-------------------------------------------
spreadsheet 2
--------------------------------------------

Manuf | start date| finish date| Total hours|

____ ____________________________________
Manu A | 1/12/04 | 1/17/14 | 14
_________________________________________
Manu B | 1/13/04 | 1/16/04 | 19
_________________________________________
Manu C | 1/14/04 | 1/15/04 | 6

 

[Egress1]

Howdy,

I couldn't figure out how to do this with LOOKUP function since the hour numbers are variable and LOOKUP requires a sorted list but here it is using nested IF statements. HTH

lookup.xls
	A	B	C	D	E	F	G	H	I
1	Date	A	B	C		MANU	START	FINISH	HOURS
2	01/12/04	4	0	0		A	01/12/04	01/17/04	14.00
3	01/13/04	4	2	0		B	01/13/04	01/16/04	19.00
4	01/14/04	2	6	1		C	01/14/04	01/15/04	6.00
5	01/15/04	0	6	5
6	01/16/04	0	5	0
7	01/17/04	4	0	0
8	01/18/04	0	0	0
Sheet1

 

[Aladin Akyurek]

Let A1:D8 on Sheet1 house the sample you provided, including labels.

Sheet2

Book1
	A	B	C	D	E
1		MANU	START	FINISH	HOURS
2	1	A	1/12/2004	1/17/2004	14
3	2	B	1/13/2004	1/16/2004	19
4	3	C	1/14/2004	1/15/2004	6
Sheet2

Formulas...

A2:

=MATCH($B2,Sheet1!$B$1:$D$1,0)

C2:

=INDEX(Sheet1!$A$2:$A$8,MIN(IF(INDEX(Sheet1!$B$2:$D$8,0,$A2)>0,ROW(INDEX(Sheet1!$B$2:$D$8,0,$A2))))-(CELL("Row",Sheet1!B$2)-1))

which must be confirmed with control+shift+enter instead of just with enter.

D2:

=INDEX(Sheet1!$A$2:$A$8,MAX(IF(INDEX(Sheet1!$B$2:$D$8,0,$A2)>0,ROW(INDEX(Sheet1!$B$2:$D$8,0,$A2))))-(CELL("Row",Sheet1!C$2)-1))

Again confirm with control+shift+enter.

E2:

=SUM(INDEX(Sheet1!$B$2:$D$8,0,$A2))

 

[monkey_wrench]

Just wanted to thank you guys for the help...I will try both the formulas and see which one works better for me...thanks again
-Gerrit