Dispatching Materials details output as plain Text

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
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



 

Attachments

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
440
Would you consider a Userform for this? It may be simpler for both of us!
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
440
More questions!

1. I need to split the address, name and telephone into different columns otherwise they may not alwys be picked up. That ok?
2. Is S25 the product and available in colourbox, blister etc? If so, I'd prefer to set it out like:
xl2bb.xlam
ABCDEFGH
1ProductOptions
2S25Colorbox Blister CartonsWarranty CardLabels
3S26BottleMagnumJeroboamMethuselahSalmanazarBalthazarNabuchadnezzar
Material_Names
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,257
Office Version
2013
Platform
Windows
Remarks: Somehow XL2BB capture range is not working when i paste the range it shows like this " ￿￿" ,
Please read this:
 

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
Would you consider a Userform for this? It may be simpler for both of us!
Yes i am open for everything , i just want to make my manual process more faster and smarter with your precious help. But one thing i would like to add here there will be CHINESE characters in real data like in address , i am not aware if USERFORM get angry with Chinese language.

Regards
 

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
More questions!

1. I need to split the address, name and telephone into different columns otherwise they may not alwys be picked up. That ok?
2. Is S25 the product and available in colourbox, blister etc? If so, I'd prefer to set it out like:
xl2bb.xlam
ABCDEFGH
1ProductOptions
2S25Colorbox Blister CartonsWarranty CardLabels
3S26BottleMagnumJeroboamMethuselahSalmanazarBalthazarNabuchadnezzar
Material_Names
1. Yes i can make address , names , telephone in different columns , i just have to redo my data but thats i know how to do it , I will make it as per your say above.
2. this is trickly as every product will have different materials to use , Let me say S25 is a main commodity for example its a Charger , but to make it complete product we need to order Colorbox , Blister , Warranty Card etc . How you set it up is no problem for me as long as it will make it easy for me the whole process and i have dont have to look for right address and names everytime we need to make the dispatch text message.


Regards
 

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
Please read this:
thanks i will read it , it will help me alot if it can get it fixed.
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
440
I couldn't sleep so I knocked up this.

It needs de-bugging and some error trapping adding, but let me know if the concept is ok. We can develop it more once you get a feel of it!
 

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
@Paul Ked
Its really appreciate-able that you have done the effort. I just took a 3 hours sleep , first thing i check was your post , I have tried the spreadsheet shared by you. But i dont know what i am doing wrong , on Dispatch sheet when i enter the Supplier name nothing happens , similarly when i enter the material name also nothing happen.May be i am missing the key point on how it will work.
if you require i will capture a video on how i am doing it and upload it here , in case if you want to have a look. ?

Regards
 

Forum statistics

Threads
1,078,252
Messages
5,339,100
Members
399,278
Latest member
randomNumberGenerator2211

Some videos you may like

This Week's Hot Topics

Top