Vlookup

    I have 263 worksheets with account activity information for each account seperatelt in each sheet. I have a master sheet with account numbers and would like a vlookup to go and look up acitivity from each sheet.


    Ex: Master sheet
    Account1 - VLOOKUP(D4,'(1)'!$B$20:$BA$1000,8,FALSE))
    Account2 - VLOOKUP(D4,'(2)'!$B$20:$BA$1000,8,FALSE))
    Account3 - VLOOKUP(D4,'(3)'!$B$20:$BA$1000,8,FALSE))


    Is there a way where the number of the sheet can be made to change automatically (1), (2) when I copy and paste the formula to the next row.


    Please help

    Re: Vlookup


    Hi Geetha,


    Try this:


    [vba]
    =VLOOKUP(D4,INDIRECT("'"&+(ROW()-3)&"'!$A$1:B2"),2,FALSE)
    [/vba]


    Change the references accordingly. I assumed that you are entering the data in 4 th row onwords and if it not so change the formula accordingly so that it gives 1,2 and so on..


    HTH.

    Re: Vlookup


    Hi Geetha,


    Please post the formula which you have used. Is the formula returning #Ref for all the cells or some of the range.


    HTH

    Re: Vlookup


    =VLOOKUP(G6,INDIRECT("'"&"("+(ROW()-1)&")"&"'!B20:BA1000"),8,FALSE)


    My sheets are numbered (1), (2). SO I changed the formula to include the paranthesis. The formula works in parts - ("'"&"("+(ROW()-1)&")"&"'" returns the sheet number. However the same fails when I include the "Indirect" formula and incorporate into the vlookup.


    Any help would be appreciated. Thanks in advance


    Regards

    Re: Vlookup


    Hi Geetha,


    Try this:


    [vba]
    =VLOOKUP(G2,INDIRECT("'("&+(ROW()-1)&")'!B2:C10"),2,FALSE)
    [/vba]


    In your formula "&" is missing before +ROW(). Change the sheet references accordingly.


    HTH

    Re: Vlookup


    It is never usually necessary to have your data stored this way. You are making work for yourself. Get all data correctly laid out and you can use AutoFilter,AdvancedFilter, PivotTables etc. Start by reading this
    http://ozgrid.com/forum/announcement.php?f=8

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login