I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
error in the Table Array. I checked my named ranged, and they appear to be
okay. Any other suggestions of what may be wrong?

What is the range, and what is the column index number (3rd argument) in your
vlookup? #REF can come up if the column index number exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
error in the Table Array. I checked my named ranged, and they appear to be
okay. Any other suggestions of what may be wrong?

What I have is a workbook that contains 200 sheets of data. I have created
and inserted a sheet named "Lookup" that I am trying to pull the data from.
Within that 'lookup" sheet, I have various ranges named. So, for example,
column A is CLIN, column B is WBS and column C is Total amt (see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20 and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What I'm trying to do is
by using those named ranges, lookup the WBS number and drop the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time. This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago. The problem is that
my Total amounts have changed, so what I tried to do was create a NEW
"Lookup" sheet, insert it into the workbook and delete the OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just like the old
"lookup", but for some reason the VLOOKUP formula now returns the REF# error
in the Table Array field of the formula box. Confused yet?!? :-)


"bpeltzer" wrote:

What is the range, and what is the column index number (3rd argument) in your
vlookup? #REF can come up if the column index number exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
error in the Table Array. I checked my named ranged, and they appear to be
okay. Any other suggestions of what may be wrong?

It would help us if you showed us the EXACT VLOOKUP formula you're using and
the definitions of any defined names as well.

"Amy" wrote:

What I have is a workbook that contains 200 sheets of data. I have created
and inserted a sheet named "Lookup" that I am trying to pull the data from.
Within that 'lookup" sheet, I have various ranges named. So, for example,
column A is CLIN, column B is WBS and column C is Total amt (see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20 and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What I'm trying to do is
by using those named ranges, lookup the WBS number and drop the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time. This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago. The problem is that
my Total amounts have changed, so what I tried to do was create a NEW
"Lookup" sheet, insert it into the workbook and delete the OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just like the old
"lookup", but for some reason the VLOOKUP formula now returns the REF# error
in the Table Array field of the formula box. Confused yet?!? :-)


"bpeltzer" wrote:

What is the range, and what is the column index number (3rd argument) in your
vlookup? #REF can come up if the column index number exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
error in the Table Array. I checked my named ranged, and they appear to be
okay. Any other suggestions of what may be wrong?

=VLOOKUP($B18,CLIN1A,2,FALSE)

"Bob Umlas, Excel MVP" wrote:

It would help us if you showed us the EXACT VLOOKUP formula you're using and
the definitions of any defined names as well.

"Amy" wrote:

What I have is a workbook that contains 200 sheets of data. I have created
and inserted a sheet named "Lookup" that I am trying to pull the data from.
Within that 'lookup" sheet, I have various ranges named. So, for example,
column A is CLIN, column B is WBS and column C is Total amt (see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20 and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What I'm trying to do is
by using those named ranges, lookup the WBS number and drop the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time. This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago. The problem is that
my Total amounts have changed, so what I tried to do was create a NEW
"Lookup" sheet, insert it into the workbook and delete the OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just like the old
"lookup", but for some reason the VLOOKUP formula now returns the REF# error
in the Table Array field of the formula box. Confused yet?!? :-)


"bpeltzer" wrote:

What is the range, and what is the column index number (3rd argument) in your
vlookup? #REF can come up if the column index number exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
error in the Table Array. I checked my named ranged, and they appear to be
okay. Any other suggestions of what may be wrong?

Is CLIN1A defined to be at least two columns wide? It should be.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Amy" wrote in message
...
=VLOOKUP($B18,CLIN1A,2,FALSE)

"Bob Umlas, Excel MVP" wrote:

It would help us if you showed us the EXACT VLOOKUP formula
you're using and
the definitions of any defined names as well.

"Amy" wrote:

