Auto updating list...
 

Morning guys.

I have a problem that sounds simple at first, but I'm really having a tough
time getting it to actually do what I want.

Know, my Excel knowledge is limited at best because everything I know is
self taught, but here goes.

I have created a list of our customer's details in Excel. The usual details
are included such as:- Surname (column A), First Name (B), Company they work
for (C), Address 1 (D), Address 2 (E), Town (F), County (G), Postcode (H),
Phone number (H), e-mail (I), etc.

Now the problem. I want to be able to automatically track how many people
are from each company which is simple to do with a COUNTIF formula. Now this
is only effective if all of the company names are spelt correctly. Now
because I am only setting it up, it will be down to someone else to update
and maintain it so I need it to be idiot proof.

My thinking is to make the cells in the column for company names have
dropdown boxes so the company can be selected from a list. Again, easily
done with Data Validation.

Now, just to complicate things further, if the company name isn't in the
list when they click the dropdown box, I want them to be able to type it
directly in, so that when they go to the next cell down to choose the next
company, the one they previously entered will now appear as a selectable
option from the list.

I have googled it and found a few methods that work in a similar way, but
the majority seem to be where you add the name of the new company into a
dedicated cell, which is all well and good, but when the list gets to an
axcessive amount of lines, it's a pain in the arse to have to keep scrolling
up and down just to enter a name (I'm not a fan of the Freeze Panes option,
don't ask me why, I just don't like the idea, especially if I have to then
hand the document over to someone who isn't very "Excel Savvy")

I know it seems like a really arse about face way of doing things, but like
I said, I need it to be idiot proof. I would rather spend hours (or even
days) setting it up and have 100% accurate data, than spend a few minutes and
for the data to be wrong.

Hope I haven't confused you too much, but could really do with a little help
on this.

Cheers
Matt

Auto updating list...
 

There is a sample file at this location:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0012 - Update Validation List -- type a new value in a cell that contains
data validation, and it's automatically added to the source list, and the
list is sorted; a macro automates the list updates.


--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Morning guys.

I have a problem that sounds simple at first, but I'm really having a
tough
time getting it to actually do what I want.

Know, my Excel knowledge is limited at best because everything I know is
self taught, but here goes.

I have created a list of our customer's details in Excel. The usual
details
are included such as:- Surname (column A), First Name (B), Company they
work
for (C), Address 1 (D), Address 2 (E), Town (F), County (G), Postcode (H),
Phone number (H), e-mail (I), etc.

Now the problem. I want to be able to automatically track how many people
are from each company which is simple to do with a COUNTIF formula. Now
this
is only effective if all of the company names are spelt correctly. Now
because I am only setting it up, it will be down to someone else to update
and maintain it so I need it to be idiot proof.

My thinking is to make the cells in the column for company names have
dropdown boxes so the company can be selected from a list. Again, easily
done with Data Validation.

Now, just to complicate things further, if the company name isn't in the
list when they click the dropdown box, I want them to be able to type it
directly in, so that when they go to the next cell down to choose the next
company, the one they previously entered will now appear as a selectable
option from the list.

I have googled it and found a few methods that work in a similar way, but
the majority seem to be where you add the name of the new company into a
dedicated cell, which is all well and good, but when the list gets to an
axcessive amount of lines, it's a pain in the arse to have to keep
scrolling
up and down just to enter a name (I'm not a fan of the Freeze Panes
option,
don't ask me why, I just don't like the idea, especially if I have to then
hand the document over to someone who isn't very "Excel Savvy")

I know it seems like a really arse about face way of doing things, but
like
I said, I need it to be idiot proof. I would rather spend hours (or even
days) setting it up and have 100% accurate data, than spend a few minutes
and
for the data to be wrong.

Hope I haven't confused you too much, but could really do with a little
help
on this.

Cheers
Matt

Auto updating list...
 

Thanks Biff, that looks perfect. I'll try and decipher the coding and adapt
it to my document. Time to dabble with Visual Basic for the first time ever.
Wish me luck :)

Matt

"T. Valko" wrote:

There is a sample file at this location:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0012 - Update Validation List -- type a new value in a cell that contains
data validation, and it's automatically added to the source list, and the
list is sorted; a macro automates the list updates.


--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Morning guys.

I have a problem that sounds simple at first, but I'm really having a
tough
time getting it to actually do what I want.

Know, my Excel knowledge is limited at best because everything I know is
self taught, but here goes.

I have created a list of our customer's details in Excel. The usual
details
are included such as:- Surname (column A), First Name (B), Company they
work
for (C), Address 1 (D), Address 2 (E), Town (F), County (G), Postcode (H),
Phone number (H), e-mail (I), etc.

Now the problem. I want to be able to automatically track how many people
are from each company which is simple to do with a COUNTIF formula. Now
this
is only effective if all of the company names are spelt correctly. Now
because I am only setting it up, it will be down to someone else to update
and maintain it so I need it to be idiot proof.

My thinking is to make the cells in the column for company names have
dropdown boxes so the company can be selected from a list. Again, easily
done with Data Validation.

Now, just to complicate things further, if the company name isn't in the
list when they click the dropdown box, I want them to be able to type it
directly in, so that when they go to the next cell down to choose the next
company, the one they previously entered will now appear as a selectable
option from the list.

I have googled it and found a few methods that work in a similar way, but
the majority seem to be where you add the name of the new company into a
dedicated cell, which is all well and good, but when the list gets to an
axcessive amount of lines, it's a pain in the arse to have to keep
scrolling
up and down just to enter a name (I'm not a fan of the Freeze Panes
option,
don't ask me why, I just don't like the idea, especially if I have to then
hand the document over to someone who isn't very "Excel Savvy")

I know it seems like a really arse about face way of doing things, but
like
I said, I need it to be idiot proof. I would rather spend hours (or even
days) setting it up and have 100% accurate data, than spend a few minutes
and
for the data to be wrong.

Hope I haven't confused you too much, but could really do with a little
help
on this.

Cheers
Matt

Auto updating list...
 

Let's thank Debra for having such a great site.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Thanks Biff, that looks perfect. I'll try and decipher the coding and
adapt
it to my document. Time to dabble with Visual Basic for the first time
ever.
Wish me luck :)

Matt

"T. Valko" wrote:

There is a sample file at this location:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0012 - Update Validation List -- type a new value in a cell that
contains
data validation, and it's automatically added to the source list, and the
list is sorted; a macro automates the list updates.


--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Morning guys.

I have a problem that sounds simple at first, but I'm really having a
tough
time getting it to actually do what I want.

Know, my Excel knowledge is limited at best because everything I know
is
self taught, but here goes.

I have created a list of our customer's details in Excel. The usual
details
are included such as:- Surname (column A), First Name (B), Company they
work
for (C), Address 1 (D), Address 2 (E), Town (F), County (G), Postcode
(H),
Phone number (H), e-mail (I), etc.

Now the problem. I want to be able to automatically track how many
people
are from each company which is simple to do with a COUNTIF formula.
Now
this
is only effective if all of the company names are spelt correctly. Now
because I am only setting it up, it will be down to someone else to
update
and maintain it so I need it to be idiot proof.

My thinking is to make the cells in the column for company names have
dropdown boxes so the company can be selected from a list. Again,
easily
done with Data Validation.

Now, just to complicate things further, if the company name isn't in
the
list when they click the dropdown box, I want them to be able to type
it
directly in, so that when they go to the next cell down to choose the
next
company, the one they previously entered will now appear as a
selectable
option from the list.

I have googled it and found a few methods that work in a similar way,
but
the majority seem to be where you add the name of the new company into
a
dedicated cell, which is all well and good, but when the list gets to
an
axcessive amount of lines, it's a pain in the arse to have to keep
scrolling
up and down just to enter a name (I'm not a fan of the Freeze Panes
option,
don't ask me why, I just don't like the idea, especially if I have to
then
hand the document over to someone who isn't very "Excel Savvy")

I know it seems like a really arse about face way of doing things, but
like
I said, I need it to be idiot proof. I would rather spend hours (or
even
days) setting it up and have 100% accurate data, than spend a few
minutes
and
for the data to be wrong.

Hope I haven't confused you too much, but could really do with a little
help
on this.

Cheers
Matt