How do I concatenate text in a different sheet and a changing number

User251 ✭✭
edited 03/24/22 in Formulas and Functions


I need to concatenate text and year that are stored in a different sheet and get the result in a second sheet so that the year number changes depending on what the year is

For example :

if I have the base year 2022 in Sheet 1

The concatenated texts in Sheet 2 should look like this " Calendar Year 2020" and "Calendar Year 2021".

IOW, the year number next to the text "Calendar Year" should decrease by 2 and 1 depending on the base.

In Excel, if the base year is in Sheet1, cell A1 : the formula is written as follows:

=CONCATENATE("Calendar Year", Sheet1!A1-2) to get "Calendar Year 2020)



Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!