CRM with unique text input per drop down selection

[jnsmahn]

Hi,

I want to create a simple CRM system with drop down pages where the comment section has unique text input after each drop down selection. I have created an example file using a vlookup formula just to show you what I am looking for.

In the first tab, I want to be able to select "Seller 1" and then write a comment. When i then select "Seller 2", the comment on "Seller 1" should dissapear and when I go back at "Seller 1" again the comment should reapear.

Any suggestions on how to create it?

Thank you!

[Richard Buttrey]

Hi,

The approach I'd adopt is to create a database of comments for suppliers. This should be a simple two dimensional table containing columns for

Date, Seller number, Comment.

You could either feed this database manually, or preferably have a dedicated row of three cells in which you enter a new record, then have a simple macro that is attached to a button which when clicked adds the new record to the database and clears the data entry cells.

Then you could either have a Pivot Table to give you the analysis functionality you want, or another single line macro that would filter the database, either in lace with an Autofilter or an Advanced Filter which would extract all the comments for a particular supplier to a dedicated output sheet.

Personally I'd prefer the Pivot Table option.

[jnsmahn]

Thank you for your response! This definatly helped me getting in the right direction, as I have been searching for ways to do this with a drop down list. But I understand that a pivot is much more efficient.

A problem I have is that I have 30 dealers/suppliers with over 130 sellers. Would you suggest me to create a tab for each dealer/supplier? I have no expierence with macros, but I guess I can google it.

Could you please be a dear and create an example file for me? Or is it very time consuming?

[Richard Buttrey]

Thank you for your response! This definatly helped me getting in the right direction, as I have been searching for ways to do this with a drop down list. But I understand that a pivot is much more efficient.

A problem I have is that I have 30 dealers/suppliers with over 130 sellers. Would you suggest me to create a tab for each dealer/supplier?

No, no, no and most definitely no. You should have a single database sheet that contains a table that records information about your dealers and suppliers.
Are Sellers unique to Dealers, as your workbook implies. If so then create a table which contains columns for

Date, Dealer Number, Seller number, Comment.

and record all your comment records in this single table. When you've done this and added say a couple of dozen records for different supplier/dealer combinations then upload the workbook again and we'll help you put a pivot table together

[jnsmahn]

Okay, thank you.

I have now made a "database"-tab with all the dealer- and seller numbers. I will be using this daily to record different comments about different sellers. Also the sheet should be flexible when it comes to new sellers at different dealers. If I need to put alle the comments into this "database"-tab, it will be alot of comments. But as you mentioned earlier, it is possible to have a macro-button that removes the comments and adds it into an overview-file (pivot)?

Thank you so much for your help so far!

[Richard Buttrey]

Hi,

See attached.

I've added a Pivot Table and functionality to add new records. Enter new details in row 4 and press the button.

At some stage if sellers are unique to suppliers it would be useful to create a dependent drop down list so afterf you pick a dealer you only see those sellers that are applicable.