Variable scripting (text scripting) in Excel 2016

[wdgor]

Is there any way to put a paragraph in one cell in Excel that has intra paragraph dropdown choices (I am aware of the 255 character limit but thought I could concatenate to make the paragraph whole)? The"Choose an item" (in the example below) has dropdown choices from Word that I would like to use to build the sentences in Excel. Obviously, this is a capability in Word but it doesn't translate to anything I can find in Excel. The only thing I came up with is: TEXTJOIN(" ",,Scripts!A4,Scripts!A5,Scripts!A6,Scripts!A7) where the scripts are the various choices. That is VERY user unfriendly. I am not sure I can do what I want but would appreciate anyone's appraisal and potential solution. Thank you.

Cardiac Catheterization Laboratory<o></o>
Procedurerequests are accomplished <w:sdt title="Scheduling" id="-1709095277" SdtTag="Scheduling" DropDown="t" DocPart="EA2EFC7459734095965C316F86A8BCC9" ShowingPlcHdr="t"><w:listitem ListValue="Choose an item."></w:listitem> <w:listitem ListValue="contacting centralized scheduling." DataValue="contacting centralized scheduling."></w:listitem> <w:listitem ListValue="an administrative assistant answering calls from requestors." DataValue="an administrative assistant answering calls from requestors."></w:listitem> <w:listitem ListValue="someone manually filling out the schedule book." DataValue="someone manually filling out the schedule book."></w:listitem> Choose an item.</w:sdt> Once a procedure is scheduled, patients notpreviously registered are added in a pre-registration status to the <w:sdtpr></w:sdtpr><w:sdt title="EHR" id="2091500892" SdtTag="EHR" DropDown="t" DocPart="0FE33675A711438FAA5E5F64AFC8EAF6" ShowingPlcHdr="t"><w:listitem ListValue="Choose an item."></w:listitem> <w:listitem ListValue="Epic" DataValue="Epic"></w:listitem> <w:listitem ListValue="Cerner" DataValue="Cerner"></w:listitem> <w:listitem ListValue="Allscripts" DataValue="Allscripts"></w:listitem> <w:listitem ListValue="eClinicalWorks" DataValue="eClinicalWorks"></w:listitem> <w:listitem ListValue="Athena" DataValue="Athena"></w:listitem> Choose an item.</w:sdt> ElectronicHealth Record. Patients <w:sdtpr></w:sdtpr><w:sdt title="are/are not" id="823934947" DocPart="0FE33675A711438FAA5E5F64AFC8EAF6" ShowingPlcHdr="t" ComboBox="t"><w:listitem ListValue="Choose an item."></w:listitem> <w:listitem ListValue="are" DataValue="are"></w:listitem> <w:listitem ListValue="are not" DataValue="are not"></w:listitem> Choose an item.</w:sdt>scheduled forpre-admission testing<w:sdtpr></w:sdtpr><w:sdt title="Pre-Admit Testing" id="1211920410" SdtTag="Pre-Admit Testing" DocPart="0FE33675A711438FAA5E5F64AFC8EAF6" ShowingPlcHdr="t" ComboBox="t"><w:listitem ListValue="Choose an item."></w:listitem> <w:listitem ListValue=" that occurs in advance of the scheduled procedure date" DataValue=" that occurs in advance of the scheduled procedure date"></w:listitem> <w:listitem ListValue=" that occurs on the same day as scheduled procedure" DataValue=" that occurs on the same day as scheduled procedure"></w:listitem> <w:listitem ListValue="." DataValue="."></w:listitem> Choose an item.</w:sdt>. When the patient arrives on the day of theprocedure, the patient is registered in <w:sdtpr></w:sdtpr><w:sdt title="EHR" id="1706441967" SdtTag="EHR" DropDown="t" DocPart="EE85CD0560FA4A36BE90D10E8F9BDD38" ShowingPlcHdr="t"><w:listitem ListValue="Choose an item."></w:listitem> <w:listitem ListValue="Epic" DataValue="Epic"></w:listitem> <w:listitem ListValue="Cerner" DataValue="Cerner"></w:listitem> <w:listitem ListValue="Allscripts" DataValue="Allscripts"></w:listitem> <w:listitem ListValue="eClinicalWorks" DataValue="eClinicalWorks"></w:listitem> <w:listitem ListValue="Athena" DataValue="Athena"></w:listitem> Choose an item.</w:sdt>

 

[ZVI]

You can find a perfect explanation of dropdown lists on the Contextures web page - Excel Drop Down Lists
Read also Create Dependent Drop Down Lists
There are also sample files on that site.

 

[wdgor]

Thank you. I do understand drop down lists but I didn't explain the problem well enough. The example above is only a part of a paragraph and there will be multiple cells with similar needs. The problem is having multiple dropdowns (where the "Choose an item" variable lives). Once chosen, that dropdown result would become a part of the sentence. Then the user would go to the next "Choose an item". This all lives in one cell and the result would be a complete sentence with the dropdown variable inserted. Does that better explain the problem?

 

[ZVI]

This all lives in one cell and the result would be a complete sentence with the dropdown variable inserted.

Not sure I've understand it completely. The example like: choose the 1st word from dropdown of the cell A1, then choose 2nd word from dropdown of the cell A2 and join their values in cell A3 would be clearer for me. But seems it was mentioned in your post#1 as not user friendly. In any cases there can be only one dropdown list in the cell and dropdown can't be a part of the cell's value itself.

 

[wdgor]

I have not done a good job of explaining the need. The entire paragraph resides in cell A1. Within that paragraph there are
multiple places where the user would be asked to make a choice on the verbiage that would be used. That variable verbiage would come from the drop downs within cell A1. The TEXTJOIN function I mentioned would be used like: ="Procedure requests would come from "&TEXTJOIN(" ",,Scripts!A4,Scripts!A5,Scripts!A6,Scripts!A7)&"."

Scripts!A4, etc. Would come from a different worksheet in the workbook that would contain the drop down choices - Attending physician
Resident
Intern

When all put together, it would read: Procedure requests would come from Attending physician.

The problem with that approach is it is very unfriendly to the user. In fact, it is not usable.

There would be other sentences within the same A1 cell that would also have drop downs. The whole idea is to build a narrative based on drop down choices within the same cell A1.

I may not be able to do what I want without some complex VBA code. I am not adept at writing code and was hoping I could use something in native Excel. I sincerely hope this makes sense and I do appreciate your patience.

 

[ZVI]

Macro does not work in editing stage of the cell value when cursor is in the cell or into Fx formula editing field.
Seems the only way is in editing the 1st sentence of A1 sentences by one series of the dropdowns in other cells (in B1:D1 for example), the 2nd sentence - by B2:E2 etc. Or may be one series of dropdowns and the Apply button. Is this suitable for you?

 

[wdgor]

That appears to be the essence of the problem - it doesn't work like Word. How would you envision the Apply to work? Would it be able to concatenate all the various sentences into the same cell? These paragraphs have over 255 characters. I am trying to make this easy for the user. I also thought of making a hyperlink to the Word document. Let them do the drop down choices there and then copy that into cell A1 in Excel. Would that be a viable alternate approach?