Hi, I need some advice.

I have a list of 100 names in one column and to the right of that I
have several additional columns of data linked to those names.

I produce a one page report every week that contains selected names
from the list along with the other data linked to those names to the
right. I usually put the names inside a border box to dress up the
appearance. The size of the list varies from about 5 names to 20 at
most.

Is there a way I can automate this process say with a bunch of drop
down or combo boxes linked to the list in another spreasheet? I'm
getting tired of copying and pasting the names into the report every
week.

Thanks for any ideas you can offer.

What determines which names are selected?

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Hi, I need some advice.

I have a list of 100 names in one column and to the right of that I
have several additional columns of data linked to those names.

I produce a one page report every week that contains selected names
from the list along with the other data linked to those names to the
right. I usually put the names inside a border box to dress up the
appearance. The size of the list varies from about 5 names to 20 at
most.

Is there a way I can automate this process say with a bunch of drop
down or combo boxes linked to the list in another spreasheet? I'm
getting tired of copying and pasting the names into the report every
week.

Thanks for any ideas you can offer.

The list is a list of company names and the ones included each week are
determined on their suitability for that week's project.

OK, that explains it all.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
The list is a list of company names and the ones included each week are
determined on their suitability for that week's project.

Bob,

Here is a little macro for you. It is driven from a list of items on another
worksheet, and puts it on a third.

Sub Test()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim iLastRow As Long
Dim iLastCol As Long
Dim iRow As Long
Dim iPos As Long
Dim i As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")

sh3.Cells.Clear
With sh2
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
On Error Resume Next
iPos = Application.Match(.Cells(i, "A").Value, sh1.Range("A:A"),
0)
On Error GoTo 0
If iPos 0 Then
iRow = iRow + 1
sh1.Rows(iPos).Copy sh3.Cells(iRow, "A")
End If
Next i
iLastCol = sh3.Cells(1, sh3.Columns.Count).End(xlToLeft).Column
sh3.Columns("A:B").Insert
sh3.Rows("1:2").Insert
sh3.Range("A1").Offset(1, 1).Resize(iRow + 2, iLastCol +
2).BorderAround _
ColorIndex:=3, Weight:=xlThick
ActiveWindow.DisplayGridlines = False
sh3.Activate
End With

End Sub

Sub bob()
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim RangeArray
Dim m As Long

Set Range1 = Worksheets(1).Range("A1:D5")
Set Range2 = Worksheets(1).Range("A11:D15")
Set Range3 = Worksheets(1).Range("A21:D25")
RangeArray = Array(Range1, Range2, Range3)
m = 1
MsgBox RangeArray(m).Address

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hi, I need some advice.

I have a list of 100 names in one column and to the right of that I
have several additional columns of data linked to those names.

I produce a one page report every week that contains selected names
from the list along with the other data linked to those names to the
right. I usually put the names inside a border box to dress up the
appearance. The size of the list varies from about 5 names to 20 at
most.

Is there a way I can automate this process say with a bunch of drop
down or combo boxes linked to the list in another spreasheet? I'm
getting tired of copying and pasting the names into the report every
week.

Thanks for any ideas you can offer.

Bob,

Thanks for the macro. I got it to work a few times. I wish my VBA
skills were better so I understood what you gave me.

The second macro is a mystery to me. What is that one supposed to do?

Thanks again!

It was an answer to someone else's question. Bob copied it into this answer
by mistake. You can disregard it.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Bob,

Thanks for the macro. I got it to work a few times. I wish my VBA
skills were better so I understood what you gave me.

The second macro is a mystery to me. What is that one supposed to do?

Thanks again!

Is there a way to modify the code so:

1. It only looks at one sheet to print the list? (now it seems to
require the same list on two sheets - 1 & 2)

2. So it closes a gap if there is a gap in the list? (now it repeats
the entry before a gap)

Thanks!

Bob,

The problem with request #1 is that at present the macro copies the complete
row. If we put the selection list somewhere on sheet1, it is likely that
would get copied also. Can we restrict the copy to a set number of columns
rather than the whole row?

I am not really clear as to what is meant by #2? Are you referring to the
blanks that I put in there in rows 1 & 2 and columns A & B (which I hoped
would make the report clearer :-))?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Is there a way to modify the code so:

1. It only looks at one sheet to print the list? (now it seems to
require the same list on two sheets - 1 & 2)

2. So it closes a gap if there is a gap in the list? (now it repeats
the entry before a gap)

Thanks!