Prev: EXTENSION OF EXCEL SHEET COLUMNS BEYOND IV GRID
Next: Calculating Turn Around Times excluding Sunday
From: Radhakant Panigrahi on 19 May 2010 01:02
i have data in 10 different sheets (Sheet 2 to Sheet11)with identical columns.
i need to use the vlookup function in Sheet1 taking the range from column E
to H from each sheet.

Is there any tool whereby i can make te vookup function once for all sheets
and need not to use it for 10 times.

regards
From: ozgrid.com on 19 May 2010 01:11
See http://www.ozgrid.com/VBA/VlookupAllSheets.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Radhakant Panigrahi" <rkp.gen(a)gmail.com> wrote in message
news:8A5B07E3-6E82-439D-96A6-FF556386DD32(a)microsoft.com...
>i have data in 10 different sheets (Sheet 2 to Sheet11)with identical
>columns.
> i need to use the vlookup function in Sheet1 taking the range from column
> E
> to H from each sheet.
>
> Is there any tool whereby i can make te vookup function once for all
> sheets
> and need not to use it for 10 times.
>
> regards

From: L. Howard Kittle on 19 May 2010 03:27

Try this which I got from Peo Sjoblom in 2006.

Somewhere on the worksheet list your ten worksheets by name. Select the ten
cells and in the name box name them MySheets. They will now be included in
the formula.

Enter the formula where you want the answer to be displayed using Ctrl +
Shift +Enter. You will get { } around the formula. This called
Array-Enter. If you change the formula later you will need to array-enter
again.

Enter the lookup value in A2 on the formula sheet.

A2:A200 is the lookup table (table_array) on each of the MySheets list,
adjust in the formula to suit the data on the sheets, but all must be the
same on each sheet.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:B200"),2,0)

So, it looks on each sheet for whatever is in A2 of the formula sheet and
returns column B of the sheet where it is found.

HTH
Regards,
Howard



"Radhakant Panigrahi" <rkp.gen(a)gmail.com> wrote in message
news:8A5B07E3-6E82-439D-96A6-FF556386DD32(a)microsoft.com...
>i have data in 10 different sheets (Sheet 2 to Sheet11)with identical
>columns.
> i need to use the vlookup function in Sheet1 taking the range from column
> E
> to H from each sheet.
>
> Is there any tool whereby i can make te vookup function once for all
> sheets
> and need not to use it for 10 times.
>
> regards


 | 
Pages: 1
Prev: EXTENSION OF EXCEL SHEET COLUMNS BEYOND IV GRID
Next: Calculating Turn Around Times excluding Sunday