I have two spreadsheets stored on SharePoint. A "training" spreadsheet that is used to manage training courses; and a "master" spreadsheet that has an overview of who is running which training.
Each year we would have different subfolders and new spreadsheets to keep things organised.
I have this formula in the Training spreadsheet which works by manually entering it:
=VLOOKUP($B$4,'https://www.sharepoint.com/sites/Team/Shared Documents/Training courses/2023-24/[2023-24 Course Schedule.xlsx]Courses'!$B$1:$J$80,3,FALSE)
This formula is used in a few places with different col_index_num
What I am trying to do is create a template for the training spreadsheet so that I don't have to change the table_array manually every year.
I tried adding the table_array ('https://www.sharepoint.com/sites/Team/Shared Documents/Training courses/2023-24/[2023-24 Course Schedule.xlsx]Courses'!$B$1:$J$80) into a cell and reference it so we would just change the URL in one cell. But I can't get it to work. I couldn't even get INDIRECT to work (which wouldn't be of use anyway since it doesn't work if the other spreadsheet is closed).
Any suggestions would be greatly appreciated!!
Thank you
