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


    Did you try?


    [COLOR="#0000FF"]=INDIRECT("'"&A2&"'!Q2")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • 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!