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
 

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>
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top