PDA

View Full Version : Formula Error



Pete
06-15-2008, 10:42 AM
Hi

Need to amend of re point the following formula to the right cell:
=SUM(OFFSET('CashFlow Q4'!$F147, QUOTIENT(160-6, MATCH("DEAL MARGIN",'CashFlow Q4'!$C:$C, 0)-6), (INT(RIGHT($F$5, 2))-INT(RIGHT('CashFlow Q4'!$F$5)))*4, 1, 4))

if you go to worksheet "CashFlow Yearly" and look at column F row 6 the QUOTIENT(160-6 of the formula should correspond with 'CashFlow Q4'!$F147----------but it is out for all the columns and rows...........

below is the vba part of the above formula


'Loop through each column in current row
curCol = 6
While (.Cells(5, curCol).Value <> "")

'Assign formula
.Cells(startrow + 1, curCol).Formula = _
"=SUM(" & _
"OFFSET(" & _
"'CashFlow Q4'!$F" & startrow + 1 & ", " & _
"QUOTIENT(" & startrow + 1 & "-6, MATCH(""DEAL MARGIN"",'CashFlow Q4'!$C:$C, 0)-6), " & _
"(INT(RIGHT(" & .Cells(5, curCol).Address & ", 2))-INT(RIGHT('CashFlow Q4'!$F$5)))*4, " & _
"1, " & _
"4" & _
")" & _
")"

curCol = curCol + 1
Wend

Simon Lloyd
06-15-2008, 11:09 AM
Peter, you haven't included a workbook for us to look at CashFlow Yearly or any other sheet!, which cell should it point to?

Pete
06-15-2008, 11:12 AM
ok apologies worksheet attached in zip form

Pete
06-15-2008, 11:18 AM
if you go to worksheet "CashFlow Yearly" and look at column F row 147 the QUOTIENT(160-6 of the formula should correspond with 'CashFlow Q4'!$F147----------but it is out for all the columns and rows...........scroll up from row 147.....and you'll see.....

This goes out of sink once i have deleted and re add back the customer.......i was under the impression that the 'CashFlow Q4'!$F147, QUOTIENT(160-6 part of the formula should work together i.e. change accordingly