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 |
#11
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Roger you are right the 1st check had 22 lines but the person helping me who
prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#12
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
I can send you the 2 spreadsheet if you would like. I guess my explainatin
is not very clear, but it is not as confusing as it sounds once you have seen the actual spreadsheet. Actually the formula doesnt need to be repeated 23 times just to pull the information from column 8-20. And then there 2 more column that actually needs to be teh same as column8-9. But I guess at this point better to send you the actual spreadsheets if you wouldnt mind. "Roger Govier" wrote: 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) |
#13
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Hi
Sure, send me the sheets and I will take alook. remove NOSPAM from my email address to send -- Regards Roger Govier "klafert" wrote in message ... I can send you the 2 spreadsheet if you would like. I guess my explainatin is not very clear, but it is not as confusing as it sounds once you have seen the actual spreadsheet. Actually the formula doesnt need to be repeated 23 times just to pull the information from column 8-20. And then there 2 more column that actually needs to be teh same as column8-9. But I guess at this point better to send you the actual spreadsheets if you wouldnt mind. "Roger Govier" wrote: 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) |
#14
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
But your are right I only need the formula for the 1st check, which is 22
lines rows 2-19. However, rows 5 & 19 will have the same formula as 6 & 19 will be the same exact formula. Apprecite any help? We are so close!!!! Thanking you in advance "klafert" wrote: Roger you are right the 1st check had 22 lines but the person helping me who prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#15
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Hi
You said you were sending me the files. Nothing received as yet. -- Regards Roger Govier "klafert" wrote in message ... But your are right I only need the formula for the 1st check, which is 22 lines rows 2-19. However, rows 5 & 19 will have the same formula as 6 & 19 will be the same exact formula. Apprecite any help? We are so close!!!! Thanking you in advance "klafert" wrote: Roger you are right the 1st check had 22 lines but the person helping me who prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#16
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
sending now - was waiting for you tell me it was ok to e-mail - sorry coming
in 2 minutes. I assume I use the e-mail that I saw when I click on your name in the uk? right? "Roger Govier" wrote: Hi You said you were sending me the files. Nothing received as yet. -- Regards Roger Govier "klafert" wrote in message ... But your are right I only need the formula for the 1st check, which is 22 lines rows 2-19. However, rows 5 & 19 will have the same formula as 6 & 19 will be the same exact formula. Apprecite any help? We are so close!!!! Thanking you in advance "klafert" wrote: Roger you are right the 1st check had 22 lines but the person helping me who prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#17
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Sent the spreadsheets in a .zip file. Let me know if you got it ok.
"Roger Govier" wrote: Hi You said you were sending me the files. Nothing received as yet. -- Regards Roger Govier "klafert" wrote in message ... But your are right I only need the formula for the 1st check, which is 22 lines rows 2-19. However, rows 5 & 19 will have the same formula as 6 & 19 will be the same exact formula. Apprecite any help? We are so close!!!! Thanking you in advance "klafert" wrote: Roger you are right the 1st check had 22 lines but the person helping me who prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#18
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
As I put in an earlier message, remove the word NOSPAM from the address
-- Regards Roger Govier "klafert" wrote in message ... sending now - was waiting for you tell me it was ok to e-mail - sorry coming in 2 minutes. I assume I use the e-mail that I saw when I click on your name in the uk? right? "Roger Govier" wrote: Hi You said you were sending me the files. Nothing received as yet. -- Regards Roger Govier "klafert" wrote in message ... But your are right I only need the formula for the 1st check, which is 22 lines rows 2-19. However, rows 5 & 19 will have the same formula as 6 & 19 will be the same exact formula. Apprecite any help? We are so close!!!! Thanking you in advance "klafert" wrote: Roger you are right the 1st check had 22 lines but the person helping me who prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#19
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
Our mails seem to be crossing.
Nothing received. send to roger at technology4u dot co dot uk Do the obvious things to turn the above to a valid email address -- Regards Roger Govier "klafert" wrote in message news Sent the spreadsheets in a .zip file. Let me know if you got it ok. "Roger Govier" wrote: Hi You said you were sending me the files. Nothing received as yet. -- Regards Roger Govier "klafert" wrote in message ... But your are right I only need the formula for the 1st check, which is 22 lines rows 2-19. However, rows 5 & 19 will have the same formula as 6 & 19 will be the same exact formula. Apprecite any help? We are so close!!!! Thanking you in advance "klafert" wrote: Roger you are right the 1st check had 22 lines but the person helping me who prepped the sheet copy 23 lines for every check after the 1st check. I guess they didnt realize that line one was the heading however, I am only pulling information for column 8-20 from Paytst50-mod.xls. Worse comes to worse I can do some kind of sort and delete what may be an extra line. "klafert" wrote: 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) |
#20
|
|||
|
|||
Copying forumla for vlook up but changing the column Index #
|
Thread Tools | |
Display Modes | |
|
|