software

Worksheet One contains survey data pertaining to customer satisfaction for
all of our building communities. The data is entered in each row as follows..
.. community name, lot number, buyer name, etc. Therefore, this worksheet
contains all the survey results for all of our buyers, and then based on the
survey responses, an overall rating is calculated. I would like to then link
each row to its corresponding worksheet per community. By doing this, I can
calculate the survey ratings per community as opposed to the overall rating
calculated on worksheet One.

I would greatly appreciate any assistance on this topic.

Thanks


Don't know if this will help but you could calculate each individual
community with formulas, either sumproduct or an array formula
quot;Averagequot;.

e.g.
=SUMPRODUCT(--(A1:A100=E1),D1100)/COUNTIF(A1:A100,E1)
or
=AVERAGE(IF(A1:A100=E1,D1100))
entered with CTRL SHIFT ENTER

where
E1 = a community name
A1:A100 = community name column
D1100 = ratings

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498893Hey there,

Thanks... this actually will help me in another application, but it's not
exactly what I needed. Basically, I have one worksheet that acts as data
entry. All the communities and all the survey results. This sheet gives me
an over all company evaluation. I then would like to have a worksheet per
community, so I am trying to link community specific information to the
corresponding worksheet without having to cut and paste to hthosoe worksheets.
Any ideas? Thanks for your help up to this point.

pinmaster wrote:
gt;Don't know if this will help but you could calculate each individual
gt;community with formulas, either sumproduct or an array formula
gt;quot;Averagequot;.
gt;
gt;e.g.
gt;=SUMPRODUCT(--(A1:A100=E1),D1100)/COUNTIF(A1:A100,E1)
gt;or
gt;=AVERAGE(IF(A1:A100=E1,D1100))
gt;entered with CTRL SHIFT ENTER
gt;
gt;where
gt;E1 = a community name
gt;A1:A100 = community name column
gt;D1100 = ratings
gt;
gt;HTH
gt;JG
gt;

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200601/1

I'm sure there is a better way but what I would do is this:
Assuming your community names are in column A, then select column A and
insert a blank column. Now starting on the same row as your 1st community
name, say row 2 type: =IF(B2lt;gt;quot;quot;,COUNTIF($B$2:B2,B2)amp;B2,quot;quot;) copy down as far
as needed, what that will do is create unique records which you can then use
in a VLOOLUP formulas to pull every records for specific communities. So
let's say that you want to pull every record for 1 of the community in it's
own sheet, you would then put the name of the community in a cell...say A1
then use this formula in A2:
=IF(ISERROR(VLOOKUP(ROW(1:1)amp;$A$1,Sheet2!$A1:$F$10 0,COLUMN(C1),0)),quot;quot;,VLOOKUP(ROW(1:1)amp;$A$1,Sheet2!$ A1:$F$1000,COLUMN(C1),0))
copied down and across
where $A$1 contains the name of the community
Sheet2!$A$1:$F$100 is the range will all the data....ajust to your needs
COLUMN(C1) will pull data from the 3rd column of the data sheet, if you need
to pull the community name as well then chance C1 to B1, but since each
community sheet will only pull it's own data there would be no need to pull
the name also.
Try that and let me know how it goes.

Note. you can hide the new inserted column once you have copied the formula
down.

Hope this helps!
JG

quot;Slovenc0417quot; wrote:

gt; Worksheet One contains survey data pertaining to customer satisfaction for
gt; all of our building communities. The data is entered in each row as follows..
gt; .. community name, lot number, buyer name, etc. Therefore, this worksheet
gt; contains all the survey results for all of our buyers, and then based on the
gt; survey responses, an overall rating is calculated. I would like to then link
gt; each row to its corresponding worksheet per community. By doing this, I can
gt; calculate the survey ratings per community as opposed to the overall rating
gt; calculated on worksheet One.
gt;
gt; I would greatly appreciate any assistance on this topic.
gt;
gt; Thanks
gt;

Excellent!!!

This works perfectly. I really appreciate your assistance!

Rob

