Dynamic Index & Match - can it be done

[Paul at GTS]

Hi,
We have two workbooks, one logs delivery statistics, the other logs sales statistics.

At present we have the sales workbook looking at the delivery workbook to pull out milages etc. The workbooks have a tab per month.

The data is pulled across using Index & Match with named ranges.

My issue is that the named range needs to be changed with the start of each new month as the sheet tab changes Jan-19 to Feb-19 etc.

Is there a way of inserting the required tab name into the Named Range - ideally I'd like to input Jan-19 into cell A1 and the month name is picked up from there and positioned in the named range. Below are the formula and ranges from current month.

This is the formula that pulls data over:
=INDEX(MilageMatrix,MATCH($C4,MilageLookup,0),4)

Milage Matrix Range :
='[Time sheets 08 v2.0.xlsx]Jun 19'!$AF$7:$AI$37

Milage Lookup Range :
='[Time sheets 08 v2.0.xlsx]Jun 19'!$AF$7:$AF$37

Can it be done ?

Thanks

 

[navic]

Not a big help, but maybe some guidelines. If you can help the following.

For example, the A1 cell contains the SheetName Tab like "Products". This cell has been named 'SheetName'.
The INDIRECT formula below returns the contents of the cell from the worksheet concerned. The formula returns the data from the cell 'I30' from the WorkSheet Name Tab 'Products'.

=INDIRECT(ADDRESS(30;9;;;SheetName))

Also, this formula below is similar but has a different meaning.

=INDIRECT("'"&SheetName&"'!I30")

Also different formula with the same result

=INDIRECT($A$1&"!$I$30")

btw: You can use INDIRECT.EXT function if you want get data from closed workbook.

You need to find ways to nestled one of the above formulas in your formula.

 

[Paul at GTS]

Navic, I have not used INDIRECT before so will have a try,

Thanks

 

[navic]

You're welcome,

btw: I apologize for the first formula. My settings are non-us. As delimiter, instead of semicolons you use a comma.

=INDIRECT(ADDRESS(30,9,,,SheetName))

 

[Paul at GTS]

thanks