Data form

[Bob]

My named range is "ab_Month" in A5:A1500, among other
names in sheet(All in 5:1500). Problem, if I use the data
form for entry the form assumes 1500 entries in
database.That defeats the purpose of the form, because the
next entry is placed AFTER the range.
Any suggestions?
Thanks

 

[RSnyder]

Just a thought! Can you assign the input from your data
form to a temporary location and then based upon the users
response to add the data allow a macro to actually expand
the named range and copy the keyed data to the new line?
My normal process of adding data is by user input on a
comment box level using the response and assigning it to a
variable to process later. I let the VB module control
insertions and navagation in the sheets and workbook.

 

[Dave Peterson]

When you use Data|Form, excel looks in a certain range (either a range named
database or cells a1:b2) for data.

Maybe you could use a dynamic range that expands/contracts when you add/delete
rows.

Debra Dalgleish has some notes for dynamic ranges at:
http://www.contextures.com/xlNames01.html#Dynamic

So if I had always had data in column A, I could define a named range
(Insert|Name|Define, call it Database and give it this formula in the Refersto
box:

=OFFSET(sheet1!$A$5,0,0,COUNTA(sheet1!$A$5:$A$65536),12)

This would point at A5:L##.