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
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!