Hi Philippe
glad its solved .. IMHO the evaluate formula tool is one of the most useful
things in excel.
Cheers
JulieD
"Philippe L. Balmanno" wrote in message
news:XUBkd.43659$SW3.18011@fed1read01...
Yes, thanks.
Using the evaluate formula tool, It complained that there was a formula
that could change when the sheet was recalculated. I looked at the column
headings as I knew I used a formula to get those values and determined
that the problem was that my 1.5% (column heading) was the problem. My
column heading (1.5%) is a product of a formula and the offset match
formula prefers the actual value rather than the product of a formula.
Once I fixed this the formula worked. I will be trying out Max's
suggestion although rounding isn't the problem as the product is a solid
value of 1.5% but this may lead to a possible way to use a formula's
product in the match.
"JulieD" wrote in message
...
Hi
so is the problem solved now?
Cheers
JulieD
"Philippe L. Balmanno" wrote in message
news:UOAkd.43618$SW3.10629@fed1read01...
In this case the array starts at A5 (5+54=59) therefore A59 is the row I
need.
The error is in the column value. It was a product of a formula
=1.25%+.25%=1.5%. The Offset Match formula doesn't like looking for the
product of a formula but rather a value because it determined that this
product can change if another factor is changed. Once I entered 1.5% in
the cell my Offset formula worked.
"duane" wrote in message
...
=OFFSET('UAS Compound Factor'!A5,MATCH(CERS!L53,'UAS Compound
Factor'!A6:A105,0),MATCH(CERS!L54,'UAS Compound Factor'!B5:AL5,0))
isn't this formula finding the value in a cell defined as
1) start at a5
2) find the position of the exact l53 value in a6:a105 and go down that
number of rows
3) find the position of the exact l54 value in b5:al5 and go up that
number of columns
presumably the value of l54 exists in a6:a105 and l53 exists in
b5:al5?
Is a coincidence that you expect the value of 54 to be in row 59 (54
than 5)?
--
duane
------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread:
http://www.excelforum.com/showthread...hreadid=277267