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)