Variable in Cross Sheet Reference

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

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)