If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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) |
#7
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
BTW,
it has 23,000 lines. The formula is on all lines I wouldn't use an incrementing Countif on 23,000 rows even though it would work! I'd use something like you did but I didn't know what to calculate for based on my understanding so I just suggested what I did to "coax" a response from the OP. Biff "Biff" wrote in message ... 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) |
#8
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Yep! I agree.
It is bit confusing though as he says 23 lines, but A2:A23 is 22 lines, A24:A46 is 23 lines and, in the first posting, the value being looked up was A2 in each case (which I can understand as it is all for the same employee). By the second posting, the Vlookup was A2, A3 ... A24... A47 He also said Other than starting the formula over every 23 lines it worked great. How could it, if it went outside the table range??? Maybe he will post back with some clarification. -- Regards Roger Govier "Biff" wrote in message ... BTW, it has 23,000 lines. The formula is on all lines I wouldn't use an incrementing Countif on 23,000 rows even though it would work! I'd use something like you did but I didn't know what to calculate for based on my understanding so I just suggested what I did to "coax" a response from the OP. Biff "Biff" wrote in message ... 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) |
#9
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
U r understanding it correctly, but to make it clearer, in the spreadsheet I
am pulling it from there is one row with one check # with several columns. However in the spreadshett I am pulling the information in the data for every column must be in a separate rown and the check # is repeated on every row for 23 row. So in Spreadsheet Paytst50-mod.xls the check # is listed in a2..a1522, but in Payroll import.xls the check # for a2 is listed 23 times then it pulls the ck # for a2 from Paytst50-mod.xls 23 more times but the data is pulled from column 8-20, (column Index postion from spreadsheet Paytst50-mod.xls , then I calulte a formula for few lines, which I may need help with later on formult repeating. This file is being imported into a ..csv file. After I get the information pulled in, then I will copy and paste values only. Does this make it clearer. Thanks for all the help we are getting closer and sorry I fell a sleep, wished I had been up when u guess were doing working this out. "Biff" wrote: BTW, it has 23,000 lines. The formula is on all lines I wouldn't use an incrementing Countif on 23,000 rows even though it would work! I'd use something like you did but I didn't know what to calculate for based on my understanding so I just suggested what I did to "coax" a response from the OP. Biff "Biff" wrote in message ... 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) |
#10
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Hi
That's fine, but what Biff and I can't understand is your Data table runs from column A to column Y this makes 25 columns in total. Now you say you are taking data from this table from columns 8 to 20 which is OK, as both column 8 and column 20 exist within the 25 column range. However, from 8 to 20 is 13 lines, yet you wanted the formula to be repeated (and incremented) 23 times. If it is repeated 23 times, then it would be looking for data from column 30 - therefore outside of the table, and would return an error. If the formula is only looking up data for 13 lines, which would take it from A2 through to A14, what happens on lines A15 through to A23 before you get back to repeating the block of 8 to 20 again? -- Regards Roger Govier "klafert" wrote in message ... U r understanding it correctly, but to make it clearer, in the spreadsheet I am pulling it from there is one row with one check # with several columns. However in the spreadshett I am pulling the information in the data for every column must be in a separate rown and the check # is repeated on every row for 23 row. So in Spreadsheet Paytst50-mod.xls the check # is listed in a2..a1522, but in Payroll import.xls the check # for a2 is listed 23 times then it pulls the ck # for a2 from Paytst50-mod.xls 23 more times but the data is pulled from column 8-20, (column Index postion from spreadsheet Paytst50-mod.xls , then I calulte a formula for few lines, which I may need help with later on formult repeating. This file is being imported into a .csv file. After I get the information pulled in, then I will copy and paste values only. Does this make it clearer. Thanks for all the help we are getting closer and sorry I fell a sleep, wished I had been up when u guess were doing working this out. "Biff" wrote: BTW, it has 23,000 lines. The formula is on all lines I wouldn't use an incrementing Countif on 23,000 rows even though it would work! I'd use something like you did but I didn't know what to calculate for based on my understanding so I just suggested what I did to "coax" a response from the OP. Biff "Biff" wrote in message ... 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) |
Thread Tools | |
Display Modes | |
|
|