Sharepoint 2016 Calculated Formula in list

SFC Kerstin J Lockery 96 Reputation points
2021-02-24T16:56:39.203+00:00

Good day, I am trying to build a list, and I am not sure if this is even possible - what I am trying. This list is a basic list that lists computers that I am working with, and automatically builds their hostname based on columns that are input. I can use a code as =LEFT([Facility],4)&LEFT([UIC],5)&"NB"&LEFT([Serial Number],3) - which gives me the first 4 letters in Facility, the first 5 letters in UIC, add NB, and then the last 3 numbers for serial number. This works fine alone, but based on manufacturer, I need to to return either the first three characters or the last three. What I am looking to do is this, have a [hostname] column: 1) based on the selection, it will either do one of the following codes (only difference is the SN portion) Dell: =LEFT([Facility],4)&LEFT([UIC],5)&"NB"&LEFT([Serial Number],3) HP: =LEFT([Facility],4)&LEFT([UIC],5)&"NB"&RIGHT([Serial Number],3) 2) based on the computer make/model selection, the "Hostname" column will automatically build me the hostname that I am wanting But when I try =IF statement, I can not seem to get it to work. I have tried to build them based on my full list of make/model, and I have also changed it to be simple. I created a column [Manuf] that works off this, I have gotten to this point of just having simple : =IF(Manufacturer="Dell Latitude D610","Dell",IF(Manufacturer="Dell Latitude E5520","Dell",IF(Manufacturer="Dell Latitude E5530","Dell",IF(Manufacturer="Dell Latitude E5570","Dell",IF(Manufacturer="Dell Latitude 5580","Dell",IF(Manufacturer="Dell Latitude E5580","Dell",IF(Manufacturer="Dell Latitude E6230","Dell",IF(Manufacturer="Dell Latitude E6400","Dell",IF(Manufacturer="Dell Latitude E6510","Dell",IF(Manufacturer="Dell Latitude E6520","Dell",IF(Manufacturer="Dell Latitude E6540","Dell",IF(Manufacturer="Dell Optiplex 780 (Legacy Turn-In)","Dell",IF(Manufacturer="Dell OptiPlex GX620 (Legacy Turn-In)","Dell",IF(Manufacturer="HP Elitebook 8560p","HP",IF(Manufacturer="HP Probook 650 G3","HP",IF(Manufacturer="HP Probook 650 G4","HP")))))))))))))))) And I get a return of in a column called [Manuf] of either Dell or HP - so trying to keep it simple, then under my [hostname] column I try: =IF[Manuf]=Dell, "=LEFT([Facility],4)&LEFT([UIC],5)&"NB"&LEFT([Serial Number],3)", =IF([Manuf]=HP "=LEFT([Facility],4)&LEFT([UIC],5)&"NB"&RIGHT([Serial Number],3)" but nothing works, I have tried about 50 different versions of code. Is this even possible? I just want it to return information based on type of computer, the only difference with the codes is in [serial number] - it will either return the first 3 characters or the last 3 based on if it is either Dell or HP.

SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,862 questions
{count} votes

1 additional answer

Sort by: Most helpful
  1. JoyZ 18,051 Reputation points
    2021-02-25T07:41:58.827+00:00

    Hi @SFC Kerstin J Lockery ,

    Since you have get the returned value in [Manuf] column with value Dell or HP, the next step we need to use this column in hostname column:

    IF([Manuf]="Dell",LEFT([Facility],4)&LEFT([UIC],5)&"NB"&LEFT([Serial Number],3),IF([Manuf]="HP",LEFT([Facility],4)&LEFT([UIC],5)&"NB"&RIGHT([Serial Number],3),""))  
    

    Compared to your formula, add double quotes for the exact values Dell and HP, remove the double quotes for the calculated function , and finally, if the condition is not met, add empty text.

    My simple test result for your reference:

    71965-image.png

    More information for your reference:

    https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a


    If an Answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments