Using Data Form
 

Excel 2000

I have a list of customers which I add to each day and then have to redefine
the range name i.e. extend to include the last row or rows which I entered.

I have been using the built-in feature of Data Form and although I can run
this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?

Thanks, Robert

Using Data Form
 

Read this KB Robert

http://support.microsoft.com/default...62&Product=xlw
ShowDataForm Method Fails If Data Can't Be Found

John Walkenbach his much better Dataform
http://j-walk.com/ss/dataform/index.htm

See this
http://j-walk.com/ss/dataform/features.htm
to see the differents between the Excel dataform and John's one


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Robert" wrote in message ...
Excel 2000

I have a list of customers which I add to each day and then have to redefine
the range name i.e. extend to include the last row or rows which I entered.

I have been using the built-in feature of Data Form and although I can run
this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?

Thanks, Robert

Using Data Form
 

Ron,

Thanks for the leads, however I would like to write formula / code as this
requirement is on a number of PCs and I don't want to buy the code for each
machine.

The VBA part that I can't get right is naming a range, using macro record, I
select the first cell, then holding the shift key press End and Home keys.
I then name the range. Looking at the macro this refers to R1C1 etc. etc.

Any help most welcome. Robert

"Ron de Bruin" wrote in message
...
Read this KB Robert

http://support.microsoft.com/default...62&Product=xlw
ShowDataForm Method Fails If Data Can't Be Found

John Walkenbach his much better Dataform
http://j-walk.com/ss/dataform/index.htm

See this
http://j-walk.com/ss/dataform/features.htm
to see the differents between the Excel dataform and John's one


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Robert" wrote in message
...
Excel 2000

I have a list of customers which I add to each day and then have to
redefine
the range name i.e. extend to include the last row or rows which I
entered.

I have been using the built-in feature of Data Form and although I can
run
this by clicking a button assigned with the code
ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?

Thanks, Robert

Using Data Form
 

You could create a dynamic range named 'Database'. There are
instructions he

http://www.contextures.com/xlNames01.html

Robert wrote:
Excel 2000

I have a list of customers which I add to each day and then have to redefine
the range name i.e. extend to include the last row or rows which I entered.

I have been using the built-in feature of Data Form and although I can run
this by clicking a button assigned with the code ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Using Data Form
 

Debra,

Thank you ever so much, dynamic range is just the thing. I tried
=vlooup(A1,Customers!A:H,2) which seems to also work i.e. row number not
entered - just don't understand why this method worked.

Thanks, Robert


"Debra Dalgleish" wrote in message
...
You could create a dynamic range named 'Database'. There are
instructions he

http://www.contextures.com/xlNames01.html

Robert wrote:
Excel 2000

I have a list of customers which I add to each day and then have to
redefine
the range name i.e. extend to include the last row or rows which I
entered.

I have been using the built-in feature of Data Form and although I can
run
this by clicking a button assigned with the code
ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Using Data Form
 

If you name your range database (not with a dynamic formula definition, but
refering to the current data) and add your information using the dataform,
then the dataform will automatically extend the definition of database.

--
Regards,
Tom Ogilvy

"Robert" wrote in message
...
Debra,

Thank you ever so much, dynamic range is just the thing. I tried
=vlooup(A1,Customers!A:H,2) which seems to also work i.e. row number not
entered - just don't understand why this method worked.

Thanks, Robert


"Debra Dalgleish" wrote in message
...
You could create a dynamic range named 'Database'. There are
instructions he

http://www.contextures.com/xlNames01.html

Robert wrote:
Excel 2000

I have a list of customers which I add to each day and then have to
redefine
the range name i.e. extend to include the last row or rows which I
entered.

I have been using the built-in feature of Data Form and although I can
run
this by clicking a button assigned with the code
ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Using Data Form
 

See Tom's answer

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Robert" wrote in message ...
Ron,

Thanks for the leads, however I would like to write formula / code as this
requirement is on a number of PCs and I don't want to buy the code for each
machine.

The VBA part that I can't get right is naming a range, using macro record, I
select the first cell, then holding the shift key press End and Home keys.
I then name the range. Looking at the macro this refers to R1C1 etc. etc.

Any help most welcome. Robert

"Ron de Bruin" wrote in message
...
Read this KB Robert

http://support.microsoft.com/default...62&Product=xlw
ShowDataForm Method Fails If Data Can't Be Found

John Walkenbach his much better Dataform
http://j-walk.com/ss/dataform/index.htm

See this
http://j-walk.com/ss/dataform/features.htm
to see the differents between the Excel dataform and John's one


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Robert" wrote in message
...
Excel 2000

I have a list of customers which I add to each day and then have to
redefine
the range name i.e. extend to include the last row or rows which I
entered.

I have been using the built-in feature of Data Form and although I can
run
this by clicking a button assigned with the code
ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?

Thanks, Robert

Using Data Form
 

Tom,

Thanks for the tip, I never realised that the name "database" did this. I
have several lists, each on a separate sheet within a workbook and I have
found to my delight, that prefixing database with the sheet name allows for
multiple database range names.

Thanks, Robert


"Tom Ogilvy" wrote in message
...
If you name your range database (not with a dynamic formula definition,
but
refering to the current data) and add your information using the dataform,
then the dataform will automatically extend the definition of database.

--
Regards,
Tom Ogilvy

"Robert" wrote in message
...
Debra,

Thank you ever so much, dynamic range is just the thing. I tried
=vlooup(A1,Customers!A:H,2) which seems to also work i.e. row number not
entered - just don't understand why this method worked.

Thanks, Robert


"Debra Dalgleish" wrote in message
...
You could create a dynamic range named 'Database'. There are
instructions he

http://www.contextures.com/xlNames01.html

Robert wrote:
Excel 2000

I have a list of customers which I add to each day and then have to
redefine
the range name i.e. extend to include the last row or rows which I
entered.

I have been using the built-in feature of Data Form and although I
can
run
this by clicking a button assigned with the code
ActiveSheet.ShowDataForm, I
can't seem to figure out how to extend the range.

Do I need VBA code or a formula that expands?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html