Multiple formulas on sheet 1 referencing data on sheet 2

[jonathan92591]

Hello,

Let's pretend I had sheet 1 and sheet 2. Sheet 1 has multiple formulas, sum(), average(), etc. Now the data is on sheet two. Each formula points to product 1, 2, 3. So on sheet 2, product 1, 2, 3 have one data in the column. So I need the formulas on sheet 1 to reference sheet 2. I was thinking of match() or vlookup() but I have yet been able to successively do this.

I sincerely appreciate your time and energy. Thank you.

JT

 

[Robert Mika]

Is this layout of your Sheet2?

Excel Workbook
	A	B
1	Product	Data
2	a	1
3	b	2
4	c	3
5	a	1
6	b	5
7	c	7
8	a	3
9	b	4
10	c	5
Sheet2

and your formulas shoudl SUM(), or AVERAGE() any of this product?
or if not, what would you like to achive on Sheet1?

 

[jonathan92591]

Hello,

Sheet 2 looks like this:

Excel Workbook
	A	B	C	D
1	Widget 1 Sale Date	Widgets 1 Sold	Widget 2 Sale Date	Widgets 2 Sold
2	11/1/2011	5	11/1/2011	1
3	11/2/2011	10	11/3/2011	3
4	11/3/2011	15	11/5/2011	5
5	11/4/2011	20	11/7/2011	4
6	11/5/2011	25	11/9/2011	5
7	11/6/2011	30	11/11/2011	6
Sheet 2 (Data)

And yes, Sheet 1 is a series of calculations based on data sets from Sheet 2. Like sold per month, sale range, % of total sold for the given month, etc.

Row 1 on Sheet 2 will have the product for data and sold. So I'd like to type that product ID date and sold so my formula points the to the reference cell then jumps to Sheet 2 to collect the data. If I can do this, I'll be able to duplicate the sheet for each product and simply change the name of the product on Sheet 1 and all of the formulas fill in.

Thank you for helping. I really appreciate it.

JT

Is this layout of your Sheet2?

Excel Workbook
	A	B
1	Product	Data
2	a	1
3	b	2
4	c	3
5	a	1
6	b	5
7	c	7
8	a	3
9	b	4
10	c	5
Sheet2

and your formulas shoudl SUM(), or AVERAGE() any of this product?
or if not, what would you like to achive on Sheet1?

 

[Robert Mika]

Hi jonathan,
Does this help?
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Widgets 1</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Sold</td><td style=";">Widgets 1 Sold</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">SUM</td><td style="text-align: right;;">105</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Average</td><td style="text-align: right;;">17.5</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B3</th><td style="text-align:left">=$A$2&" "&A3</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B4</th><td style="text-align:left">=SUM(OFFSET(Sheet2!$A$1,1,MATCH("*"&B3&"*",Sheet2!$A$1:$D$1,0)-1,50,1))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B5</th><td style="text-align:left">=AVERAGE(OFFSET(Sheet2!$A$1,1,MATCH("*"&B3&"*",Sheet2!$A$1:$D$1,0)-1,50,1))</td></tr></tbody></table></td></tr></tbody></table>