I have a table with two columns. The table starts in column B.
In column B is a dynamic list, let's say company names. If my Excel tool only assessed 5 of 10 companies, then 5 companies will be listed in column B in rows 1:5. In rows 6:10 will be blanks because the assessment didn't include those 5 companies.
In column C is an averageif function based off of columns 1:10.
See the table below:
[TABLE="class: grid, width: 880"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Company (Column B)[/TD]
[TD]Data (Column C)[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A4[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B41,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A5[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B42,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A6[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B43,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A7[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B44,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A8[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B45,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A9[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B46,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A10[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B47,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A11[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B48,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A12[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B49,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A13[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B50,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A14[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B51,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A15[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B52,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A16[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B53,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A17[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B54,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A18[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B55,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A19[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B56,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A20[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B57,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A21[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B58,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
</tbody>[/TABLE]
I would like to be able to order this table based off the values in column B (it doesn't matter if it's largest to smallest or vise versa). This is tricky because column A is linked to another sheet and when I sort column B, it throws off column A. Can I sort this table by adapting my formulas? Or do I have to create a new table?
Thanks so much for your help... I'm at a loss here.
In column B is a dynamic list, let's say company names. If my Excel tool only assessed 5 of 10 companies, then 5 companies will be listed in column B in rows 1:5. In rows 6:10 will be blanks because the assessment didn't include those 5 companies.
In column C is an averageif function based off of columns 1:10.
See the table below:
[TABLE="class: grid, width: 880"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Company (Column B)[/TD]
[TD]Data (Column C)[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A4[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B41,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A5[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B42,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A6[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B43,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A7[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B44,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A8[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B45,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A9[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B46,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A10[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B47,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A11[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B48,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A12[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B49,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A13[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B50,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A14[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B51,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A15[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B52,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A16[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B53,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A17[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B54,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A18[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B55,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A19[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B56,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A20[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B57,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
[TR]
[TD]='LPA Categories & Data Validate'!A21[/TD]
[TD]=IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B58,'Selected LP List'!$I$4:$I$740),"")[/TD]
[/TR]
</tbody>[/TABLE]
I would like to be able to order this table based off the values in column B (it doesn't matter if it's largest to smallest or vise versa). This is tricky because column A is linked to another sheet and when I sort column B, it throws off column A. Can I sort this table by adapting my formulas? Or do I have to create a new table?
Thanks so much for your help... I'm at a loss here.