How to make any formula look at entire column on second sheet

[jonathan92591]

Hello everyone,

How to make any formula look at entire column on second sheet

--

Excel or Access version: Excel 2010

Computer operating system: Windows XP

Sample data:

Sheet 1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	SOS/ASS100321
2	SOS/ASS100321D
3	SOS/ASS100321S
4	Dates		*	*	*	*	*	*	*	*	*
5	Start	5/15/2009	*	*	*	*	*	*	*	*	*
6	End	3/22/2010	*	*	*	*	*	*	*	*	*
SOSASS100321

Sheet 2

Excel Workbook
	A	B	C
1	SOS/ASS100321	SOS/ASS100321D	SOS/ASS100321S
2	SOS/ASS100321	5/15/2009	1
3	SOS/ASS100321	5/21/2009	1
4	SOS/ASS100321	6/3/2009	1
5	SOS/ASS100321	6/8/2009	2
6	SOS/ASS100321	6/23/2009	1
7	SOS/ASS100321	6/25/2009	1
8	SOS/ASS100321	6/26/2009	1
9	SOS/ASS100321	6/29/2009	2
10	SOS/ASS100321	6/30/2009	2
11	SOS/ASS100321	7/2/2009	2
12	SOS/ASS100321	7/3/2009	1
13	SOS/ASS100321	7/7/2009	1
14	SOS/ASS100321	7/11/2009	1
15	SOS/ASS100321	7/13/2009	1
16	SOS/ASS100321	7/16/2009	1
17	SOS/ASS100321	7/27/2009	1
18	SOS/ASS100321	7/28/2009	3
19	SOS/ASS100321	7/31/2009	2
20	SOS/ASS100321	8/1/2009	2
21	SOS/ASS100321	8/13/2009	1
22	SOS/ASS100321	8/23/2009	4
23	SOS/ASS100321	8/25/2009	1
24	SOS/ASS100321	9/9/2009	2
25	SOS/ASS100321	9/14/2009	1
26	SOS/ASS100321	9/15/2009	1
27	SOS/ASS100321	9/17/2009	2
28	SOS/ASS100321	9/18/2009	1
29	SOS/ASS100321	9/22/2009	2
30	SOS/ASS100321	9/23/2009	3
31	SOS/ASS100321	9/30/2009	1
32	SOS/ASS100321	10/5/2009	2
33	SOS/ASS100321	10/8/2009	1
34	SOS/ASS100321	10/9/2009	1
35	SOS/ASS100321	10/12/2009	4
36	SOS/ASS100321	10/13/2009	2
37	SOS/ASS100321	10/19/2009	2
38	SOS/ASS100321	10/28/2009	2
39	SOS/ASS100321	10/29/2009	4
40	SOS/ASS100321	10/30/2009	2
41	SOS/ASS100321	11/6/2009	1
42	SOS/ASS100321	11/11/2009	2
43	SOS/ASS100321	11/23/2009	2
44	SOS/ASS100321	11/24/2009	2
45	SOS/ASS100321	11/28/2009	1
46	SOS/ASS100321	12/2/2009	1
47	SOS/ASS100321	12/3/2009	1
48	SOS/ASS100321	12/4/2009	1
49	SOS/ASS100321	12/7/2009	2
50	SOS/ASS100321	12/8/2009	1
51	SOS/ASS100321	12/9/2009	2
52	SOS/ASS100321	12/11/2009	2
53	SOS/ASS100321	12/17/2009	3
54	SOS/ASS100321	12/21/2009	2
55	SOS/ASS100321	12/22/2009	1
56	SOS/ASS100321	12/23/2009	2
57	SOS/ASS100321	12/30/2009	1
58	SOS/ASS100321	12/31/2009	3
59	SOS/ASS100321	1/4/2010	8
60	SOS/ASS100321	1/5/2010	2
61	SOS/ASS100321	1/6/2010	2
62	SOS/ASS100321	1/7/2010	1
63	SOS/ASS100321	1/11/2010	1
64	SOS/ASS100321	1/12/2010	2
65	SOS/ASS100321	1/18/2010	2
66	SOS/ASS100321	1/19/2010	2
67	SOS/ASS100321	1/20/2010	4
68	SOS/ASS100321	1/21/2010	1
69	SOS/ASS100321	1/22/2010	1
70	SOS/ASS100321	1/25/2010	6
71	SOS/ASS100321	1/26/2010	3
72	SOS/ASS100321	1/27/2010	2
73	SOS/ASS100321	1/28/2010	2
74	SOS/ASS100321	1/29/2010	3
75	SOS/ASS100321	2/2/2010	2
76	SOS/ASS100321	2/3/2010	1
77	SOS/ASS100321	2/8/2010	3
78	SOS/ASS100321	2/9/2010	1
79	SOS/ASS100321	2/10/2010	7
80	SOS/ASS100321	2/11/2010	2
81	SOS/ASS100321	2/12/2010	1
82	SOS/ASS100321	2/15/2010	1
83	SOS/ASS100321	2/16/2010	1
84	SOS/ASS100321	2/17/2010	1
85	SOS/ASS100321	2/18/2010	1
86	SOS/ASS100321	2/22/2010	2
87	SOS/ASS100321	2/23/2010	2
88	SOS/ASS100321	2/24/2010	1
89	SOS/ASS100321	3/1/2010	2
90	SOS/ASS100321	3/4/2010	1
91	SOS/ASS100321	3/5/2010	1
92	SOS/ASS100321	3/8/2010	2
93	SOS/ASS100321	3/12/2010	2
94	SOS/ASS100321	3/15/2010	4
95	SOS/ASS100321	3/16/2010	1
96	SOS/ASS100321	3/17/2010	2
97	SOS/ASS100321	3/19/2010	1
98	SOS/ASS100321	3/21/2010	1
99	SOS/ASS100321	3/22/2010	1
Data

Formula(s) right now: = SMALL (Data!$B:$B,1)

Current result(s): 5/15/2009

My goal: Have multiple formulas on sheet 1 look at sheet 2 and sum/average/small/large/ the column for each product so I can copy the sheet and change the product's name. All of the data for each product will be on one sheet. Then there will be a drilldown for each product. If I could make this a form factor it would save me so much time and reduce errors.

Error message: No error message

How error occurred: No error message

Generated in: Excel

Thank you.

JT

 

[jonathan92591]

I found some information after my post that may help.

http://www.mrexcel.com/forum/showthread.php?t=544265

 

[jonathan92591]

Is it possible to use lookup, index/match, to find oldest date based off a reference cell?

I'm trying to find the oldest date for hundreds of products.

My data sheet looks like this:

Product A Sale Date (Column 1)
11-10-11
11-12-11
11-13-11

Product A Sales on Date (Column 2)
4
5
8

Column 3 would be Product B Sale Date and Column 4 would be Product B Sales on Date and so forth. There are over 100, so, 200 columns. Date then number, date then number, etc.

I have a form factor sheet that is based on the start and end dates, or "small" and "large" formulas as I'm currently using. But I have to manually select the columns which is time consuming and could lead to errors. It would be neat if I could have the "small" and "large" formula target a specific column based on a reference sales such as Product A Sale Date or Product H Sale Date, etc.