Jordan has attended:
Excel Intermediate course
Excel Advanced course
Index function
I update a document daily with a large list of products which is saved down as a new dated version every day. I refer to this information regularly so it would be useful to have it in table format for easy INDEX MATCH functionality. The problem is I want to refer to the most updated version. Is there a way to refer to this table without changing the hyperlink to the most recent document every day?
Thanks,
RE: Index function
Hi Jordan,
Thanks for your question! This is definitely possible, but it will take a bit of set up to make it automatic and will make your function very long and complex.
You will need to set up a reference sheet with a list of dates going forward and the corresponding lookup arrays (this can be hidden once you're finished setting up)
Eg
Dates Array_names1
23/11/17 target23.11.17.xlsx!range_name_of_column
24/11/17 target24.11.17.xlsx!range_name_of_column
25/11/17 target25.11.17.xlsx!range_name_of_column
You can then nest INDEX MATCH into the array parts of your current INDEX MATCH to return the correct lookup array. You will need to use the INDIRECT function to return the right range, and use TODAY() as the lookup value so it becomes dynamic.
Eg
=INDEX(INDIRECT(INDEX(Dates,MATCH(TODAY(),Array_names1,0))),MATCH(lookup_value,INDIRECT(INDEX(Dates,MATCH(TODAY(),Array_names2,0),0))))
Let me know if you have any further questions.
Regards,
Sarah
Excel Trainer
