Vlookup using tabs with specific cell reference

Hi there,

I have looked across the forum and found similar problems but cannot piece together enough info using the VLOOKUP in combination with indirect to solve my problem. I am pretty new to vlookup and usually just include a specific tab reference to pull out data but would like to understand a more dynamic way of doing this. Brief synopsis below:

Totals tab contains a list of codes in column A. Each one of these codes has a corresponding tab named after it. Within each tab there is a sum total box in cell Q2.

Totals sheet:
Column A Column B
[TABLE="width: 207"]

[tr]

[td]

Campaign #

[/td]

[td]

Cost to date

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

502216

[/td]

[td]

£ 34,890.74

[/td]

[/tr]

[tr]

[td]

502217

[/td]

[td]

£ 7,044.00

[/td]

[/tr]

[tr]

[td]

502218

[/td]

[td]

£ 3,858.00

[/td]

[/tr]

[tr]

[td]

502220

[/td]

[td]

£ 3,806.00

[/td]

[/tr]

[tr]

[td]

502222

[/td]

[td]

£ 2,706.00

[/td]

[/tr]

[tr]

[td]

502223

[/td]

[td]

£ 1,428.00

[/td]

[/tr]

[tr]

[td]

502227

[/td]

[td]

£ 4,262.00

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

So currently I type the campaign number in Column A, name my relevant tab the same and my formula in Column B reads =SUM('502216'!Q2)

I want to be able to type a formula in Column B so that as I type a new campaign number in column A it will look for the tab named the same and then pull the total in from cell Q2 in that tab.

I'm getting lost amongst the VLOOKUP and INDIRECT combination......any help and explanation would be greatly appreciated.

Thanks
Wayne

Re: Vlookup using tabs with specific cell reference

Quote from NBVC;770504

Did you try?

=INDIRECT("'"&A2&"'!Q2")

Ha that simple! I have clearly been over thinking it.

Thanks NBVC much appreciated!