VLOOKUP Headache

[ouija]

Hi all,

I have a major problem with using VLOOKUP, here is a simple example of
what i want to achieve...

I have a spreadsheet with 5 worksheets;

MAIN
ARSENAL
ASTON VILLA
BIRMINGHAM
BLACKBURN

on the 'MAIN' worksheet the columns are

A DATE
B TEAM1
C TEAM2
D TEAM1 AVG
E TEAM2 AVG

There are hundreds of lines of data for 'DATE','TEAM1'&''TEAM2'

I want to do a VLOOKUP in column D 'TEAM1 AVG' that looks at the date
in 'DATE', looks at the team in 'TEAM1' and goes to the relevant
worksheet for that team then looks for that date and the number six
columns accross from the date on that worksheet.

My initial VLOOKUP looked like this;

=VLOOKUP(A2,Arsenal!B:G,6,FALSE)

But when I drag this formula all worksheet references stay as
'Arsenal!' because I don't know how to tell it to change the second
part of the lookup according to what is written in column B. The aim is
that if cell B# says Arsenal the worksheet reference in the VLOOKUP is
'Arsenal!' and if it says Aston Villa then the VLOOKUP says 'Aston
Villa!'

I tried to rectify this with a macro but then I end up with specific
cell references as follows:

Sub Macro1()
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "Arsenal"
Range("D2").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Arsenal!C[-2]:C[3],6,FALSE)"
Range("D3").Select
End Sub

Here I have tried copying the contents of B2, then cursoring over to
cell D2 and typing
"=VLOOKUP("
then cursor to column A and pasting the clipboard to the VLOOKUP giving

"=VLOOKUP(A2,Arsenal"
and typing the rest to give
"=VLOOKUP(A2,Arsenal,6,FALSE)"
The macro just shows the result of when i followed that process copy
the name of the team in column B to paste into a vlookup and so the
worksheet name and cell references always remain the same.

Arrghh!

Ultimately I want to be able to either;

1. Write a VLOOKUP in cell D2 which can be drag copied down and update
itself to the correct worksheet reference

or

2. Select column D and run a macro that writes a VLOOKUP specific to
each cell with as little code as possible

Nobody I know can do it and I'm pulling my hair out.

Thanks for bothering to read this nonsense

 

[Bob Phillips]

=VLOOKUP(A2,INDIRECT("'"&B2&"'!B:G"),6,FALSE)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

I don't know how to tell it to change the second
part of the lookup according to what is written in column B.

Try the INDIRECT function:

=VLOOKUP(A2,INDIRECT("'"&B2&"'!B:G"),6,FALSE)


--ron

 

[ouija]

you beauties, sorted

hair growing back