Index function

Resolved · Low Priority · Version 2010

Thu 23 Nov 2017, 08:06 Delegate Jordan said...

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,

Thu 23 Nov 2017, 11:42 Trainer Sarah said...

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

Thu 30 Nov 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Make macros work in newer versions of Excel

If you have created macros in Excel 97 or 2000 that you want to be able to use in 2002/XP or 2003, you may need to alter the macro security settings in the newer version of Excel you are using.

To do this, go to Tools - Options - Security.

Select Macro Security and change the security setting to Low.

Tick the boxes next to 'Trust Add-ins' and 'Trust Visual Basic' and click OK.

After you have restarted Windows, you should then be able to use your macros created in earlier versions of Excel.

View all Excel hints and tips