Macro : Copy/Paste range of value with a Variable starting point

[Helpco]

Hi,

Thank you for reading this post.

I have a table containing the result for different races I extract from another software, compared over 2 year (2011, 2012)

The table look a bit like that :

	2011		2012
	Alpha	Bravo	Charlie	Romeo	TOTAL	Alpha	Bravo	Charlie	Romeo	TOTAL
Race 1	1	53	24	75	153	354	968	95	876	2293
Race 2	5	45245	24	242	45516	345	687	247856	24	248912
Race 3	52468	42	456	68	53034	6987	7858	7857	786547	809249
Race 4	6958	74785	78785	7578	168106	75	7585	7855	7557	23072

<tbody>
</tbody><colgroup><col span="7"><col><col span="3"></colgroup>

The problem is, on the software I extract the data from, the number of columns can change (let's say there is an additional pilot).

In a new tab, I want to have only the result for the year 2012 (I use a color code to show if it was better than 2011, I don't need the actual result to be shown). I tried to use the function Indirect but I can't get it to work between two different worbook, the fuction I tried is :

=Tab1!INDIRECT($B$1&"7") Wich I want it to return Y7 of Tab1. (I typed Y in B1)

but as I say it does not work, any other idea ?????? a sort of Vlookup ???

thank you very much for your help, much appreciated

Co

 

[sheetspread]

=INDIRECT("Tab1!"&B1&7)

 

[AlphaFrog]

=Tab1!INDIRECT($B$1&"7") Wich I want it to return Y7 of Tab1. (I typed Y in B1)

This syntax could work for your INDIRECT formula.

=INDIRECT("Sheet1!" & $B$1 & "7")