 # 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.

• 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

I get a #ref error message. Tying to figure it out. Thanks for the help

Regards

• 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

• Re: Vlookup