Drag formula, change sheet

[lamdl]

Hey guys,

I am trying to drag the formula vertically in Sheet 1 B2. However, I would like the table to change to the table associated with the correct partner. I want the formula to stay the same with the exception of the table the data is pulled from.

Example.

Partner 1 formula: =SUMPRODUCT(Table1[ALL PARTNER AVG],Table1[WEIGHT])/SUM(Table1[WEIGHT])
Partner 2 formula: =SUMPRODUCT(Table2[ALL PARTNER AVG],Table2[WEIGHT])/SUM(Table2[WEIGHT])

Workbook: Book1.xlsx

Thanks in advance for your help!

[FDibbins]

It looks like you are trying to take an average, based on other averages. This is mathematically incorrect - to be accurate, you need to get the base data and base the average on that...total weight/total people.

Also, this would be a lot simpler if you had all the data in 1 table, instead of 1 on each sheet. Then you could do something like this...

	E	F	G
1	Partner 1	27.80602007	303
2	Partner 2	29.4125	401
3	Partner 3	13.04641834	1915
4	Partner 2	30.99814471	575
5	Partner 2	36.58558559	116
6	Partner 2	10.31560284	314
7	Partner 2	18.66666667	35
8	Partner 3	18.42045455	274
9	Partner 4	13.04641834	1915
10	Partner 5	14.01396113	3896
11	Partner 6	10.31560284	314

	A	B	C
1	PARTNER	AVG FOR OPERATING UTILITIES	Column1
2	Partner 1	17.25984	27.80602
3	Partner 2		26.48678
4	Partner 3	17.25984	26.88091

C2=SUMPRODUCT(($E$1:$E$11=A2)*(F1:F11)*(G1:G11))/SUMIF($E$1:$E$11,A2,$G$1:$G$11)

[FDibbins]

Hmmm another option, pretty much leaving everything as is.

1. Remove the space in teh Partner numbers on sheet1
2. Change the table name to match the partner number (partnet1, partner2 etc - you cannot use spaces here)
3. change your formula to this...
=SUMPRODUCT(INDIRECT(A2&"[ALL PARTNER AVG]"),INDIRECT(A2&"[WEIGHT]"))/ SUM(INDIRECT(A2&"[WEIGHT]"))

[lamdl]

Thanks for your help. I made things a little easier for myself by putting the calculation the partner specific sheets.

Now I am just trying to reference that value on the summary sheet.

How do I create a formula so just the sheet changes but the reference cell changes the same?

Example in B2, B3, B4. Dragging formula down:

='Partner1'!$P$7
='Partner2'!$P$7
='Partner3'!$P$7

[FDibbins]

Did you see my post # 3?

For your latest question...
=INDIRECT("'"&A2&"'!P7")

[lamdl]

Thanks! that works.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.