Lookup and Use a Formula

[TKS_Mark]

I want to lookup a formula in one sheet that I have stored as text and use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just multiplying [a]
times and giving the result. How can I make a looked up formula work in
its new location? (I'm using tables, hence the [bracketed] references.)

 

[CLR]

Dont store the VLOOKUPs as TEXT, store them as regular formulas...thenjust
reference the value they return and don't move the formula itself at all......

Vaya con Dios,
Chuck, CABGx3

 

[TKS_Mark]

The references the formulas are needing are from the sheet I'm doing the
lookup from. In other words, I store the formula [A]* on sheet in a row
with the duct definition. That reference sheet doesn't have the duct size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this duct
is [A]*. So I need a column labeled "area" to automatically change it's
formula based on my having selected "duct". I will have many different sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula. But
the cell displays [A]* (or =[A]*, depending on how I do it) instead of
the results. (Like if A is 12 and B is 10, then the result should be 120.

Dont store the VLOOKUPs as TEXT, store them as regular formulas...thenjust
reference the value they return and don't move the formula itself at all......

Vaya con Dios,
Chuck, CABGx3

I want to lookup a formula in one sheet that I have stored as text and use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just multiplying [a]
times and giving the result. How can I make a looked up formula work in
its new location? (I'm using tables, hence the [bracketed] references.)

 

[T. Valko]

If your "formulas" are in the format:

12*10
10*10
2*4
5*7

=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,255)

Another possibility:

Assume your "formulas" are in column A and you want the calculated value in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

=Calc

--
Biff
Microsoft Excel MVP

The references the formulas are needing are from the sheet I'm doing the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula. But
the cell displays [A]* (or =[A]*, depending on how I do it) instead
of
the results. (Like if A is 12 and B is 10, then the result should be 120.

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......

Vaya con Dios,
Chuck, CABGx3

I want to lookup a formula in one sheet that I have stored as text and
use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[TKS_Mark]

The first suggestion works when there are numbers instead of cell references.
But as soon as I change to cell references, it won't work. Copy and paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel tries to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations around
the cell references. I tried the second suggestion using two columns and I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "". How
can I strip these ""s?

If your "formulas" are in the format:

12*10
10*10
2*4
5*7

=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,255)

Another possibility:

Assume your "formulas" are in column A and you want the calculated value in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

=Calc

--
Biff
Microsoft Excel MVP

The references the formulas are needing are from the sheet I'm doing the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula. But
the cell displays [A]* (or =[A]*, depending on how I do it) instead
of
the results. (Like if A is 12 and B is 10, then the result should be 120.

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......

Vaya con Dios,
Chuck, CABGx3



:

I want to lookup a formula in one sheet that I have stored as text and
use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[iliace]

Perhaps you can use this:

=INDIRECT(LEFT(C1,FIND("*",C1)-1))*INDIRECT(MID(C1,FIND("*",C1)+1,255))

The first suggestion works when there are numbers instead of cell references.
But as soon as I change to cell references, it won't work. Copy and paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel tries to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations around
the cell references. I tried the second suggestion using two columns and I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "". How
can I strip these ""s?

If your "formulas" are in the format:


Another possibility:

Assume your "formulas" are in column A and you want the calculated value in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

The references the formulas are needing are from the sheet I'm doing the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct size
though. I only make this definition once.
On another sheet, I will lookup "duct" and know that the area of this duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.
I think I have the hard part done. It gives me the correct formula. But
the cell displays [A]* (or =[A]*, depending on how I do it) instead
of
the results. (Like if A is 12 and B is 10, then the result should be 120.
:
Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......
Vaya con Dios,
Chuck, CABGx3
:
I want to lookup a formula in one sheet that I have stored as text and
use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I
can't
get the formula to work in my table.
Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[T. Valko]

I've got the formula down to ="[A]*",

Literally? Your formula has the brackets [ ] ?

I assume A and B are really numbers?

So, your formulas actually look like this including the quotes?

="[10]*[10]"

If that is a cell formula then the result is: [10]*[10]

If so, why not get rid of everything except:

10*10

Why do you need "formulas" ?

I think you're making this more complicated than need be.


--
Biff
Microsoft Excel MVP

The first suggestion works when there are numbers instead of cell
references.
But as soon as I change to cell references, it won't work. Copy and paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel tries
to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations
around
the cell references. I tried the second suggestion using two columns and
I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "".
How
can I strip these ""s?

If your "formulas" are in the format:

12*10
10*10
2*4
5*7

=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,255)

Another possibility:

Assume your "formulas" are in column A and you want the calculated value
in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

=Calc

--
Biff
Microsoft Excel MVP

The references the formulas are needing are from the sheet I'm doing
the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct
size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this
duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula.
But
the cell displays [A]* (or =[A]*, depending on how I do it)
instead
of
the results. (Like if A is 12 and B is 10, then the result should be
120.

:

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......

Vaya con Dios,
Chuck, CABGx3



:

I want to lookup a formula in one sheet that I have stored as text
and
use it
on another sheet. I know how to do the lookup and am able to bring
the
correct formula based on other items in my =vlookup statement. But
I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[TKS_Mark]

T. Valko: We will have a hundred pieces of duct. Some of them will be A=4
and B=2. Others will be 5 and 6, etc, etc. Then we'll have dozens of tanks
with another formulae to calculate them. There are drawings for each unique
piece so engineers will know what A, B, C, etc, means for each piece. I keep
the formula in just one location so estimators won't make mistakes copying.
There will be 5000 items estimated, but the formulas come from a sheet that
is only ~100 rows long. The brackets [] are because it is a new table
reference in Excel 2007. But using the old way doesn't work either. I get
the formula down to "A1*B1" and can't eliminate the quotes.

I've got the formula down to ="[A]*",

Literally? Your formula has the brackets [ ] ?

I assume A and B are really numbers?

So, your formulas actually look like this including the quotes?

="[10]*[10]"

If that is a cell formula then the result is: [10]*[10]

If so, why not get rid of everything except:

10*10

Why do you need "formulas" ?

I think you're making this more complicated than need be.


--
Biff
Microsoft Excel MVP

The first suggestion works when there are numbers instead of cell
references.
But as soon as I change to cell references, it won't work. Copy and paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel tries
to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations
around
the cell references. I tried the second suggestion using two columns and
I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "".
How
can I strip these ""s?

If your "formulas" are in the format:

12*10
10*10
2*4
5*7

=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,255)

Another possibility:

Assume your "formulas" are in column A and you want the calculated value
in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

=Calc

--
Biff
Microsoft Excel MVP


The references the formulas are needing are from the sheet I'm doing
the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct
size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this
duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula.
But
the cell displays [A]* (or =[A]*, depending on how I do it)
instead
of
the results. (Like if A is 12 and B is 10, then the result should be
120.

:

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......

Vaya con Dios,
Chuck, CABGx3



:

I want to lookup a formula in one sheet that I have stored as text
and
use it
on another sheet. I know how to do the lookup and am able to bring
the
correct formula based on other items in my =vlookup statement. But
I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[TKS_Mark]

Iliace: That's actually much closer than I was able to go. If I use the
standard cell references (A1, B1...) instead of table references ([column1],
[column2]...), this works great. I wonder if there's a way to make it work
with tables too. Apparently, not too many people have started using tables
(home ribbon, styles, format as table, check "my table has headers").

Perhaps you can use this:

=INDIRECT(LEFT(C1,FIND("*",C1)-1))*INDIRECT(MID(C1,FIND("*",C1)+1,255))

The first suggestion works when there are numbers instead of cell references.
But as soon as I change to cell references, it won't work. Copy and paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel tries to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations around
the cell references. I tried the second suggestion using two columns and I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "". How
can I strip these ""s?

If your "formulas" are in the format:


Another possibility:

Assume your "formulas" are in column A and you want the calculated value in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

The references the formulas are needing are from the sheet I'm doing the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula. But
the cell displays [A]* (or =[A]*, depending on how I do it) instead
of
the results. (Like if A is 12 and B is 10, then the result should be 120.

"CLR" wrote:

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......

Vaya con Dios,
Chuck, CABGx3

"TKS_Mark" wrote:

I want to lookup a formula in one sheet that I have stored as text and
use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[TKS_Mark]

Iliace: Also, many of my formulas are too complicated to disect them like
the formula below does. Ex: =IF(F242>0,(F242+G242)*2*H242,PI()*G242*H242).
I really need to be able to plop the complete formula into a cell and re-use
automatically.

Perhaps you can use this:

=INDIRECT(LEFT(C1,FIND("*",C1)-1))*INDIRECT(MID(C1,FIND("*",C1)+1,255))

The first suggestion works when there are numbers instead of cell references.
But as soon as I change to cell references, it won't work. Copy and paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel tries to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations around
the cell references. I tried the second suggestion using two columns and I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "". How
can I strip these ""s?

If your "formulas" are in the format:


Another possibility:

Assume your "formulas" are in column A and you want the calculated value in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

The references the formulas are needing are from the sheet I'm doing the
lookup from. In other words, I store the formula [A]* on sheet in a
row
with the duct definition. That reference sheet doesn't have the duct size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of this duct
is [A]*. So I need a column labeled "area" to automatically change
it's
formula based on my having selected "duct". I will have many different
sized
ducts and beams, etc, but I only want to make one formula that comes up
automatically.

I think I have the hard part done. It gives me the correct formula. But
the cell displays [A]* (or =[A]*, depending on how I do it) instead
of
the results. (Like if A is 12 and B is 10, then the result should be 120.

"CLR" wrote:

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself at
all......

Vaya con Dios,
Chuck, CABGx3

"TKS_Mark" wrote:

I want to lookup a formula in one sheet that I have stored as text and
use it
on another sheet. I know how to do the lookup and am able to bring the
correct formula based on other items in my =vlookup statement. But I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[T. Valko]

Ok, now I understand what the brackets mean. This is an exclusive "feature"
to Excel 2007.

Unfortunately, I don't use Excel 2007 so I probably can't be of any help.

--
Biff
Microsoft Excel MVP

T. Valko: We will have a hundred pieces of duct. Some of them will be
A=4
and B=2. Others will be 5 and 6, etc, etc. Then we'll have dozens of
tanks
with another formulae to calculate them. There are drawings for each
unique
piece so engineers will know what A, B, C, etc, means for each piece. I
keep
the formula in just one location so estimators won't make mistakes
copying.
There will be 5000 items estimated, but the formulas come from a sheet
that
is only ~100 rows long. The brackets [] are because it is a new table
reference in Excel 2007. But using the old way doesn't work either. I
get
the formula down to "A1*B1" and can't eliminate the quotes.

I've got the formula down to ="[A]*",

Literally? Your formula has the brackets [ ] ?

I assume A and B are really numbers?

So, your formulas actually look like this including the quotes?

="[10]*[10]"

If that is a cell formula then the result is: [10]*[10]

If so, why not get rid of everything except:

10*10

Why do you need "formulas" ?

I think you're making this more complicated than need be.


--
Biff
Microsoft Excel MVP

The first suggestion works when there are numbers instead of cell
references.
But as soon as I change to cell references, it won't work. Copy and
paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel
tries
to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations
around
the cell references. I tried the second suggestion using two columns
and
I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "".
How
can I strip these ""s?

:

If your "formulas" are in the format:

12*10
10*10
2*4
5*7

=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,255)

Another possibility:

Assume your "formulas" are in column A and you want the calculated
value
in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

=Calc

--
Biff
Microsoft Excel MVP


The references the formulas are needing are from the sheet I'm doing
the
lookup from. In other words, I store the formula [A]* on sheet
in a
row
with the duct definition. That reference sheet doesn't have the
duct
size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of
this
duct
is [A]*. So I need a column labeled "area" to automatically
change
it's
formula based on my having selected "duct". I will have many
different
sized
ducts and beams, etc, but I only want to make one formula that comes
up
automatically.

I think I have the hard part done. It gives me the correct formula.
But
the cell displays [A]* (or =[A]*, depending on how I do it)
instead
of
the results. (Like if A is 12 and B is 10, then the result should
be
120.

:

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself
at
all......

Vaya con Dios,
Chuck, CABGx3



:

I want to lookup a formula in one sheet that I have stored as
text
and
use it
on another sheet. I know how to do the lookup and am able to
bring
the
correct formula based on other items in my =vlookup statement.
But
I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up
formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)

 

[RagDyer]

So, to see if I understand what you're saying, A, B, and C would/could/might
stand for length, width, and depth, where the dimensions vary according to
the size and style of the item.
If your pricing is based on cost/sq.ft., you'd use say, an A*B formula,
And A*B*C for the cu.ft. cost of something.

Do you have these A, B, C, ...etc. values as fields (columns), and the
various items filling the rows?

Are your formulas based around looking up an item in a row, and then
calculating the cost/price by using a pre-set (by formula) combination of
the columns values for that particular row.

If this be the case, there are ways to use regular, "resident in older
version functions" to accomplish this.

If you would care to describe in more detail, how your list is set up, and
include some explicit examples of your calculation methods and their
results, I'm sure there are many folks here who can suggest alternative
solutions.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

T. Valko: We will have a hundred pieces of duct. Some of them will be
A=4
and B=2. Others will be 5 and 6, etc, etc. Then we'll have dozens of
tanks
with another formulae to calculate them. There are drawings for each
unique
piece so engineers will know what A, B, C, etc, means for each piece. I
keep
the formula in just one location so estimators won't make mistakes
copying.
There will be 5000 items estimated, but the formulas come from a sheet
that
is only ~100 rows long. The brackets [] are because it is a new table
reference in Excel 2007. But using the old way doesn't work either. I
get
the formula down to "A1*B1" and can't eliminate the quotes.

I've got the formula down to ="[A]*",

Literally? Your formula has the brackets [ ] ?

I assume A and B are really numbers?

So, your formulas actually look like this including the quotes?

="[10]*[10]"

If that is a cell formula then the result is: [10]*[10]

If so, why not get rid of everything except:

10*10

Why do you need "formulas" ?

I think you're making this more complicated than need be.


--
Biff
Microsoft Excel MVP

The first suggestion works when there are numbers instead of cell
references.
But as soon as I change to cell references, it won't work. Copy and
paste
the

2 4 A1*B1
10*10
2*4
5*7
=LEFT(C1,FIND("*",C1)-1)*MID(C1,FIND("*",C1)+1,255)

Then click on the evaluate formula command. You can watch as Excel
tries
to
multiply "A1"*"B1". For some reason, Excel keeps putting quotations
around
the cell references. I tried the second suggestion using two columns
and
I
had the same result.

I've got the formula down to ="[A]*", but I can't get rid of the "".
How
can I strip these ""s?

:

If your "formulas" are in the format:

12*10
10*10
2*4
5*7

=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,255)

Another possibility:

Assume your "formulas" are in column A and you want the calculated
value
in
column B.

Create this named formula:
Insert>Name>Define
Name: Calc
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))
OK

Then, in column B enter this formula:

=Calc

--
Biff
Microsoft Excel MVP


The references the formulas are needing are from the sheet I'm doing
the
lookup from. In other words, I store the formula [A]* on sheet
in a
row
with the duct definition. That reference sheet doesn't have the
duct
size
though. I only make this definition once.

On another sheet, I will lookup "duct" and know that the area of
this
duct
is [A]*. So I need a column labeled "area" to automatically
change
it's
formula based on my having selected "duct". I will have many
different
sized
ducts and beams, etc, but I only want to make one formula that comes
up
automatically.

I think I have the hard part done. It gives me the correct formula.
But
the cell displays [A]* (or =[A]*, depending on how I do it)
instead
of
the results. (Like if A is 12 and B is 10, then the result should
be
120.

:

Dont store the VLOOKUPs as TEXT, store them as regular
formulas...thenjust
reference the value they return and don't move the formula itself
at
all......

Vaya con Dios,
Chuck, CABGx3



:

I want to lookup a formula in one sheet that I have stored as
text
and
use it
on another sheet. I know how to do the lookup and am able to
bring
the
correct formula based on other items in my =vlookup statement.
But
I
can't
get the formula to work in my table.

Ex: the result of my vlookup yields [a]* instead of just
multiplying [a]
times and giving the result. How can I make a looked up
formula
work in
its new location? (I'm using tables, hence the [bracketed]
references.)