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

Hi.

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)

Thanks.

Find more posts tagged with

Formulas

Accepted answers

Sameer Karkhanis

Use this,

="Calendar Year " + {That single cell selected as sheet1 range} - 2)

Sameer Karkhanis

@User251 Apologies that I missed the important part in my formula, I meant to say please use this so that you can reference the cell from other sheet as a range,

="Calendar Year " + VALUE({That single cell selected as sheet1 range} - 2)

All comments

Sameer Karkhanis

Use this,

="Calendar Year " + {That single cell selected as sheet1 range} - 2)

User251

Thank you @Sameer Karkhanis .Your suggestion solves my issue when I am referencing a cell within the same sheet. It looks like it is not possible to use a reference sheet with concatenation.

Sameer Karkhanis

@User251 Apologies that I missed the important part in my formula, I meant to say please use this so that you can reference the cell from other sheet as a range,

="Calendar Year " + VALUE({That single cell selected as sheet1 range} - 2)

User251

Thank you so much @Sameer Karkhanis !! it worked!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.