Using Vlookup with multiple sheet

[Jammings]

I am trying to update data on mutiply sheet into one spreadsheet
This is a fornightly payroll workbook where each sheet is the date of the
fornight payment. After I set up the vlookup formulae to pick up the first
pay period in say colum c. Can I edit the formulae to pick up data in the
next sheet, without actually typing in the sheet name or using find and
replace.

Staff 4-Jan-08 18-Jan-08 1-Feb-08 15-Feb-08 29-Feb-08

Rusty Brown 0 14,300 10,300 14,300
Uton COUBOURNE 0 28,375
Ernol FOX 0 22,454
Anseka Gibson 0 14,300

 

[T. Valko]

As long as your sheet names *exactly* match the column dates and the lookup
table is in the *exact* same location on each sheet.

B1:F1 = 4-Jan-08 18-Jan-08 1-Feb-08 15-Feb-08 29-Feb-08
A2 = Rusty Brown

Enter this formula in B2 and copy across then down as needed (adjust the
range of the lookup table to suit):

=VLOOKUP($A2,INDIRECT("'"&TEXT(B$1,"d-mmm-yy")&"'!A1:B10"),2,0)

 

[Jammings]

The formulae works, thanks much, however I am interested in understanding the
components of the formulae, eg What does the ""& text represent

 

[T. Valko]

I am interested in understanding the components of the formulae

=VLOOKUP($A2,INDIRECT("'"&TEXT(B$1,"d-mmm-yy")&"'!A1:B10"),2,0)

Let's look at this example:

B1 = 4-Jan-08

B1 contains the date 4-Jan-08 and you also have a sheet named 4-Jan-08 that
you want to reference in a lookup formula.

In Excel dates entered in cells are really numbers formatted to look like
dates. See this topic in Excel help for an explanation:

About dates and date systems

The sheet name is a text string representing a date while the true
underlying value of cell B1 is a number. The true underlying numeric value
of cell B1 is 39451.

If we were to reference cell B1 without some sort of "transformation" we
would be referencing the underlying numeric value 39451 and this doesn't
match the text string 4-Jan-08 which is the sheet name. So, we have to
somehow convert 39451 to a TEXT string that matches the sheet name 4-Jan-08.

We do that using the TEXT() function.

TEXT(B$1,"d-mmm-yy")

Takes the numeric value of cell B1 and converts it to a TEXT string as a
date in the format we specify: "d-mmm-yy".

TEXT(B$1,"d-mmm-yy") returns the TEXT string 4-Jan-08 which now matches the
sheet name 4-Jan-08.

When sheet names contain numbers, dates or spaces Excel requires that a
reference to sheet name be enclosed in single quotes: '4-Jan-08'!A1

In the formula above we use the INDIRECT function to build the reference to
sheet 4-Jan-08. It can get really confusing trying to decipher all those
quotes. The INDIRECT() function converts a TEXT representation of a
reference to a valid reference that can be used in other functions. That's
what all those quotes do. When you double quote " " something in Excel,
Excel evaluates whatever's inside the quotes as TEXT (even if it's a
number!): "10". That is no longer the NUMBER 10 but is the TEXT string 10.

Here is what the formula evaluates to in the end:

=VLOOKUP($A2,'4-Jan-08'!$A$1:$B$10,2,0)



exp101