Hi,

I need to able to copy a formula from a cell in another worksheet,
which I find by using a V lookup, and have it use that formula in a
cell......


Sheet A: (Value's View)

A B
1 ESP 0.25
2 ERT =OFFSET(THISCELL, 1, 0)
3 ERR 0.45

Sheet B: (Formula View)

A B
1 ESP =VLOOKUP(A1,Sheet A!$A$6:$I$311,3,FALSE)
2 ERT =VLOOKUP(A2,Sheet A!$A$6:$I$311,3,FALSE)
3 ESP =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
4 ESR =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
5 ERT =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)

Sheet B: (Value's View)

A B
1 ESP 0.25
2 ERT 0.25
3 ESP 0.25
4 ERR 0.45
5 ERT 0.45



So when column A (on sheet B) has ESP in it it gets the value from the
ESP row in sheet A, but if it has ERT in it it will take the formula
from sheet a and use it to get the value from the row above.

Any ideas? Hope I have explained myself properly.

Thanks in advance for your help.

Phil

When a formula references a cell it gets that cell's value. It has no
access to the formula in that cell.

I think you have to do something ugly like:

1. Modify the Sheet A table to have a code in column B when you want the
one-up to happen. So say you put "*" in cell B2 (on Sheet A)


2. Then on Sheet B cell B2 (and copied down):

=IF(VLOOKUP(A2,Sheet A!$A$1:$I$311,2,FALSE)="*",B1,VLOOKUP(A2,Sheet
A!$A$1:$I$311,2,FALSE))

(I modified the lookup to reference a table starting at A1, and looking at
the second column (B) as your example shows).

Just as an aside. It is generally inefficient to reference a larger table
than you need. Your formula references a table of 9 columns:

=VLOOKUP(A2,Sheet A!$A$1:$I$311,3,FALSE)

even though it is hard wired to return the third. So it's better to do
this:

=VLOOKUP(A2,Sheet A!$A$1:$C$311,3,FALSE)

--
Jim
wrote in message
ups.com...
| Hi,
|
| I need to able to copy a formula from a cell in another worksheet,
| which I find by using a V lookup, and have it use that formula in a
| cell......
|
|
| Sheet A: (Value's View)
|
| A B
| 1 ESP 0.25
| 2 ERT =OFFSET(THISCELL, 1, 0)
| 3 ERR 0.45
|
| Sheet B: (Formula View)
|
| A B
| 1 ESP =VLOOKUP(A1,Sheet A!$A$6:$I$311,3,FALSE)
| 2 ERT =VLOOKUP(A2,Sheet A!$A$6:$I$311,3,FALSE)
| 3 ESP =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
| 4 ESR =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
| 5 ERT =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
|
| Sheet B: (Value's View)
|
| A B
| 1 ESP 0.25
| 2 ERT 0.25
| 3 ESP 0.25
| 4 ERR 0.45
| 5 ERT 0.45
|
|
|
| So when column A (on sheet B) has ESP in it it gets the value from the
| ESP row in sheet A, but if it has ERT in it it will take the formula
| from sheet a and use it to get the value from the row above.
|
| Any ideas? Hope I have explained myself properly.
|
| Thanks in advance for your help.
|
| Phil
|

Thanks for the help Jim.

Unfortunatlly that wont work for what I need it to do, as I simplified
the example a little. I'm doing this for a friend of mine who is in the
building business and its used to work out cost for drainage.

Anyway....The problem is that there are more of these codes and the
number of cells which need to be OFFSET differs based on the code. Some
of them I just need to get the value from 1 row above, sometime I need
the value from 4 rows above.

Never mind I think I will have to tell him that it can't be done.

Thanks anyway.

Phil



Jim Rech wrote:
When a formula references a cell it gets that cell's value. It has no
access to the formula in that cell.

I think you have to do something ugly like:

1. Modify the Sheet A table to have a code in column B when you want the
one-up to happen. So say you put "*" in cell B2 (on Sheet A)


2. Then on Sheet B cell B2 (and copied down):

=IF(VLOOKUP(A2,Sheet A!$A$1:$I$311,2,FALSE)="*",B1,VLOOKUP(A2,Sheet
A!$A$1:$I$311,2,FALSE))

(I modified the lookup to reference a table starting at A1, and looking at
the second column (B) as your example shows).

Just as an aside. It is generally inefficient to reference a larger table
than you need. Your formula references a table of 9 columns:

=VLOOKUP(A2,Sheet A!$A$1:$I$311,3,FALSE)

even though it is hard wired to return the third. So it's better to do
this:

=VLOOKUP(A2,Sheet A!$A$1:$C$311,3,FALSE)

--
Jim
wrote in message
ups.com...
| Hi,
|
| I need to able to copy a formula from a cell in another worksheet,
| which I find by using a V lookup, and have it use that formula in a
| cell......
|
|
| Sheet A: (Value's View)
|
| A B
| 1 ESP 0.25
| 2 ERT =OFFSET(THISCELL, 1, 0)
| 3 ERR 0.45
|
| Sheet B: (Formula View)
|
| A B
| 1 ESP =VLOOKUP(A1,Sheet A!$A$6:$I$311,3,FALSE)
| 2 ERT =VLOOKUP(A2,Sheet A!$A$6:$I$311,3,FALSE)
| 3 ESP =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
| 4 ESR =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
| 5 ERT =VLOOKUP(A3,Sheet A!$A$6:$I$311,3,FALSE)
|
| Sheet B: (Value's View)
|
| A B
| 1 ESP 0.25
| 2 ERT 0.25
| 3 ESP 0.25
| 4 ERR 0.45
| 5 ERT 0.45
|
|
|
| So when column A (on sheet B) has ESP in it it gets the value from the
| ESP row in sheet A, but if it has ERT in it it will take the formula
| from sheet a and use it to get the value from the row above.
|
| Any ideas? Hope I have explained myself properly.
|
| Thanks in advance for your help.
|
| Phil
|