software

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.

quot;Amyquot; wrote:

gt; I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
gt; error in the Table Array. I checked my named ranged, and they appear to be
gt; 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 quot;Lookupquot; that I am trying to pull the data from.
Within that 'lookupquot; 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.

CLINWBSTOTAL AMT
0001A01.01.03472
0001A01.01.045,364

To confuse things further, this function worked at one time. This workbook
that I have had a quot;Lookupquot; 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
quot;Lookupquot; sheet, insert it into the workbook and delete the OLD quot;Lookupquot;
sheet. From what I can tell the new quot;lookupquot; is set up just like the old
quot;lookupquot;, but for some reason the VLOOKUP formula now returns the REF# error
in the Table Array field of the formula box. Confused yet?!? :-)quot;bpeltzerquot; wrote:

gt; What is the range, and what is the column index number (3rd argument) in your
gt; vlookup? #REF can come up if the column index number exceeds the number of
gt; columns in the table array.
gt;
gt; quot;Amyquot; wrote:
gt;
gt; gt; I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
gt; gt; error in the Table Array. I checked my named ranged, and they appear to be
gt; gt; 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.

quot;Amyquot; wrote:

gt; What I have is a workbook that contains 200 sheets of data. I have created
gt; and inserted a sheet named quot;Lookupquot; that I am trying to pull the data from.
gt; Within that 'lookupquot; sheet, I have various ranges named. So, for example,
gt; column A is CLIN, column B is WBS and column C is Total amt (see below).
gt; CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20 and so on. I
gt; have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What I'm trying to do is
gt; by using those named ranges, lookup the WBS number and drop the Total amt
gt; into my other 200 sheets. I am using column index # 2.
gt;
gt; CLINWBSTOTAL AMT
gt; 0001A01.01.03472
gt; 0001A01.01.045,364
gt;
gt; To confuse things further, this function worked at one time. This workbook
gt; that I have had a quot;Lookupquot; sheet in it, say 2 weeks ago. The problem is that
gt; my Total amounts have changed, so what I tried to do was create a NEW
gt; quot;Lookupquot; sheet, insert it into the workbook and delete the OLD quot;Lookupquot;
gt; sheet. From what I can tell the new quot;lookupquot; is set up just like the old
gt; quot;lookupquot;, but for some reason the VLOOKUP formula now returns the REF# error
gt; in the Table Array field of the formula box. Confused yet?!? :-)
gt;
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; What is the range, and what is the column index number (3rd argument) in your
gt; gt; vlookup? #REF can come up if the column index number exceeds the number of
gt; gt; columns in the table array.
gt; gt;
gt; gt; quot;Amyquot; wrote:
gt; gt;
gt; gt; gt; I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
gt; gt; gt; error in the Table Array. I checked my named ranged, and they appear to be
gt; gt; gt; okay. Any other suggestions of what may be wrong?

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

quot;Bob Umlas, Excel MVPquot; wrote:

