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
"Average".

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

where
E1 = a community name
A1:A100 = community name column
D1:D100 = ratings

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498893

Hey 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:
Don't know if this will help but you could calculate each individual
community with formulas, either sumproduct or an array formula
"Average".

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

where
E1 = a community name
A1:A100 = community name column
D1:D100 = ratings

HTH
JG


--
Message posted via OfficeKB.com
http://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(B2<"",COUNTIF($B$2:B2,B2)&B2,"") 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)&$A$1,Sheet2!$A1:$F$10 0,COLUMN(C1),0)),"",VLOOKUP(ROW(1:1)&$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

"Slovenc0417" wrote:

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

Excellent!!!

This works perfectly. I really appreciate your assistance!

Rob

pinmaster wrote:
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(B2<"",COUNTIF($B$2:B2,B2)&B2,"") 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)&$A$1,Sheet2!$A1:$F$1 00,COLUMN(C1),0)),"",VLOOKUP(ROW(1:1)&$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

Worksheet One contains survey data pertaining to customer satisfaction for
all of our building communities. The data is entered in each row as follows..
[quoted text clipped - 8 lines]

Thanks

--
Message posted via http://www.officekb.com

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

Regards
JG

"Slovenc0417 via OfficeKB.com" wrote:

Excellent!!!

This works perfectly. I really appreciate your assistance!

Rob

pinmaster wrote:
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(B2<"",COUNTIF($B$2:B2,B2)&B2,"") 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)&$A$1,Sheet2!$A1:$F$1 00,COLUMN(C1),0)),"",VLOOKUP(ROW(1:1)&$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

Worksheet One contains survey data pertaining to customer satisfaction for
all of our building communities. The data is entered in each row as follows..
[quoted text clipped - 8 lines]

Thanks

--
Message posted via http://www.officekb.com