Variable in Cross Sheet Reference

JonathanWaters · 2023-02-10T04:02:58+00:00

There was an error rendering this rich post.

JonathanWaters

Hi All

I have created 5 different cross sheet references, that refer to a range in 5 different sheets, these are named:

{Year1}

{Year2}

{Year3}

{Year4}

{Year5}

On a separate sheet I have a formula in one column [Invoice Year] that returns 1,2,3,4,5 based on what year of the project the invoice falls in

The formula is =VLOOKUP([PO Number]@row, {"Year"+[Invoice Year]@row}, 4, false)

The formula doesn't work, and I'm not sure if it's because I have done something wrong, or variables are not allowed in cross sheet references. If I dont use the variable e.g

=VLOOKUP([PO Number]@row, {Year1}, 4, false)

then it works perfectly

Would appreciate any assistance you can give

Cheers

Jonathan

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You cannot use variables in cross sheet references. You would need to use a nested IF statement.

=VLOOKUP([PO Number]@row, IF([Invoice Year]@row = 1, {Year1}, IF([Invoice Year]@row = 2, {Year2}, ...........))))), 4, false)

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.