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 !
---------------------------------------------------------------------------
TKS_Mark said:
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.
T. Valko said:
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
TKS_Mark said:
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.)