Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
use vlook to show the value of a cell using a formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
use vlook to show the value of a cell using a formula
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
use vlook to show the value of a cell using a formula
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
use vlook to show the value of a cell using a formula
#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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
use vlook to show the value of a cell using a formula
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
use vlook to show the value of a cell using a formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to show zero if reference cell is zero? tricky! | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |