SOLVED

Dependent columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1536946%22%20slang%3D%22en-US%22%3EDependent%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536946%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%201%20SharePoint%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMultiple%20columns.%20I%20want%20one%20column%20dependent%20on%20the%20column%20next%20to%20it.%20Quite%20simply%2C%20if%20the%20column%20to%20the%20left%20is%20empty%2C%20have%20the%20value%20%22pending%22.%20If%20the%20column%20has%20something%20in%20it%2C%20have%20the%20value%20%22complete%22.%3C%2FP%3E%3CP%3EI%20don't%20think%20a%20lookup%20column%20type%20does%20this.%20Is%20there%20a%20way%20to%20accomplish%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1536946%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1537530%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1537530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735351%22%20target%3D%22_blank%22%3E%40Yung_man%3C%2FA%3E%26nbsp%3B%2C%20You%20can%20create%20a%20calculated%20column%20for%20this.%20Use%20below%20formula%20for%20your%20calculated%20column%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%3DIF(ISBLANK(%5BColumn%201%5D)%2C%22Pending%22%2C%22Complete%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReplace%20%22Column%201%22%20with%20the%20display%20name%20of%20your%20column.%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Calculated%20Field%20Formulas%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fdeveloper%2Fsharepoint-2010%2Fbb862071(v%3Doffice.14)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECalculated%20Field%20Formulas%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3E%3CSTRONG%3EAccept%20as%20solution%3C%2FSTRONG%3E%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20solve%20your%20issue.%20This%20will%20help%20others%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20content%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3E%3CSTRONG%3ELike%3C%2FSTRONG%3E%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538161%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20reply%2C%20this%20looks%20promising.%20However%2C%20for%20some%20reason%20I%20can't%20find%20my%20column%20name%20in%20the%20%22Insert%20Column%22%20beside%20the%20formula%20text%20box.%20They%20show%20all%20the%20ones%20that%20are%20of%20type%20%22Choice%22%20but%20the%20columns%20I%20want%20to%20refer%20to%20do%20not%20show%20-%20which%20are%20of%20type%20%22Multiple%20lines%20of%20text%22.%20I%20tried%20forcing%20the%20column%20name%20by%20typing%20it%20in%20but%20didn't%20work.%20Any%20ideas%20if%20the%20columns%20I%20want%20to%20depend%20on%20are%20not%20shown%20in%20the%20list%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538296%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735351%22%20target%3D%22_blank%22%3E%40Yung_man%3C%2FA%3E%2C%20Unfortunately%26nbsp%3B%3CSPAN%3EMulti%20line%26nbsp%3Btext%20fields%20cannot%20be%20used%20in%20formulas%20in%20SharePoint.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHowever%2C%20I%20will%20suggest%20you%20to%20consider%20one%20of%20the%20solutions%20from%20below%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%3C%2FSTRONG%3E%20%3CSTRONG%3E1%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3EYou%20can%20trick%20calculated%20columns%20into%20referring%20to%20a%20Multiple%20lines%20of%20text%20column%20by%20first%20creating%20a%20single%20lines%20of%20text%20column%2C%20using%20it%20in%20formula%20and%20then%20change%20its%20data%20type%20to%20multiple%20lines%20of%20text%20(or%20delete%20this%20column%20and%20create%20new%20column%20with%20exact%20same%20name).%20Refer%20below%20links%3A%3C%2FP%3E%3COL%3E%3CLI%3E%3CA%20title%3D%22How%20to%20use%20'Multiline%20Text%20Field'%20in%20calculated%20field%3F%22%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2Foffice%2Fen-US%2Fd69019d1-7296-4e0c-802d-b9051fdf61d9%2Fhow-to-use-multiline-text-field-in-calculated-field%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20use%20'Multiline%20Text%20Field'%20in%20calculated%20field%3F%3C%2FA%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CA%20title%3D%22How%20to%20trick%20a%20Calculated%20column%20into%20referring%20to%20a%20Multiple%20Lines%20of%20Text%20column%22%20href%3D%22https%3A%2F%2Fwowsomuch.com%2Fsharepoint%2Fcalculated-column-refer-to-multiple-lines-of-text-column%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EHow%20to%20trick%20a%20Calculated%20column%20into%20referring%20to%20a%20Multiple%20Lines%20of%20Text%20column%3C%2FA%3E%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThis%20is%20a%20kind%20of%20hack%20so%20you%20should%20use%20it%20with%20caution.%20Also%2C%20if%20you%20try%20to%20modify%20the%20formula%20or%20update%20the%20calculated%20column%20after%20this%20setup%20probably%20it%20will%20through%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%202%3A%3C%2FSTRONG%3E%20Use%20SharePoint%20designer%20workflow%2FMicrosoft%20flow%20(Power%20Automate)%20on%20list%20item%20creation%20and%20item%20update%20to%20check%20if%20the%20multiple%20lines%20of%20field%20is%20blank%20or%20not%20and%20set%20the%20other%20column%20value%20accordingly%20using%20condition%20actions.%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20solve%20your%20issue.%20This%20will%20help%20others%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20content%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3E%3CSTRONG%3ELike%3C%2FSTRONG%3E%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have 1 SharePoint list.

 

Multiple columns. I want one column dependent on the column next to it. Quite simply, if the column to the left is empty, have the value "pending". If the column has something in it, have the value "complete".

I don't think a lookup column type does this. Is there a way to accomplish this?

3 Replies

@Yung_man , You can create a calculated column for this. Use below formula for your calculated column:

 

=IF(ISBLANK([Column 1]),"Pending","Complete")

 

Replace "Column 1" with the display name of your column.

Calculated Field Formulas  


Please click Accept as solution if my post helped you solve your issue. This will help others find the correct solution easily. It also closes the item. If the content was useful in other ways, please consider giving it Like.

@ganeshsanap 

 

Thanks for the quick reply, this looks promising. However, for some reason I can't find my column name in the "Insert Column" beside the formula text box. They show all the ones that are of type "Choice" but the columns I want to refer to do not show - which are of type "Multiple lines of text". I tried forcing the column name by typing it in but didn't work. Any ideas if the columns I want to depend on are not shown in the list?

Best Response confirmed by Yung_man (Occasional Contributor)
Solution

@Yung_man, Unfortunately Multi line text fields cannot be used in formulas in SharePoint.

However, I will suggest you to consider one of the solutions from below:

Solution 1:

You can trick calculated columns into referring to a Multiple lines of text column by first creating a single lines of text column, using it in formula and then change its data type to multiple lines of text (or delete this column and create new column with exact same name). Refer below links:

  1. How to use 'Multiline Text Field' in calculated field? 
  2. How to trick a Calculated column into referring to a Multiple Lines of Text column 

This is a kind of hack so you should use it with caution. Also, if you try to modify the formula or update the calculated column after this setup probably it will through an error.

 

Solution 2: Use SharePoint designer workflow/Microsoft flow (Power Automate) on list item creation and item update to check if the multiple lines of field is blank or not and set the other column value accordingly using condition actions.


Please click Mark as Best Response if my post helped you solve your issue. This will help others find the correct solution easily. It also closes the item. If the content was useful in other ways, please consider giving it Like.