| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
I have a spreadsheet that I use the vlookup command. However, I have 23
lines per payroll check per employee - I am getting the data I want. However it has 23,000 lines. The formula is on all lines but the column Index # chaning to pull the correct information. Is there a way to copy this forumla but have it change the column Index # automatically, instead if me manually change each column when necessary. Example below - hopefully this will clear up any confusion. =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying forumla for vlook up but changing the column Index #
Try this:
=VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0) Biff "klafert" wrote in message ... I have a spreadsheet that I use the vlookup command. However, I have 23 lines per payroll check per employee - I am getting the data I want. However it has 23,000 lines. The formula is on all lines but the column Index # chaning to pull the correct information. Is there a way to copy this forumla but have it change the column Index # automatically, instead if me manually change each column when necessary. Example below - hopefully this will clear up any confusion. =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying forumla for vlook up but changing the column Index #
This worked, the only problem is that after the 23 line I need it to look for
the next check number. Example Check # 1 is A2-A23, Check #2 is a24-A46 , check # 3 is A47-A69. I guess I should have pasted my example as follows: =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A3,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A4,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) The 24th line would be: =VLOOKUP(A24,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) and the 47th line would be: =VLOOKUP(A47,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) Other than starting the formula over every 23 lines it worked great. ... "Biff" wrote: Try this: =VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0) Biff "klafert" wrote in message ... I have a spreadsheet that I use the vlookup command. However, I have 23 lines per payroll check per employee - I am getting the data I want. However it has 23,000 lines. The formula is on all lines but the column Index # chaning to pull the correct information. Is there a way to copy this forumla but have it change the column Index # automatically, instead if me manually change each column when necessary. Example below - hopefully this will clear up any confusion. =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying forumla for vlook up but changing the column Index #
If you start with column 8 and increment it for 23 rows then that takes you
outside of your lookup table - $A$2:$Y$1522. My guess is you want something like this: =VLOOKUP(A5,A$1:F$3,COUNTIF(A$5:A5,A5)+1,0) The column number will start at 2 and increment 1 for each instance of the lookup value and will restart with a new lookup value. Biff "klafert" wrote in message ... This worked, the only problem is that after the 23 line I need it to look for the next check number. Example Check # 1 is A2-A23, Check #2 is a24-A46 , check # 3 is A47-A69. I guess I should have pasted my example as follows: =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A3,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A4,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) The 24th line would be: =VLOOKUP(A24,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) and the 47th line would be: =VLOOKUP(A47,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) Other than starting the formula over every 23 lines it worked great. .. "Biff" wrote: Try this: =VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0) Biff "klafert" wrote in message ... I have a spreadsheet that I use the vlookup command. However, I have 23 lines per payroll check per employee - I am getting the data I want. However it has 23,000 lines. The formula is on all lines but the column Index # chaning to pull the correct information. Is there a way to copy this forumla but have it change the column Index # automatically, instead if me manually change each column when necessary. Example below - hopefully this will clear up any confusion. =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying forumla for vlook up but changing the column Index #
Hi Biff
No it is not incrementing by 23 rows from 8 (which I agree would take it outside of the table) it incrementing from 8 to 23 then back to 8 again. I think that your original posting modified to =VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,MOD(ROW(A16),16)+8,0) will cycle through the necessary range 8 to 23 repeatedly. -- Regards Roger Govier "Biff" wrote in message ... If you start with column 8 and increment it for 23 rows then that takes you outside of your lookup table - $A$2:$Y$1522. My guess is you want something like this: =VLOOKUP(A5,A$1:F$3,COUNTIF(A$5:A5,A5)+1,0) The column number will start at 2 and increment 1 for each instance of the lookup value and will restart with a new lookup value. Biff "klafert" wrote in message ... This worked, the only problem is that after the 23 line I need it to look for the next check number. Example Check # 1 is A2-A23, Check #2 is a24-A46 , check # 3 is A47-A69. I guess I should have pasted my example as follows: =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A3,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A4,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) The 24th line would be: =VLOOKUP(A24,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) and the 47th line would be: =VLOOKUP(A47,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) Other than starting the formula over every 23 lines it worked great. .. "Biff" wrote: Try this: =VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0) Biff "klafert" wrote in message ... I have a spreadsheet that I use the vlookup command. However, I have 23 lines per payroll check per employee - I am getting the data I want. However it has 23,000 lines. The formula is on all lines but the column Index # chaning to pull the correct information. Is there a way to copy this forumla but have it change the column Index # automatically, instead if me manually change each column when necessary. Example below - hopefully this will clear up any confusion. =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying forumla for vlook up but changing the column Index #
Hmmm....
The way I read it is...... I have 23 lines per payroll check per employee Example Check # 1 is A2-A23, Check #2 is a24-A46 , check # 3 is A47-A69 Each lookup value is listed 22 (or 23 ?) times therefore needs 22 (or 23 ?) column_index_numbers. So, if you start at 8 and increment for 21 (or 22 ?) more lookup_values then you're outside the lookup_table. Biff "Roger Govier" wrote in message ... Hi Biff No it is not incrementing by 23 rows from 8 (which I agree would take it outside of the table) it incrementing from 8 to 23 then back to 8 again. I think that your original posting modified to =VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,MOD(ROW(A16),16)+8,0) will cycle through the necessary range 8 to 23 repeatedly. -- Regards Roger Govier "Biff" wrote in message ... If you start with column 8 and increment it for 23 rows then that takes you outside of your lookup table - $A$2:$Y$1522. My guess is you want something like this: =VLOOKUP(A5,A$1:F$3,COUNTIF(A$5:A5,A5)+1,0) The column number will start at 2 and increment 1 for each instance of the lookup value and will restart with a new lookup value. Biff "klafert" wrote in message ... This worked, the only problem is that after the 23 line I need it to look for the next check number. Example Check # 1 is A2-A23, Check #2 is a24-A46 , check # 3 is A47-A69. I guess I should have pasted my example as follows: =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A3,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A4,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) The 24th line would be: =VLOOKUP(A24,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) and the 47th line would be: =VLOOKUP(A47,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) Other than starting the formula over every 23 lines it worked great. .. "Biff" wrote: Try this: =VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0) Biff "klafert" wrote in message ... I have a spreadsheet that I use the vlookup command. However, I have 23 lines per payroll check per employee - I am getting the data I want. However it has 23,000 lines. The formula is on all lines but the column Index # chaning to pull the correct information. Is there a way to copy this forumla but have it change the column Index # automatically, instead if me manually change each column when necessary. Example below - hopefully this will clear up any confusion. =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0) =VLOOKUP(A2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro | Excel Discussion (Misc queries) | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
| Copying the contents of a column into a chart | Excel Worksheet Functions | |||
Hybrid Mode