pinmaster wrote:
gt;I'm sure there is a better way but what I would do is this:
gt;Assuming your community names are in column A, then select column A and
gt;insert a blank column. Now starting on the same row as your 1st community
gt;name, say row 2 type: =IF(B2lt;gt;quot;quot;,COUNTIF($B$2:B2,B2)amp;B2,quot;quot;) copy down as far
gt;as needed, what that will do is create unique records which you can then use
gt;in a VLOOLUP formulas to pull every records for specific communities. So
gt;let's say that you want to pull every record for 1 of the community in it's
gt;own sheet, you would then put the name of the community in a cell...say A1
gt;then use this formula in A2:
gt;=IF(ISERROR(VLOOKUP(ROW(1:1)amp;$A$1,Sheet2!$A1:$F$1 00,COLUMN(C1),0)),quot;quot;,VLOOKUP(ROW(1:1)amp;$A$1,Sheet2! $A1:$F$1000,COLUMN(C1),0))
gt;copied down and across
gt;where $A$1 contains the name of the community
gt;Sheet2!$A$1:$F$100 is the range will all the data....ajust to your needs
gt;COLUMN(C1) will pull data from the 3rd column of the data sheet, if you need
gt;to pull the community name as well then chance C1 to B1, but since each
gt;community sheet will only pull it's own data there would be no need to pull
gt;the name also.
gt;Try that and let me know how it goes.
gt;
gt;Note. you can hide the new inserted column once you have copied the formula
gt;down.
gt;
gt;Hope this helps!
gt;JG
gt;
gt;gt; Worksheet One contains survey data pertaining to customer satisfaction for
gt;gt; all of our building communities. The data is entered in each row as follows..
gt;[quoted text clipped - 8 lines]
gt;gt;
gt;gt; Thanks

--
Message posted via www.officekb.com

You are welcome, and thanks for the feedback it is much appreciated.

Regards
JG

quot;Slovenc0417 via OfficeKB.comquot; wrote:

gt; Excellent!!!
gt;
gt; This works perfectly. I really appreciate your assistance!
gt;
gt; Rob
gt;
gt; pinmaster wrote:
gt; gt;I'm sure there is a better way but what I would do is this:
gt; gt;Assuming your community names are in column A, then select column A and
gt; gt;insert a blank column. Now starting on the same row as your 1st community
gt; gt;name, say row 2 type: =IF(B2lt;gt;quot;quot;,COUNTIF($B$2:B2,B2)amp;B2,quot;quot;) copy down as far
gt; gt;as needed, what that will do is create unique records which you can then use
gt; gt;in a VLOOLUP formulas to pull every records for specific communities. So
gt; gt;let's say that you want to pull every record for 1 of the community in it's
gt; gt;own sheet, you would then put the name of the community in a cell...say A1
gt; gt;then use this formula in A2:
gt; gt;=IF(ISERROR(VLOOKUP(ROW(1:1)amp;$A$1,Sheet2!$A1:$F$1 00,COLUMN(C1),0)),quot;quot;,VLOOKUP(ROW(1:1)amp;$A$1,Sheet2! $A1:$F$1000,COLUMN(C1),0))
gt; gt;copied down and across
gt; gt;where $A$1 contains the name of the community
gt; gt;Sheet2!$A$1:$F$100 is the range will all the data....ajust to your needs
gt; gt;COLUMN(C1) will pull data from the 3rd column of the data sheet, if you need
gt; gt;to pull the community name as well then chance C1 to B1, but since each
gt; gt;community sheet will only pull it's own data there would be no need to pull
gt; gt;the name also.
gt; gt;Try that and let me know how it goes.
gt; gt;
gt; gt;Note. you can hide the new inserted column once you have copied the formula
gt; gt;down.
gt; gt;
gt; gt;Hope this helps!
gt; gt;JG
gt; gt;
gt; gt;gt; Worksheet One contains survey data pertaining to customer satisfaction for
gt; gt;gt; all of our building communities. The data is entered in each row as follows..
gt; gt;[quoted text clipped - 8 lines]
gt; gt;gt;
gt; gt;gt; Thanks
gt;
gt; --
gt; Message posted via www.officekb.com
gt;