What I have is a workbook that contains 200 sheets of data.
I have created
and inserted a sheet named "Lookup" that I am trying to pull
the data from.
Within that 'lookup" sheet, I have various ranges named. So,
for example,
column A is CLIN, column B is WBS and column C is Total amt
(see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20
and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What
I'm trying to do is
by using those named ranges, lookup the WBS number and drop
the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time.
This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago.
The problem is that
my Total amounts have changed, so what I tried to do was
create a NEW
"Lookup" sheet, insert it into the workbook and delete the
OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just
like the old
"lookup", but for some reason the VLOOKUP formula now
returns the REF# error
in the Table Array field of the formula box. Confused yet?!?
:-)


"bpeltzer" wrote:

What is the range, and what is the column index number
(3rd argument) in your
vlookup? #REF can come up if the column index number
exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and
am getting the REF#
error in the Table Array. I checked my named ranged,
and they appear to be
okay. Any other suggestions of what may be wrong?

Yes, the range for CLIN1A encompasses Column B and C and rows within.


"Chip Pearson" wrote:

Is CLIN1A defined to be at least two columns wide? It should be.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Amy" wrote in message
...
=VLOOKUP($B18,CLIN1A,2,FALSE)

"Bob Umlas, Excel MVP" wrote:

It would help us if you showed us the EXACT VLOOKUP formula
you're using and
the definitions of any defined names as well.

"Amy" wrote:

What I have is a workbook that contains 200 sheets of data.
I have created
and inserted a sheet named "Lookup" that I am trying to pull
the data from.
Within that 'lookup" sheet, I have various ranges named. So,
for example,
column A is CLIN, column B is WBS and column C is Total amt
(see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20
and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What
I'm trying to do is
by using those named ranges, lookup the WBS number and drop
the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time.
This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago.
The problem is that
my Total amounts have changed, so what I tried to do was
create a NEW
"Lookup" sheet, insert it into the workbook and delete the
OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just
like the old
"lookup", but for some reason the VLOOKUP formula now
returns the REF# error
in the Table Array field of the formula box. Confused yet?!?
:-)


"bpeltzer" wrote:

What is the range, and what is the column index number
(3rd argument) in your
vlookup? #REF can come up if the column index number
exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and
am getting the REF#
error in the Table Array. I checked my named ranged,
and they appear to be
okay. Any other suggestions of what may be wrong?

I would double check CLIN1a if I were you. Perhaps it started out as Columns
B and C, but I believe you mentioned some deleting during the process. The
name itself may contain the REF error you are getting.
--
Kevin Vaughn


"Amy" wrote:

Yes, the range for CLIN1A encompasses Column B and C and rows within.


"Chip Pearson" wrote:

Is CLIN1A defined to be at least two columns wide? It should be.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Amy" wrote in message
...
=VLOOKUP($B18,CLIN1A,2,FALSE)

"Bob Umlas, Excel MVP" wrote:

It would help us if you showed us the EXACT VLOOKUP formula
you're using and
the definitions of any defined names as well.

"Amy" wrote:

What I have is a workbook that contains 200 sheets of data.
I have created
and inserted a sheet named "Lookup" that I am trying to pull
the data from.
Within that 'lookup" sheet, I have various ranges named. So,
for example,
column A is CLIN, column B is WBS and column C is Total amt
(see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20
and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What
I'm trying to do is
by using those named ranges, lookup the WBS number and drop
the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time.
This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago.
The problem is that
my Total amounts have changed, so what I tried to do was
create a NEW
"Lookup" sheet, insert it into the workbook and delete the
OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just
like the old
"lookup", but for some reason the VLOOKUP formula now
returns the REF# error
in the Table Array field of the formula box. Confused yet?!?
:-)


"bpeltzer" wrote:

What is the range, and what is the column index number
(3rd argument) in your
vlookup? #REF can come up if the column index number
exceeds the number of
columns in the table array.

"Amy" wrote:

I am trying to use the VLOOKUP fcn in my spreadsheet and
am getting the REF#
error in the Table Array. I checked my named ranged,
and they appear to be
okay. Any other suggestions of what may be wrong?

Yes, if you have deleted the old lookup sheet, then the named ranges
which were on it will now have #REF in the Refers To box if you do
Insert | Name | Define. You will need to delete these old names and
re-instate them for the new lookup sheet.

Hope this helps.

Pete