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
:
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.
:
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.
:
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
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
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
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
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.
..
:
Try this:
=VLOOKUP(A$2,'D:\leblanc\PAYROLL\[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,ROW(A8),0)
Biff
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)
