Creating Text lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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?
 
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").Range("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
 
Command Button, would that be through a macro?

Matt Lunn said:
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").Range("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
Click to expand...
 
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
 
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
 
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,
 
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
 
Back
Top