Index match from grouped tabs

[TonyMcCann]

Hi,
I am trying to record a basic macro incorporating index match for a group of tabs.

Each tab is for a separate product and the layout for each tab is identical. The cell E2 is the identifier cell. In cell E20 I am trying to pull info from cell F21 from a file updated on a weekly basis known as "last weeks plinks", and continuing the formula across to N21. This is to update with historical data on a week by week basis. Ihave copied and pasted cells to be pulled as values to prevent errors pulling formulae.

I have used the following formula -

=index('[lastweeksplinks.xls]productAroductZ'!F21,match('ProductA:ProductZ'1$E$2,'[lastweeksplinks.xls]ProductA:ProductZ'!$E$2,0))

This reults in "#value! rather the info appearing.

Can anyone help?

Many thanks
Tony

 

[Andrew Poulsom]

Sorry, you can't use 3D references with INDEX and MATCH.

 

[TonyMcCann]

thanks Andrew.

Is there any other slick way of performing this task?