Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating Text lists
I have several rental properties. I get Guest Surveys, and list the name of a
house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#2
|
|||
|
|||
Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the
comments that you are currently interested in. HTH, Bernie MS Excel MVP "Jaytee" wrote in message ... I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#3
|
|||
|
|||
I actually did that on the appropriate page, but I'd also like to create a
"House Summary Page" in which the comment data and other scores are listed in a printable format for the homeowner. What do you think? "Bernie Deitrick" wrote: Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the comments that you are currently interested in. HTH, Bernie MS Excel MVP "Jaytee" wrote in message ... I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#4
|
|||
|
|||
Quick and dirty solution.....
Create a command button on the sheet you wish to display the comments on and enter this code (change Sheet1 in the code to whatever the sheet name holding all your data is) Dim iLoop As Integer Dim iReturnRow As Integer iReturnRow = 1 Range("B:B").ClearContents For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A:A")) If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3) iReturnRow = iReturnRow + 1 End If Next i HTH, Matt "Jaytee" wrote: I actually did that on the appropriate page, but I'd also like to create a "House Summary Page" in which the comment data and other scores are listed in a printable format for the homeowner. What do you think? "Bernie Deitrick" wrote: Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the comments that you are currently interested in. HTH, Bernie MS Excel MVP "Jaytee" wrote in message ... I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#5
|
|||
|
|||
Command Button, would that be through a macro?
"Matt Lunn" wrote: Quick and dirty solution..... Create a command button on the sheet you wish to display the comments on and enter this code (change Sheet1 in the code to whatever the sheet name holding all your data is) Dim iLoop As Integer Dim iReturnRow As Integer iReturnRow = 1 Range("B:B").ClearContents For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A:A")) If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3) iReturnRow = iReturnRow + 1 End If Next i HTH, Matt "Jaytee" wrote: I actually did that on the appropriate page, but I'd also like to create a "House Summary Page" in which the comment data and other scores are listed in a printable format for the homeowner. What do you think? "Bernie Deitrick" wrote: Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the comments that you are currently interested in. HTH, Bernie MS Excel MVP "Jaytee" wrote in message ... I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#6
|
|||
|
|||
Hi,
You could use the code in a macro. Just make sure it's always run while your display sheet is active. Let me know if you need any help with this. Thanks, Matt "Jaytee" wrote: Command Button, would that be through a macro? "Matt Lunn" wrote: Quick and dirty solution..... Create a command button on the sheet you wish to display the comments on and enter this code (change Sheet1 in the code to whatever the sheet name holding all your data is) Dim iLoop As Integer Dim iReturnRow As Integer iReturnRow = 1 Range("B:B").ClearContents For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A:A")) If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3) iReturnRow = iReturnRow + 1 End If Next i HTH, Matt "Jaytee" wrote: I actually did that on the appropriate page, but I'd also like to create a "House Summary Page" in which the comment data and other scores are listed in a printable format for the homeowner. What do you think? "Bernie Deitrick" wrote: Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the comments that you are currently interested in. HTH, Bernie MS Excel MVP "Jaytee" wrote in message ... I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#7
|
|||
|
|||
I'm just not sure what you meant by "Create a Command Button". Never done
that before! Excel Help gave me no clues either. I have never set up a Macro either, so any help in either direction would be good. Thanks "Matt Lunn" wrote: Hi, You could use the code in a macro. Just make sure it's always run while your display sheet is active. Let me know if you need any help with this. Thanks, Matt "Jaytee" wrote: Command Button, would that be through a macro? "Matt Lunn" wrote: Quick and dirty solution..... Create a command button on the sheet you wish to display the comments on and enter this code (change Sheet1 in the code to whatever the sheet name holding all your data is) Dim iLoop As Integer Dim iReturnRow As Integer iReturnRow = 1 Range("B:B").ClearContents For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A:A")) If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3) iReturnRow = iReturnRow + 1 End If Next i HTH, Matt "Jaytee" wrote: I actually did that on the appropriate page, but I'd also like to create a "House Summary Page" in which the comment data and other scores are listed in a printable format for the homeowner. What do you think? "Bernie Deitrick" wrote: Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the comments that you are currently interested in. HTH, Bernie MS Excel MVP "Jaytee" wrote in message ... I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#8
|
|||
|
|||
Hi,
If i have correctly understood your proble, you basically want to want to list all the comments against that house number. This solution assumed that youy want to get the result on the same sheet and not on a different one as desired by you. Try this: I assume tat your House numbers are in range A1:A7 and comments are in range B12:B7 Suppose you enter the house number for which you want the comments in cell A10, array enter (Ctrl+Shift+Enter) the following formula in cell B10 and copy down =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) This is not a perfect solution but just a workaround (your lists must start from row 1 Hope this helps Regards, "Jaytee" wrote: I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
#9
|
|||
|
|||
Have you tried VLOOKUP?
You could have some VLOOKUP formulas on an "owner" sheet pointing back to the data sheet to fill in the blanks. You would enter the name of the house in a cell and adjacent cells would show the info for that house only. For more on VLOOKUP see Debra Dalgleish's site http://www.contextures.on.ca/xlFunctions02.html Gord Dibben Excel MVP On Tue, 19 Jul 2005 10:55:08 -0700, Jaytee wrote: I have several rental properties. I get Guest Surveys, and list the name of a house in column A, and the comment on the house in Column C. On a seperate worksheet, I want to type in the name of a house in, lets say, A1, and generate a list of comments from the first sheet that correspond to that house name. Any help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Lists | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Quick way to sort lists by text color? | Excel Discussion (Misc queries) | |||
creating hyperlink inside oval with text in it | Excel Discussion (Misc queries) | |||
using formula to compare two text lists that are not alike and ma. | Excel Worksheet Functions |