# Multiple formulas on sheet 1 referencing data on sheet 2

#### jonathan92591

##### Board Regular
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

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Robert Mika

##### MrExcel MVP
Is this layout of your Sheet2?
Excel Workbook
AB
1ProductData
2a1
3b2
4c3
5a1
6b5
7c7
8a3
9b4
10c5
Sheet2

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

#### jonathan92591

##### Board Regular
Hello,

Sheet 2 looks like this:

Excel Workbook
ABCD
1Widget 1 Sale DateWidgets 1 SoldWidget 2 Sale DateWidgets 2 Sold
211/1/2011511/1/20111
311/2/20111011/3/20113
411/3/20111511/5/20115
511/4/20112011/7/20114
611/5/20112511/9/20115
711/6/20113011/11/20116
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
AB
1ProductData
2a1
3b2
4c3
5a1
6b5
7c7
8a3
9b4
10c5
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

##### MrExcel MVP
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>

Replies
9
Views
382
Replies
15
Views
318
Replies
1
Views
264
Replies
25
Views
235
Replies
2
Views
292

1,118,878
Messages
5,574,776
Members
412,617
Latest member
mlharris