Multiple formulas on sheet 1 referencing data on sheet 2

jonathan92591

Board Regular
Joined
Oct 27, 2011
Messages
65
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
 

Some videos you may like

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
Joined
Jun 29, 2009
Messages
7,256
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
Joined
Oct 27, 2011
Messages
65
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
Joined
Jun 29, 2009
Messages
7,256
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>
 

Watch MrExcel Video

Forum statistics

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