Dispatching Materials details output as plain Text

[bobby786]

Dear All Excel Experts ,
My job requires me to send Text message to our vendors to dispatch the materials which contains the following information

Supplier Name : Longzhen
Model NO : S25
Material Name : Colorbox
Qty : 500 pcs
Address: Room 505 , Commercial Plaza , 104 Road , Unit 2 , Baoan District
Mr.John
Tel: 8765423
Notes: Please use lifter when the goods to this address

I have excel sheets :
Dispatch = Which will be used to enter the data
Address_list= which contains Supplier name and its address details with contact person name and tel no.
Material_Names = which contains the names of the material which will be needed to dispatch it.

Right now i have to manually copy paste everything into "Dispatch" and then i will copy all cells and paste them in notepad , then copy again and paste in our Text-message App which is similar to Whats-app.

I want to make this process automatic.

Here i will try to explain it , please forgive me for language and long post , as English is not my native language.

1. I want when i enter the Supplier name , Model NO , Qty after that "Material Name" column in "dispatch" sheet should give me option to select materials from "Material_Name" sheet , so i don't have to type it , in post as example i just used the simple name but in real life my material names contains words and numbers which is hard to remember always.So i like to have selection or drop-down or whatever is possible to easily select the available material names.

2.Address Column in "Dispatch" should get the corresponding address of that supplier automatically , so i dont' have to copy paste it , As soon as i enter the supplier name it get the address automatically.

3."Notes" Column in "Dispatch" sheet should get the notes from corresponding sheet , i have few different notes for different supplier , here i have no clue how to make it automatic please share your suggestion for it.

4. Last but not least , when i select all my data copy and paste it should give me Plain Text output like the Below:
Supplier Name : Longzhen
Model NO : S25
Material Name : Colorbox
Qty : 500 pcs
Address: Room 505 , Commercial Plaza , 104 Road , Unit 2 , Baoan District
Mr.John
Tel: 8765423
Notes: Please use lifter when the goods to this address

To download the spreadsheet click on this link Dispatch.Materials

Remarks: Somehow XL2BB capture range is not working when i paste the range it shows like this " ￿￿" , therefore i have upload my spreadsheet to my cloud web page in case if any helper want to take a look , i have attached the screenshot with the post as well.

I hope to have help from you guys Thanks in advance

Regards

 

[bobby786]

@Paul Ked

there is few question i cannot figure it out how to add those functions.
1. USERFORM can add the option for DATE ? it should select automatically system date but if user need to change it he can do it within that USERFORM , once its done final step it should store the data in Dispatch sheet with date , yes we will need to add coloumn for Date in Dispatch sheet. This way user will know when he dispatch those materials.

2. Yesterday i came across a situation where one model have several materials from the same supplier , So is it possible to add this options ?


Regards

 

[Paul Ked]

I've gone to Private message with this as we are going a bit off-topic

 

[bobby786]

@Paul Ked
ok no probs

 

[RoryA]

Please note that NO questions should be handled privately.

 

[Paul Ked]

Hi Rory

After seeing the Board Message at the top, I reported this myself earlier this morning. I stated that it was not my intention to break any forum rules and that nothing other than code was transacted.

If this is the moderators' response to my report then I think it could have been dealt with in a better way than a note!

 

[RoryA]

Posting here is the simplest way to make it clear to both parties, and any other readers, that this should not happen. I'm not really sure what you think a better way would be, or why you have a problem with this way?