I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl

Post your formula.

--

wrote in message
...
I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl

On Feb 26, 1:17*pm, "PCLIVE" wrote:
Post your formula.

--

wrote in message

...



I manually put the vlookup formula into one row of my worksheet. *It's
working fine. *When I try to drag the formula down to the other rows,
it copies everything except the range lookup. *It just puts #ref where
the range should be. *That has never happened to me before. *Is there
something I'm doing wrong? *I'm using Excel 2007. *Any help would be
appreciated. *Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)

On Feb 26, 1:17*pm, "PCLIVE" wrote:
Post your formula.

--

wrote in message

...



I manually put the vlookup formula into one row of my worksheet. *It's
working fine. *When I try to drag the formula down to the other rows,
it copies everything except the range lookup. *It just puts #ref where
the range should be. *That has never happened to me before. *Is there
something I'm doing wrong? *I'm using Excel 2007. *Any help would be
appreciated. *Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) 1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message
...
On Feb 26, 1:17 pm, "PCLIVE" wrote:
Post your formula.

--

wrote in message

...



I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.

On Feb 26, 2:00*pm, "Tyro" wrote:
Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) *1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:





Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) *This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.

1000 rows using all 16384 columns in each row?

Tyro

wrote in message
...
On Feb 26, 2:00 pm, "Tyro" wrote:
Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) 1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:





Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.

On Feb 26, 2:12*pm, "Tyro" wrote:
1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:





Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) 1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

....

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

On Feb 26, 2:18*pm, wrote:
On Feb 26, 2:12*pm, "Tyro" wrote:





1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) 1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

....
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

Where's Biff when we need him?!?!? BIFFFF!!!!!!!!!!!!!!!
HELP!!!!!!!!!!!!!!!!!!!!!!!!

When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as needed
But to use this in a VLOOKUP and drag the formula down, you will need to use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address won't
change.

Tyro



wrote in message
...
On Feb 26, 2:12 pm, "Tyro" wrote:
1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:





Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

For performance reasons, I would limit the rows if possible. However,
couldn't you just make the rows constant?

=VLOOKUP(B6,'Vdr#'!$1:$1048576,2,FALSE)

Is there any reason why you wouldn't want to specify the columns instead of
using entire rows?

=VLOOKUP(B7,'Vdr#'!$A:$B,2,FALSE)


HTH,
Paul



--

wrote in message
...
On Feb 26, 2:12 pm, "Tyro" wrote:
1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:





Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

I like your second formula.

I bet that excel will know to use just the usedrange in its =vlookup().

And by limiting the range to just A:B, you don't have to worry about the formula
recalculating when something changes in column C to whatever the last column is
in xl2007.

PCLIVE wrote:

For performance reasons, I would limit the rows if possible. However,
couldn't you just make the rows constant?

=VLOOKUP(B6,'Vdr#'!$1:$1048576,2,FALSE)

Is there any reason why you wouldn't want to specify the columns instead of
using entire rows?

=VLOOKUP(B7,'Vdr#'!$A:$B,2,FALSE)

HTH,
Paul

--

wrote in message
...
On Feb 26, 2:12 pm, "Tyro" wrote:
1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:





Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

--

Dave Peterson

On Feb 26, 2:37*pm, "Tyro" wrote:
When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as needed
But to use this in a VLOOKUP and drag the formula down, you will need to use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address won't
change.

Tyro

wrote in message

...
On Feb 26, 2:12 pm, "Tyro" wrote:





1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

wrote in message

....
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. It works, except the lookup range keeps
changing as I go down the rows. I'll paste the formula as it appears
in 3 of the cells on different rows: =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) this is on row 2. =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) this is on row 4. =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) this is on row 8. Notice the range changing as I
go down. I need the range to stay exactly the same throughout the
whole table. Thanks in advance. Daryl

Thanks Dave,

That does seem to make more sense.

--

"Dave Peterson" wrote in message
...
I like your second formula.

I bet that excel will know to use just the usedrange in its =vlookup().

And by limiting the range to just A:B, you don't have to worry about the
formula
recalculating when something changes in column C to whatever the last
column is
in xl2007.

PCLIVE wrote:

For performance reasons, I would limit the rows if possible. However,
couldn't you just make the rows constant?

=VLOOKUP(B6,'Vdr#'!$1:$1048576,2,FALSE)

Is there any reason why you wouldn't want to specify the columns instead
of
using entire rows?

