Glad you found the problem. You could use TOC!A:T for the reference if
you're unsure of the actual number of rows involved.

"David" wrote:

The mistake I was making was a simple one. The actual formula I was using
was =VLOOKUP(P8,TOC!A:A,5,0). Note I was telling the formula to only look in
column A, not the entire worksheet. I thought that since the value I was
looking for was in column A, I only needed to reference that column. When I
changed the formula to =VLOOKUP(P8,TOC!A2:T500,5,0) it worked. I overlooked
the range that you had entered in your reply.
Thanks for your time, I appreciate it.
David

"JLatham" wrote:

#REF is usually caused by setting up a formula to refer to a cell and then
deleting the row/column that the referenced cell is in, or moving it
elsewhere.

Use Excel Help and search for #REF and it will offer assistance in tracking
down the problem. Once a #REF is generated in a formula, all formulas
referring to that cell will also show #REF - you've got to track back to the
first place that #REF shows up.

Try this, in Sheet1, cell B2 type in "Hello from S1B2"

In Sheet2 you should have your formula in cell B2:
=IF(ISBLANK(Sheet1!B2)," ",Sheet1!B2)
and so "Hello from S1B2" should show up there also.

While still in Sheet2, enter "Test1" into A2, enter "Test2" into A3 (no
quote marks).

Move to Sheet3 then put
"Test1" into A2
then put this formula in any other cell on Sheet3
=VLOOKUP(A2,Sheet2!A2:B5,2,0)
Actually you could change the range init to Sheet2!A2:B3 and it should still
work.

What the VLOOKUP() forumla says is:
take the value in cell A2 (on Sheet3) and compare it to values in cells
A2:A3 on Sheet2 and if a match is found, then return the value in column B
(second column in the lookup table) in the same row where the match was found
in column A.

You should see "Hello from S1B2" on Sheet3 where you put the VLOOKUP()
formula.

"David" wrote:

Thanks for the reply. I entered the formula below and it did not work. The
cell value still shows #REF. I spent a lot of time yesterday trying to find
the answer using help, but to no avail. Could something else be interfereing
with the function of the formula? Perhaps I need to change the format of the
cells?

One item I noticed when I use the Insert Function command (fx) the line for
the Table Array shows #NUM in red at the end of the line. Normally I would
expect to see values appear in parenthesis at the end of the line. This
might be the problem, but I do not know how to fix this. Any ideas?
David

"JLatham" wrote:

Absolutely, just look at Help on VLOOKUP() for details. But there is no
reason you cannot do what you want.

The formula on Sheet3 would look something like this:
=VLOOKUP(A2,Sheet2!A2:B5,2,0)
this assumes that the value you want to match up with is on Sheet3 in cell
A2 ( I put that formula on my Sheet3 in cell A3 just for testing).

"David" wrote:

I would like to use the VLOOK formula to show the value of a cell that is
using a formula. For example. I have entered data on Sheet 1 in cell b2. On
Sheet 2 cell b2 is a formula =IF(ISBLANK(Sheet1!b2)," ",Sheet1!b2). Since
there is data on Sheet 1, data appears on Sheet 2. On Sheet 3 I want to use
a VLOOK command that will find the value on Sheet 2 in the A column and show
the value of Sheet 2 b2.(I am using ranges, I have just simplified this for
explanation purposes) I would like to avoid referencing Sheet 1 from Sheet 3
if possible. Can this be done?
David