gt; It would help us if you showed us the EXACT VLOOKUP formula you're using and
gt; the definitions of any defined names as well.
gt;
gt; quot;Amyquot; wrote:
gt;
gt; gt; What I have is a workbook that contains 200 sheets of data. I have created
gt; gt; and inserted a sheet named quot;Lookupquot; that I am trying to pull the data from.
gt; gt; Within that 'lookupquot; sheet, I have various ranges named. So, for example,
gt; gt; column A is CLIN, column B is WBS and column C is Total amt (see below).
gt; gt; CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20 and so on. I
gt; gt; have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What I'm trying to do is
gt; gt; by using those named ranges, lookup the WBS number and drop the Total amt
gt; gt; into my other 200 sheets. I am using column index # 2.
gt; gt;
gt; gt; CLINWBSTOTAL AMT
gt; gt; 0001A01.01.03472
gt; gt; 0001A01.01.045,364
gt; gt;
gt; gt; To confuse things further, this function worked at one time. This workbook
gt; gt; that I have had a quot;Lookupquot; sheet in it, say 2 weeks ago. The problem is that
gt; gt; my Total amounts have changed, so what I tried to do was create a NEW
gt; gt; quot;Lookupquot; sheet, insert it into the workbook and delete the OLD quot;Lookupquot;
gt; gt; sheet. From what I can tell the new quot;lookupquot; is set up just like the old
gt; gt; quot;lookupquot;, but for some reason the VLOOKUP formula now returns the REF# error
gt; gt; in the Table Array field of the formula box. Confused yet?!? :-)
gt; gt;
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt; gt; What is the range, and what is the column index number (3rd argument) in your
gt; gt; gt; vlookup? #REF can come up if the column index number exceeds the number of
gt; gt; gt; columns in the table array.
gt; gt; gt;
gt; gt; gt; quot;Amyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
gt; gt; gt; gt; error in the Table Array. I checked my named ranged, and they appear to be
gt; gt; gt; gt; 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.comquot;Amyquot; gt; wrote in message
...
gt; =VLOOKUP($B18,CLIN1A,2,FALSE)
gt;
gt; quot;Bob Umlas, Excel MVPquot; wrote:
gt;
gt;gt; It would help us if you showed us the EXACT VLOOKUP formula
gt;gt; you're using and
gt;gt; the definitions of any defined names as well.
gt;gt;
gt;gt; quot;Amyquot; wrote:
gt;gt;
gt;gt; gt; What I have is a workbook that contains 200 sheets of data.
gt;gt; gt; I have created
gt;gt; gt; and inserted a sheet named quot;Lookupquot; that I am trying to pull
gt;gt; gt; the data from.
gt;gt; gt; Within that 'lookupquot; sheet, I have various ranges named. So,
gt;gt; gt; for example,
gt;gt; gt; column A is CLIN, column B is WBS and column C is Total amt
gt;gt; gt; (see below).
gt;gt; gt; CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20
gt;gt; gt; and so on. I
gt;gt; gt; have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What
gt;gt; gt; I'm trying to do is
gt;gt; gt; by using those named ranges, lookup the WBS number and drop
gt;gt; gt; the Total amt
gt;gt; gt; into my other 200 sheets. I am using column index # 2.
gt;gt; gt;
gt;gt; gt; CLIN WBS TOTAL AMT
gt;gt; gt; 0001A 01.01.03 472
gt;gt; gt; 0001A 01.01.04 5,364
gt;gt; gt;
gt;gt; gt; To confuse things further, this function worked at one time.
gt;gt; gt; This workbook
gt;gt; gt; that I have had a quot;Lookupquot; sheet in it, say 2 weeks ago.
gt;gt; gt; The problem is that
gt;gt; gt; my Total amounts have changed, so what I tried to do was
gt;gt; gt; create a NEW
gt;gt; gt; quot;Lookupquot; sheet, insert it into the workbook and delete the
gt;gt; gt; OLD quot;Lookupquot;
gt;gt; gt; sheet. From what I can tell the new quot;lookupquot; is set up just
gt;gt; gt; like the old
gt;gt; gt; quot;lookupquot;, but for some reason the VLOOKUP formula now
gt;gt; gt; returns the REF# error
gt;gt; gt; in the Table Array field of the formula box. Confused yet?!?
gt;gt; gt; :-)
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;bpeltzerquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; What is the range, and what is the column index number
gt;gt; gt; gt; (3rd argument) in your
gt;gt; gt; gt; vlookup? #REF can come up if the column index number
gt;gt; gt; gt; exceeds the number of
gt;gt; gt; gt; columns in the table array.
gt;gt; gt; gt;
gt;gt; gt; gt; quot;Amyquot; wrote:
gt;gt; gt; gt;
gt;gt; gt; gt; gt; I am trying to use the VLOOKUP fcn in my spreadsheet and
gt;gt; gt; gt; gt; am getting the REF#
gt;gt; gt; gt; gt; error in the Table Array. I checked my named ranged,
gt;gt; gt; gt; gt; and they appear to be
gt;gt; gt; gt; gt; okay. Any other suggestions of what may be wrong?
Yes, the range for CLIN1A encompasses Column B and C and rows within.quot;Chip Pearsonquot; wrote:

