Hi Karen, sorry it took so long to answer I've been away a few days. My list
is working pretty good now, thinks to a lot of help from others.
I have a workbook that is one of many representing years, each has many
sheets representing many vehicles, each with various repair and damage
reports. each report needs its own, unique number.
My workbook is setup with sheet 1 as "Lists", sheet2 . . . set up as the
inpu sheet auto number "xxx". the Lists contains two rows, one with
descrepencies the other with initials. Initials is a static list made with
data validation. Descrepency list has a nice auto-updating drop down that ads
every new entry to the next empty row. the date column is auto entry.
Here is the code:
sheet"Lists" code (auto updating list): click the sheet tab and view code
and paste
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
+++++++++++++++++++++++++++++++++++++++++++++
Each input list for the vehicles has this code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 1 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
Else
End If
End Sub
+++++++++++++++++++++++++++++++++++++++
Immediately below the aboce is this code to automatically enter the date:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Automatically inserts today's date in cell in column C when selected
'if the cell was empty. Does not overwrite occupied cell.
If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
End If
Else
End If
End Sub
+++++++++++++++++++++++++++++++
that's it just be sure the lists are in the correct columns, if you want a
sample write to:
and I will sent it to you. take care
"KarenF" wrote:
Hi Larry,
I'm having a similar problem.
My dynamic list works in that it expands as I add more to it, providing I
enter it inthe next available cell in the list - as you would expect.
However, I wish to use this list as a data source for validation on another
sheet in my workbook. When I enter the list name (i.e. = location), I get
the same message that you did, about the range name not being found. It does
not appear on the range list, nor when I press F3, but is in the Insert,
Name, Define list. I wondered how you were getting on with this, and if you
could offer any help.
Thanks Larry.
Take care,
Karen.
"Larry" wrote:
Roger, I appreciate the help. I have done as you suggested.
I set up a workbook called 1986,
I set up various worksheets for each auto in inventory by vehicle number.
I also made an additional sheet called list.
On this sheet I have two lists, cloumn A is "descrepency" and column B is
Initial. Column B is o.k because it is a static list at this point and works
as long as I name the range and use data validation for each column of each
sheet that I have set up.
The problem for me is the Descrepency list which I would like to be dynamic
and self updating as you indicated.
I created the list named it Descrepency.
When I go to inset/name/define and put the folllowing in "refers to"
=OFFSET($A$1,0,0,COUNTA($A:$A))
The name descrepency disappears from the list of named ranges.
Each worksheet with validation to the descrepency list shows a drop down
with all the entries listed, and a selection can be made.
If an entry is made that is not on the list it will post but the list is not
updating nor expanding as expected.
whaddayya think? thanks man. larry
"Roger Govier" wrote:
Hi Larry
To set up a dynamic range for your list using the Offset() function, you
need to do the following (assuming your list is in column A, starting in
row 1 and could be of any length, as you add new data.
InsertNameDefine
In the Name pane at the top of the dialogue enter Initials
In the Refers to pane at the bottom enter
=OFFSET($A$1,0,0,COUNTA($A:$A))
Next Click Add
If you have done it correctly, clicking on the icon at the end of the
Refers to pane, should highlight the range of cells that the Name
Initials refers to.
How Offset works
$A$1 is the reference point
,0,0 is saying use an offset of 0 Rows and 0 Columns from this cell as
the start position - which is still A1
,COUNTA(A:A) counts the number of non-blank cells in column A, and uses
this as the depth of the range.
I have omitted the final parameter in the formula, which therefore cause
it to default to 1, as your list is only one column wide, but you could
have a final , and number (or , and variable) which would define a range
which had a number of columns width.
If you had entries in cells A1 down to A12, CountA() would return 12,
and the range would get defined as A1:A12.
As soon as you enter a new value in your list, CountA() would increase
to 13, and the range would grow to A1:A13
I hope this aids your understanding and allows you to create the ranges
you want.
--
Regards
Roger Govier
"Larry" wrote in message
...
I'm having a terrible time getting something to work. I want two lists
that
will automatically update when a new item is added that is not in the
list.
I have a work book, representing a year. In the workbook are several
sheets,
representing autos. In the workbook I have created one sheet that has
two
lists in it, one for initials (Initial) and one for recording
damages(Descrepency). I have named the lists and set the target clumns
in the
auto sheets to validate to the lists.
I read Debra Dalgleish's contextures on dynamic ranges but when I
follow the
guidance there and insert the if= in defined name, the list names
disappear
and the worksheets return an error that no list name can be found. I
have
done everything my limited knowledge allows but have gotten nowhere
with
this. I got some code from a fine gentleman for lists but it also has
failed
to allow me to auto update, always returning some error.
I love this bulletin, but so far none of the recommended solutions out
there
are working, I feel I am missing some simple step here. Help??