MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- Windows
I have an Excel workbook and an MS Word document that contain email correspondence content.
The Word document contains information that is present in 3 basic lines as follows:
The first line contains an expression that includes a link code, a sent date, and a received time. This will be apparent as seen in an Xl2bb Mini Sheet and an image taken from the Word document.
The second line contains the Subject line that is relevant to a specific email message. Again, this will be apparent as seen in an Xl2bb Mini Sheet and an image taken from the Word document.
The third line is simply the message text that comes from the body area of specific email messages. This will be apparent as seen in an image of example typical locations in the Word document.
What I would like to accomplish in the Word document is to have in the first line location a link or formula that looks at the Word numbering to the left of the first line and get the relevant data from the Excel workbook per that numbering.
In addition, I would also like to get the Subject line inserted in the second line location that is also relevant to the numbering that matches the Doc # as seen in the Xl2bb Mini Sheet.
I suspect this is somewhat confusing but hopefully when the Xl2bb Mini Sheet and the Word document images are seen then it becomes clear what I am wanting.
I should note here that I suspect this can’t be done, but I wouldn’t know unless I ask. Using tables in the Word document is not what I want for a solution; thus why I think this is not possible but then there are many things about Excel and Word I am unfamiliar with regarding this endeavor.
Any help will be much appreciated.
The Word document contains information that is present in 3 basic lines as follows:
The first line contains an expression that includes a link code, a sent date, and a received time. This will be apparent as seen in an Xl2bb Mini Sheet and an image taken from the Word document.
The second line contains the Subject line that is relevant to a specific email message. Again, this will be apparent as seen in an Xl2bb Mini Sheet and an image taken from the Word document.
The third line is simply the message text that comes from the body area of specific email messages. This will be apparent as seen in an image of example typical locations in the Word document.
What I would like to accomplish in the Word document is to have in the first line location a link or formula that looks at the Word numbering to the left of the first line and get the relevant data from the Excel workbook per that numbering.
In addition, I would also like to get the Subject line inserted in the second line location that is also relevant to the numbering that matches the Doc # as seen in the Xl2bb Mini Sheet.
I suspect this is somewhat confusing but hopefully when the Xl2bb Mini Sheet and the Word document images are seen then it becomes clear what I am wanting.
I should note here that I suspect this can’t be done, but I wouldn’t know unless I ask. Using tables in the Word document is not what I want for a solution; thus why I think this is not possible but then there are many things about Excel and Word I am unfamiliar with regarding this endeavor.
Any help will be much appreciated.
Cell Formulas | ||
---|---|---|
Range | Formula | |
G60:G72 | G60 | =IF(OR(ISBLANK(J60),ISBLANK(K60)),"",VALUE(LEFT(J60,2)&":"&RIGHT(K60,2)&":00")) |
H60:H72 | H60 | =TEXT(E60,"MMM") |
I60:I72 | I60 | =TEXT(E60,"DD") |
L60:L72 | L60 | =IF(ISBLANK(A60),"",(A60)*1) |
M60:M72 | M60 | =IF(OR(ISBLANK(E60),ISBLANK(G60)),"",B60&"--Message received from dodge "&TEXT(E60+G60,"ddd dd/mmm/yyyy--\@--hh:mm")) |
B60 | B60 | =TabLegend!$I$1&"Fd"&TEXT(E60,"MM-")&SUM(O58+1) |
B61:B72 | B61 | =TabLegend!$I$1&"Fd"&TEXT(E61,"MM-")&SUM(O60+1) |
Conversations with Roger Caymin-2021.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
72 | Doc # | Link Code | Sender | Subject | Sent Date | @ | Rec'd Time | Month | Day | Doc # | Text To Copy To Relevant Word Document | ||||
73 | 1180 > | 21Td11-69 | Codger | Renovation Explosion | Sun 21/Nov/2021 | @ | 18:28 | Nov | 21 | 18 | 28 | 1180 > | 21Td11-69--Message sent to dodge Sun 21/Nov/2021--@--18:28 | ||
74 | 1181 > | 21Td11-70 | Codger | Joke for Tyly | Sun 21/Nov/2021 | @ | 21:13 | Nov | 21 | 21 | 13 | 1181 > | 21Td11-70--Message sent to dodge Sun 21/Nov/2021--@--21:13 | ||
75 | 1184 > | 21Td11-71 | Codger | No more than an ******* | Mon 22/Nov/2021 | @ | 13:05 | Nov | 22 | 13 | 05 | 1184 > | 21Td11-71--Message sent to dodge Mon 22/Nov/2021--@--13:05 | ||
76 | 1185 > | 21Td11-72 | Codger | comes with old age, I guess | Mon 22/Nov/2021 | @ | 14:12 | Nov | 22 | 14 | 12 | 1185 > | 21Td11-72--Message sent to dodge Mon 22/Nov/2021--@--14:12 | ||
77 | 1186 > | 21Td11-73 | Codger | New data | Mon 22/Nov/2021 | @ | 22:18 | Nov | 22 | 22 | 18 | 1186 > | 21Td11-73--Message sent to dodge Mon 22/Nov/2021--@--22:18 | ||
78 | 1189 > | 21Td11-74 | Codger | RE: Renovation Explosion | Tue 23/Nov/2021 | @ | 10:21 | Nov | 23 | 10 | 21 | 1189 > | 21Td11-74--Message sent to dodge Tue 23/Nov/2021--@--10:21 | ||
79 | 1190 > | 21Td11-75 | Codger | Benni = lost cause... | Tue 23/Nov/2021 | @ | 10:37 | Nov | 23 | 10 | 37 | 1190 > | 21Td11-75--Message sent to dodge Tue 23/Nov/2021--@--10:37 | ||
80 | 1197 > | 21Td11-76 | Codger | RE: xe^x | Tue 23/Nov/2021 | @ | 14:05 | Nov | 23 | 14 | 05 | 1197 > | 21Td11-76--Message sent to dodge Tue 23/Nov/2021--@--14:05 | ||
81 | 1198 > | 21Td11-77 | Codger | Mythical Floating Box | Tue 23/Nov/2021 | @ | 14:38 | Nov | 23 | 14 | 38 | 1198 > | 21Td11-77--Message sent to dodge Tue 23/Nov/2021--@--14:38 | ||
82 | 1200 > | 21Td11-78 | Codger | Better know for certain | Tue 23/Nov/2021 | @ | 19:10 | Nov | 23 | 19 | 10 | 1200 > | 21Td11-78--Message sent to dodge Tue 23/Nov/2021--@--19:10 | ||
83 | 1201 > | 21Td11-79 | Codger | First things first | Wed 24/Nov/2021 | @ | 10:05 | Nov | 24 | 10 | 05 | 1201 > | 21Td11-79--Message sent to dodge Wed 24/Nov/2021--@--10:05 | ||
84 | 1203 > | 21Td11-80 | Codger | Too much drama... | Wed 24/Nov/2021 | @ | 12:58 | Nov | 24 | 12 | 58 | 1203 > | 21Td11-80--Message sent to dodge Wed 24/Nov/2021--@--12:58 | ||
85 | 1206 > | 21Td11-81 | Codger | Turkey Day | Thu 25/Nov/2021 | @ | 08:35 | Nov | 25 | 08 | 35 | 1206 > | 21Td11-81--Message sent to dodge Thu 25/Nov/2021--@--08:35 | ||
86 | 21Td01-82 | Codger | @ | Jan | 00 | ||||||||||
87 | 21Td01-83 | Codger | @ | Jan | 00 | ||||||||||
21TdNov |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G73:G87 | G73 | =IF(OR(ISBLANK(J73),ISBLANK(K73)),"",VALUE(LEFT(J73,2)&":"&RIGHT(K73,2)&":00")) |
H73:H87 | H73 | =TEXT(E73,"MMM") |
I73:I87 | I73 | =TEXT(E73,"DD") |
L73:L87 | L73 | =IF(ISBLANK(A73),"",(A73)*1) |
M73:M87 | M73 | =IF(OR(ISBLANK(E73),ISBLANK(G73)),"",B73&"--Message sent to dodge "&TEXT(E73+G73,"ddd dd/mmm/yyyy--\@--hh:mm")) |
B73 | B73 | =TabLegend!$I$1&"Td"&TEXT(E73,"MM-")&SUM(O71+1) |
B74:B87 | B74 | =TabLegend!$I$1&"Td"&TEXT(E74,"MM-")&SUM(O73+1) |