gt; Is CLIN1A defined to be at least two columns wide? It should be.
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt; quot;Amyquot; gt; wrote in message
gt; ...
gt; gt; =VLOOKUP($B18,CLIN1A,2,FALSE)
gt; gt;
gt; gt; quot;Bob Umlas, Excel MVPquot; wrote:
gt; gt;
gt; gt;gt; It would help us if you showed us the EXACT VLOOKUP formula
gt; gt;gt; you're using and
gt; gt;gt; the definitions of any defined names as well.
gt; gt;gt;
gt; gt;gt; quot;Amyquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; What I have is a workbook that contains 200 sheets of data.
gt; gt;gt; gt; I have created
gt; gt;gt; gt; and inserted a sheet named quot;Lookupquot; that I am trying to pull
gt; gt;gt; gt; the data from.
gt; gt;gt; gt; Within that 'lookupquot; sheet, I have various ranges named. So,
gt; gt;gt; gt; for example,
gt; gt;gt; gt; column A is CLIN, column B is WBS and column C is Total amt
gt; gt;gt; gt; (see below).
gt; gt;gt; gt; CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20
gt; gt;gt; gt; and so on. I
gt; gt;gt; gt; have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What
gt; gt;gt; gt; I'm trying to do is
gt; gt;gt; gt; by using those named ranges, lookup the WBS number and drop
gt; gt;gt; gt; the Total amt
gt; gt;gt; gt; into my other 200 sheets. I am using column index # 2.
gt; gt;gt; gt;
gt; gt;gt; gt; CLIN WBS TOTAL AMT
gt; gt;gt; gt; 0001A 01.01.03 472
gt; gt;gt; gt; 0001A 01.01.04 5,364
gt; gt;gt; gt;
gt; gt;gt; gt; To confuse things further, this function worked at one time.
gt; gt;gt; gt; This workbook
gt; gt;gt; gt; that I have had a quot;Lookupquot; sheet in it, say 2 weeks ago.
gt; gt;gt; gt; The problem is that
gt; gt;gt; gt; my Total amounts have changed, so what I tried to do was
gt; gt;gt; gt; create a NEW
gt; gt;gt; gt; quot;Lookupquot; sheet, insert it into the workbook and delete the
gt; gt;gt; gt; OLD quot;Lookupquot;
gt; gt;gt; gt; sheet. From what I can tell the new quot;lookupquot; is set up just
gt; gt;gt; gt; like the old
gt; gt;gt; gt; quot;lookupquot;, but for some reason the VLOOKUP formula now
gt; gt;gt; gt; returns the REF# error
gt; gt;gt; gt; in the Table Array field of the formula box. Confused yet?!?
gt; gt;gt; gt; :-)
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; quot;bpeltzerquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt; gt; What is the range, and what is the column index number
gt; gt;gt; gt; gt; (3rd argument) in your
gt; gt;gt; gt; gt; vlookup? #REF can come up if the column index number
gt; gt;gt; gt; gt; exceeds the number of
gt; gt;gt; gt; gt; columns in the table array.
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; quot;Amyquot; wrote:
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; gt; I am trying to use the VLOOKUP fcn in my spreadsheet and
gt; gt;gt; gt; gt; gt; am getting the REF#
gt; gt;gt; gt; gt; gt; error in the Table Array. I checked my named ranged,
gt; gt;gt; gt; gt; gt; and they appear to be
gt; gt;gt; gt; gt; gt; okay. Any other suggestions of what may be wrong?
gt;
gt;
gt;

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 Vaughnquot;Amyquot; wrote:

