Variable in Cross Sheet Reference
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
Answers

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)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 64 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!