Forum Discussion

AlexVillemaire's avatar
AlexVillemaire
Copper Contributor
May 17, 2024
Solved

List Calculated Field with table reference

Hi, in a SharePoint List, I need a calculated field, and I wonder if it's feasible with just JSON. Users already input: location (drop-down list) & year. Based on those 2 values, I input manually a l...
Lists
microsoft lists
SharePoint Online
  • NikolinoDE's avatar
    NikolinoDE
    May 17, 2024

    AlexVillemaire 

    If the number of combinations for location and year is relatively small, you can maybe use nested IF statements in a calculated column. Here is an example of how you might set it up:

    1. Add a Calculated Column:
      • Go to your SharePoint list settings.
      • Click on "Create column".
      • Choose "Calculated (calculation based on other columns)" as the column type.
      • Enter your formula in the "Formula" box.

    Here is an example formula using nested IF statements:

    =IF(AND([Location]="Location1",[Year]=2023), 30, IF(AND([Location]="Location1",[Year]=2024), 35, IF(AND([Location]="Location2",[Year]=2023), 32, IF(AND([Location]="Location2",[Year]=2024), 36, "Unknown"))))

    This formula checks combinations of Location and Year and returns the corresponding labor rate. Expand the IF statements to cover all combinations. The text, steps and formulas was created with the help of AI

     

    Hope this will help you.

     

    My answers are voluntary and without guarantee!

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 17, 2024

AlexVillemaire 

If the number of combinations for location and year is relatively small, you can maybe use nested IF statements in a calculated column. Here is an example of how you might set it up:

  1. Add a Calculated Column:
    • Go to your SharePoint list settings.
    • Click on "Create column".
    • Choose "Calculated (calculation based on other columns)" as the column type.
    • Enter your formula in the "Formula" box.

Here is an example formula using nested IF statements:

=IF(AND([Location]="Location1",[Year]=2023), 30, IF(AND([Location]="Location1",[Year]=2024), 35, IF(AND([Location]="Location2",[Year]=2023), 32, IF(AND([Location]="Location2",[Year]=2024), 36, "Unknown"))))

This formula checks combinations of Location and Year and returns the corresponding labor rate. Expand the IF statements to cover all combinations. The text, steps and formulas was created with the help of AI

 

Hope this will help you.

 

My answers are voluntary and without guarantee!

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Resources