Linking rows of data to another worksheet

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
0
Slovenc0417
1/6/2006 10:56:30 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
340 Views

Similar Articles

[PageSpeed] 46

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?action=getinfo&userid=6261
View this thread: http://www.excelforum.com/showthread.php?threadid=498893

0
1/7/2006 4:13:59 AM
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.aspx/ms-excel/200601/1
0
Slovenc0417
1/9/2006 3:13:29 PM
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$100,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
> 
0
pinmaster (74)
1/10/2006 1:59:02 AM
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$100,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
0
Slovenc0417
1/10/2006 7:58:55 PM
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$100,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
> 
0
pinmaster (74)
1/10/2006 8:06:06 PM
Reply:

Similar Artilces:

Dynamics GP routing link several BOM's at once
When using routing links allow selection of several items at once to link to a routing step instead of one at a time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=487897...

Freezing? Rows
I have a question pertaining to Excel. I keep track of patients in excel sheet.. I add about 10 patients a week into this sheet. Then I sort it by patients’ name, so the sheet appears alphabetically. I know this is not the best way to keep the data... and I am afraid that by some mistake rows might get shifter and information for each patient will be shifted as well. Is there any way to prevent this kind of disaster from happening? Somehow "freezing" the information for each row for all the columns, so that individual patient's info cannot be shifted so easily? Any sugg...

resize all rows in the spreadsheet to fit the text?
I have an existing spreadsheet created by another user. Some of the rows are not automatically sized to display all text. Is there a command to resize all rows in the spreadsheet to fit the text? thanks email: softwaretest underscore 2003 at hotmail Church lady, try Ctrl a, to select the whole sheet, then format, row, autofit -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "the church lady" <tcl@mail.com&g...

2-D charting with multiple rows
I have a power user who wishes to increase the size of his 2-D graphics but is limited to using 32000 records. He needs to be able to use 64,000 records. Any suggestions? I think this might be something that is changed in regedit but I am not sure. Thanks! Yep, and you can bypass the 256 column limit with Windows API calls. Joking aside, how about two series, formatted identically. If you have duplicate entries in the legend, select the legend, then select the text of one entry, and press the Delete key. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorial...

Update Link
I have got a problem of Excel update link after upgrade to Office 2003. I have create 5 excel files which is linked togather. In the 1st one, it contains raw data, and each of the other make use of the result of the pervious file. 1 --> 2 --> 3 --> 4 --> 5 Data When I open the 5th file, it ask me to update link, form the background I can still see the values, however no matter I choose "update" or "do not update" the value turns into error. I have to open the 4th and 3rd file in order to give my value back. This does not happen when I open the fil...

Linked Forms
Hello, I am doing a project that requires two forms.. The first form contains the data for a business the second form contains data for the business owner... How do I link the two forms together...? Many thanks. Bob Send a common key piece of data from the first form (say the company name) to the second form page and include it in the second form as a hidden form field (then if using a database to store the results link with a relationship the 2 results tables by the common field) For form passing information see http://irt.org/articles/js063/index.htm -- ____...

Posting new data to PM & RM before year-end or fiscal period close
Hi GP Users, If I have already posted some new data before closing the old financial year, what are the possible problems that may happen when I close it? Any solutions to that? Thanks Andrew ...

Link Tables in Report Writer
Hi everyone, Im working with Report Writer in GP10 I read David articles and they are interesting but my question is: for exemple I want to add the "expiration date" to the "POP Receivings Posting journal" report let's assume I found the table containing the "expiration date" how do I get to it when creating table relationships Im new with GP and Im having difficulties what tables to link to get the final table. I really tried everything to figure it out but I didn't succeed I hope I was clear explaning this. thank you guys in advance It sounds li...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

One Line of Data Per Page of Report
When viewing/printing reports reports we are only getting one line of data per page. So instead of having a 5 page report with 60 entires we have a 60 page report with 1 entry per page. Is there an easy way to solve this probelm? We are not very familar with access and need this problem solved. Thanks! On Thu, 17 Jan 2008 11:07:03 -0800, Coutu <Coutu@discussions.microsoft.com> wrote: >When viewing/printing reports reports we are only getting one line of data >per page. So instead of having a 5 page report with 60 entires we have a 60 >page report with 1 entry per pag...

remember path when linking many GIFs to cells?
When I link a number of GIF images residing in a different directory to Excel 2002 cells, the program does not remember the last directory it looked in when a new link is entered. Is there a way to set up a default path for this boring operation? Thanks, z.entropic ...

move rows from column to column
I have Column b with Name in one row and number in 2nd row want to move all numbers to Column a and leave name in Column b without having to move each one seperate. 1500 records. Use a help column, in the first adjacent cell type =ISNUMBER(A2) where A2 is the first cell with name copy down 1500 rows by either dragging the formula or double click the lower right corner of the help cell (as long as it is in an adjacent column) Then apply data>filter>autofilter and filter on FALSE in the help columns, select all visible cells and copy and paste somewhere else, then select TRUE from ...

Hyperlink directly to a cell in a worksheet
Hi there, I'd like to hyperlink directly to a cell in a spreadsheet (spreadsheet opens to a specific cell selected). The link I'm using opens the spreadsheet, but it goes to the same cell everytime. Is there a way to do this? Thanks in advance for any help. Here's what the link I'm using looks like: www.myserver.com/Payroll_Risks_&_Controls.xls#='Risk Scores 1, 2, or 3'!$A$27 Risk Scores 1, 2, or 3 is the name of the worksheet and A27 is the cell I want to go to. ...