=VLOOKUP(B7,'Vdr#'!$A:$B,2,FALSE)

HTH,
Paul

--

wrote in message
...
On Feb 26, 2:12 pm, "Tyro" wrote:
1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:





Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the
spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184
cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet.
It's
working fine. When I try to drag the formula down to the other
rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is
there
something I'm doing wrong? I'm using Excel 2007. Any help would
be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

--

Dave Peterson

The formula I showed you was =VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

See the dollar signs? You didn't put them in your formula
=VLOOKUP(B2,'Vdr#'!A1:K2000,2,FALSE)
You used relative addressing so the addresses change. The dollar signs make
the addresses absolute so they don't change

Tyro


wrote in message
...
On Feb 26, 2:37 pm, "Tyro" wrote:
When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify
it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as
needed
But to use this in a VLOOKUP and drag the formula down, you will need to
use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address
won't
change.

Tyro

wrote in message

...
On Feb 26, 2:12 pm, "Tyro" wrote:





1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184
cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet.
It's
working fine. When I try to drag the formula down to the other
rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is
there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. It works, except the lookup range keeps
changing as I go down the rows. I'll paste the formula as it appears
in 3 of the cells on different rows: =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) this is on row 2. =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) this is on row 4. =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) this is on row 8. Notice the range changing as I
go down. I need the range to stay exactly the same throughout the
whole table. Thanks in advance. Daryl

Why are you specifying a lookup table 12 columns wide? VLOOKUP will lookup
a value in the left-most column of your table-array and then return a value
from the column number of that array that you specify. Since you are
specifying the second column of the table-array to return the result from,
then your range does not need to be more than two columns wide.

=VLOOKUP(B7,'Vdr#'!$A:$B,2,FALSE)

HTH,
Paul

--

wrote in message
...
On Feb 26, 2:37 pm, "Tyro" wrote:
When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify
it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as
needed
But to use this in a VLOOKUP and drag the formula down, you will need to
use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address
won't
change.

Tyro

wrote in message

...
On Feb 26, 2:12 pm, "Tyro" wrote:





1000 rows using all 16384 columns in each row?

Tyro

wrote in message

...
On Feb 26, 2:00 pm, "Tyro" wrote:

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184
cells
Just how big is your lookup table?

Tyro

wrote in message

...
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet.
It's
working fine. When I try to drag the formula down to the other
rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is
there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. It works, except the lookup range keeps
changing as I go down the rows. I'll paste the formula as it appears
in 3 of the cells on different rows: =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) this is on row 2. =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) this is on row 4. =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) this is on row 8. Notice the range changing as I
go down. I need the range to stay exactly the same throughout the
whole table. Thanks in advance. Daryl

On Feb 26, 3:20*pm, "Tyro" wrote:
The formula I showed you was *=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

See the dollar signs? *You didn't put them in your formula
=VLOOKUP(B2,'Vdr#'!A1:K2000,2,FALSE)
You used relative addressing so the addresses change. The dollar signs make
the addresses absolute so they don't change

Tyro

wrote in message

...
On Feb 26, 2:37 pm, "Tyro" wrote:





When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify
it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as
needed
But to use this in a VLOOKUP and drag the formula down, you will need to
use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address
won't
change.

Tyro

wrote in message

...
On Feb 26, 2:12 pm, "Tyro" wrote:

1000 rows using all 16384 columns in each row?

Tyro

wrote in message

....
On Feb 26, 2:00 pm, "Tyro" wrote:

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)
1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet
and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184
cells
Just how big is your lookup table?

Tyro

wrote in message

....
On Feb 26, 1:17 pm, "PCLIVE" wrote:

Post your formula.

--

wrote in message

...

I manually put the vlookup formula into one row of my worksheet.
It's
working fine. When I try to drag the formula down to the other
rows,
it copies everything except the range lookup. It just puts #ref
where
the range should be. That has never happened to me before. Is
there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl- Hide quoted text -

- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. *It works, except the lookup range keeps
changing as I go down the rows. *I'll paste the formula as it appears
in 3 of the cells on different rows: *=VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) *this is on row 2. * =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) *this is on row 4. *=VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) *this is on row 8. *Notice the range changing as I
go down. *I need the range to stay exactly the same throughout the
whole table. *Thanks in advance. *Daryl- Hide quoted text -

- Show quoted text -

Thank you so much.... thanks for your patience. Worked perfectly!!!
Thank you again!!!