gt; Yes, the range for CLIN1A encompasses Column B and C and rows within.
gt;
gt;
gt; quot;Chip Pearsonquot; wrote:
gt;
gt; gt; Is CLIN1A defined to be at least two columns wide? It should be.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Cordially,
gt; gt; Chip Pearson
gt; gt; Microsoft MVP - Excel
gt; gt; Pearson Software Consulting, LLC
gt; gt; www.cpearson.com
gt; gt;
gt; gt;
gt; gt; quot;Amyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; =VLOOKUP($B18,CLIN1A,2,FALSE)
gt; gt; gt;
gt; gt; gt; quot;Bob Umlas, Excel MVPquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; It would help us if you showed us the EXACT VLOOKUP formula
gt; gt; gt;gt; you're using and
gt; gt; gt;gt; the definitions of any defined names as well.
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Amyquot; wrote:
gt; gt; gt;gt;
gt; gt; gt;gt; gt; What I have is a workbook that contains 200 sheets of data.
gt; gt; gt;gt; gt; I have created
gt; gt; gt;gt; gt; and inserted a sheet named quot;Lookupquot; that I am trying to pull
gt; gt; gt;gt; gt; the data from.
gt; gt; gt;gt; gt; Within that 'lookupquot; sheet, I have various ranges named. So,
gt; gt; gt;gt; gt; for example,
gt; gt; gt;gt; gt; column A is CLIN, column B is WBS and column C is Total amt
gt; gt; gt;gt; gt; (see below).
gt; gt; gt;gt; gt; CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20
gt; gt; gt;gt; gt; and so on. I
gt; gt; gt;gt; gt; have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What
gt; gt; gt;gt; gt; I'm trying to do is
gt; gt; gt;gt; gt; by using those named ranges, lookup the WBS number and drop
gt; gt; gt;gt; gt; the Total amt
gt; gt; gt;gt; gt; into my other 200 sheets. I am using column index # 2.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; CLIN WBS TOTAL AMT
gt; gt; gt;gt; gt; 0001A 01.01.03 472
gt; gt; gt;gt; gt; 0001A 01.01.04 5,364
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; To confuse things further, this function worked at one time.
gt; gt; gt;gt; gt; This workbook
gt; gt; gt;gt; gt; that I have had a quot;Lookupquot; sheet in it, say 2 weeks ago.
gt; gt; gt;gt; gt; The problem is that
gt; gt; gt;gt; gt; my Total amounts have changed, so what I tried to do was
gt; gt; gt;gt; gt; create a NEW
gt; gt; gt;gt; gt; quot;Lookupquot; sheet, insert it into the workbook and delete the
gt; gt; gt;gt; gt; OLD quot;Lookupquot;
gt; gt; gt;gt; gt; sheet. From what I can tell the new quot;lookupquot; is set up just
gt; gt; gt;gt; gt; like the old
gt; gt; gt;gt; gt; quot;lookupquot;, but for some reason the VLOOKUP formula now
gt; gt; gt;gt; gt; returns the REF# error
gt; gt; gt;gt; gt; in the Table Array field of the formula box. Confused yet?!?
gt; gt; gt;gt; gt; :-)
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; quot;bpeltzerquot; wrote:
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; gt; What is the range, and what is the column index number
gt; gt; gt;gt; gt; gt; (3rd argument) in your
gt; gt; gt;gt; gt; gt; vlookup? #REF can come up if the column index number
gt; gt; gt;gt; gt; gt; exceeds the number of
gt; gt; gt;gt; gt; gt; columns in the table array.
gt; gt; gt;gt; gt; gt;
gt; gt; gt;gt; gt; gt; quot;Amyquot; wrote:
gt; gt; gt;gt; gt; gt;
gt; gt; gt;gt; gt; gt; gt; I am trying to use the VLOOKUP fcn in my spreadsheet and
gt; gt; gt;gt; gt; gt; gt; am getting the REF#
gt; gt; gt;gt; gt; gt; gt; error in the Table Array. I checked my named ranged,
gt; gt; gt;gt; gt; gt; gt; and they appear to be
gt; gt; gt;gt; gt; gt; gt; okay. Any other suggestions of what may be wrong?
gt; gt;
gt; gt;
gt; gt;

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