How do I set the worksheet to save before closing?
When there is a change in a worksheet and I try to close it, a message asks me whether I want to save the worksheet or not? How do I turn on this notification before closing the sheet? Open your workbook. Hit Alt+F11 or Tools-->Macro-->Visual basic editor (VBE). On the left, double-click ThisWorkbook. Paste the following code into the ThisWorkbook code window. Hit the Save diskette at top-left. Close the VBE. Make a change to the workbook, and close it to test. Was the change saved? Should be. :) Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Save...

Excel Worksheet menu bars customizing for limiting users to optio.
Customizing Excel worksheet menu bar for limiting users to the options provided in that menu Hi Jim Look also at the links to MS pages on the bottom of this page http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jim" <jim@discussions.microsoft.com> wrote in message news:F8009001-5F5A-4415-9527-34C71D8DF1DB@microsoft.com... > Customizing Excel worksheet menu bar for limiting users to the options > provided in that menu ...

Linked Cell Property In Activex controls
Can someone point me to an example showing how this property can be used, linking, as an example, an option button to a specific cell? Say if I wanted "1" to appear in cell B2 of the worksheet if the option button is clicked KG, The option button puts TRUE or FALSE into its Linked Cell. You can get 1 or 0 out of that by referring to it with double negation operators. = --A1 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KG" <KG@discussions.microsoft.com> wrote in message news:924E6B40-6040-4016-B75B...

How do I rotate a worksheet in Excel?
I know that it is possible to rotate content within a cell, but what if I want to rotate the whole worksheet? And yes, I know that I could just set it to landscape mode, but I want to modify it once the rows become columns and the columns become rows. You are limited to 256 columns (and therefore 256 rows). create a new worksheet Select your range on the original worksheet edit|copy go to A1 of the new worksheet edit|paste special|check transpose. MarkRulesTheWorld wrote: > > I know that it is possible to rotate content within a cell, but what if I > want to rotate the whole w...

Highlighting whole row in this macro
Can I add something to this so that when the A1:A100 field is changed, the whole row (A-L) is highlighted ColorIndex 6? Dim x, cl Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub Target.Offset(0, 1) = x Target.Interior.ColorIndex = 6 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) x = ActiveCell.Value End Sub target.entirerow.resize(1,12).interior.colorindex = 6 or since the target is already in column A: target.resize(1,12).interior.colorindex = 6 eastrivergraphics@gmail.com wrote: > &...

Worksheets 02-23-10
Could you possibly have a solution for me. My information is downloaded into excel. When it does it gives the workbook a number as the name of the worksheet. I run a macro and that's no problem, but I wanting to use an additional macro that will add additional worksheets and do other calculations. When I try to add the additional worksheets or reference the numbered worksheet, it won't do it because of the unique number is assigns each time I download the report. Donna Hauff If your running the code with that workbook activated and the single worksheet as the active...

Move Exchange mailboxes from one Domain to another.
Okay, this is the scenario... I got called in to fix a big mess. I am not the one that set this up! (Just wanted to clear that up. ;)) Windows 2003 network.. 2 Domains Domain one: Active Directory DNS DHCP File Server Domain two: Active Directory DNS Exchange Both domains are under separate forests. The person that set this up setup duplicate user accounts under both domains. Why he didn't put the exchange server under the same domain, I have no clue. I just figure he didn't know what he was doing. So what I have is everyone logging in to Domain one to do their everyday work ...

MOP/SOP LINK #2
Hi, I created a new item and its fulfillment method is Make to Order-Manual. I created a sales order and a mfg order for this item. In GP 8.0, I opened the MOP/SOP Link window, only saw the MO on the right side for this item and the SO didn't show up on the left side. Did I miss any step to link MOP and SOP? What should I do, so the SO will show up in the left side? Thanks. stien Make sure the end date is set correctly, it must be out farther than the requested ship date on the SO (the default is the current date) also make sure you have a SITE ID in, even if you only have one s...

Creating a table into another Access database
Is there a VBA command or SQL statement for me to run a Make Table Query to create a table in another Access database? I would like to distribute several access databases to different groups with tables populated with data specific to the group. I don't want to create the tables in the main database and transfer the table, because there's many tables and it would probalby exceed the 2gb limit of MS Access. SELECT * FROM INTO [;Database=C:\Folder\File.mdb].Table1 FROM MyTable -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "anon" ...

Graph OLE Linked objects problem
I got error message from access2003 when I tried to open a Graph OLE Linked objects, the message is: Strategic Budget builder can't find the file containing the linked OLE object you tried to update using OLE/DDE links command. When I click "yes", the other message pop up: "You may have misspelled the file name, or the file may have been deleted or renamed. If the file has been moved to a different location, use the OLE/DDE Links command to change the source. Or delete the object, and create a new linked object." Tried to re-installed OFFICE2003, but it still do...

Importing specific content into an Excel 2002 worksheet
I have some word documents that contain data I need throughout the document. As it stand, Ctrl+C and Ctrl+V are the easiest ways because the data is never in the same location on each document. Is there a way of capturing this data, and adding it to a selected field in Excel? The Word documents have a lot of 'noise' before, and after the data which is mostly contained in a table. This table also contains 'noise' that is not needed for the end result. Any suggestions are welcomed. Wayne. wrote: > I have some word documents that contain data I need throughout the &...

Copy from one publisher document to another
I am trying to help my father. He has created a bunch of individual publisher documents that he is now copying and pasting into one publisher document with multiple pages (he didn't know he could add pages originally). We are doing this by opening publisher twice and selecting all of an individual page and then copying. We then paste it into a blank page in the new multiple page publication. Our problem is that when we paste the items don't always land centered on the new page. Can anyone help us with this? My dad had Publisher 2003. Thanks, Barbara Select all, Cntrol A, then ...