IF/AND - reference another table to populate cells

Options
Natasha D
Natasha D ✭✭✭
edited 09/18/23 in Formulas and Functions

Hi everyone.

I have two sheets. The main sheet has information on a number of assets including columns Replacement Year and Replacement Type. The other sheet is an "indicative cost table" with Replacement Type as the primary column followed by each year 2026, 2027, 2028 etc across the top. The cost of replacement by type, by year has then been populated into the table.

I have created a column in the main sheet called Replacement Cost and I would like to automatically populate this by referencing the Indicative Cost Table. It would need to look at the Replacement Year & the Replacement Type in the main sheet and then pick the correct cost from the array. I am stumped on how to do this other than I expect it would be an index and match formula? Can anyone help please?

Answers

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hey @Natasha D ,

    Can you send a screenshot to help me understand the layout of the buid?

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Natasha D
    Natasha D ✭✭✭
    Options

    Hi @Itai

    Thanks for responding. Here is a snip of the main table with the columns I am referring to. The second snip is the indicative cost table. I would like the formula in the 'Replacement Cost' column. It would need to look at the Replacement Type and Replacement Year and then cross reference the data in the second snip. Once it finds the right cell, it will automatically populate the Replacement Cost. Essentially, I only want to update the cost in the indicative cost table instead of manually in the main sheet. Costs change often and it is time consuming and also prone to data errors. I do wonder if this is outside the scope of what Smartsheet can do. Thanks!


  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hey @Natasha D ,

    Not sure, worth asking @Andrée Starå

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Natasha D
    Natasha D ✭✭✭
    Options

    Thanks for having a look @Itai :)

    @Andrée Starå do you know if this is possible?

    Many thanks

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    You should be able to do this with an INDEX/MATCH if your reference sheet is set with Replacement Type next to the costs column wise (or you put a helper column in to duplicate it):

    =INDEX({Replacement Type-final year}, MATCH([Replacement Type]@row, {Replacement Type}), (Year@row - 2021))

    The 2021 part of the last column can be changed if there are more columns (e.g. including if 2023 was the first year then it would be 2020), and from your initial data you'd get an #INVALID COLUMN VALUE for the third row as is. As your Replacement date/year are calculated from start year + useful lifespan, this could be modified to not display dates in the past (using a MAX formula with current formula as the first value and the 1st available replacement year as the second) but only current/future ones.

    Another addition would be to put the above formula inside an IFERROR, but this won't prevent overdue replacements from generating the error mentioned previously, but can display "not known" or blank values if you don't have the data for an upcoming year.

    Hope this helps, but if you have any problems/questions, then just post. 🙂

  • Jen Castillo
    Options

    Hi! I saw this hasn't been answered so I thought I'd take a stab at it. :)

    How manual is your creation of the indicative cost table?

    Since we can't reference Column Names in our formulas, I changed made a few changes to the cost table's set up. Replacement Type/Replacement Years are now drop down columns. However, if you have hundreds of Assets/Years, this can be tedious. Although if you're already doing it manually in your cost table, then perhaps this would work? Maybe someone else has a better idea of how to structure it!

    Indicative Cost Table (Color-coded for clarity.)











    Main Table

    Do you reference all the other columns, or just your Replacement Type and Year? I plugged this formula into Replacement Cost while referencing the Cost/Type/Year columns from the cost table. If either Type or Year is missing, I added IFERROR to populate "Missing Data."

    =IFERROR(INDEX(COLLECT({Cost}, {Type}, [Replacement Type]@row, {Year}, [Replacement Year]@row), 1), "Missing Data")
    















    Hope this brings you a step closer to what you need!

  • Natasha D
    Natasha D ✭✭✭
    Options

    Thank you @Jen Castillo & @Nick Korna. I have been swapped with something else but will attempt your suggestions next week. I'm sure I'll have questions! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!