Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup with multiple sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup with multiple sheet
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) -- Biff Microsoft Excel MVP "Jammings" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup with multiple sheet
The formulae works, thanks much, however I am interested in understanding the
components of the formulae, eg What does the ""& text represent "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Jammings" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup with multiple sheet
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 -- Biff Microsoft Excel MVP "Jammings" wrote in message ... The formulae works, thanks much, however I am interested in understanding the components of the formulae, eg What does the ""& text represent "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Jammings" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup in a multiple sheet file | Excel Worksheet Functions | |||
lookup single value in one sheet, return multiple results from theother sheet | Excel Worksheet Functions | |||
Vlookup with Multiple criteria and multiple sheets | Excel Worksheet Functions | |||
connecting multiple cells to new sheet (VLOOKUP?IF?) | Excel Worksheet Functions | |||
How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) |