Quote:
|
Originally Posted by drathzel I think its prob right but if you are diong it for a whole column and you want paid or p in it?
EG
£
22 Paid
33 Paid
44 Paid
55
66
77
88
99 paid
286 owed |
As everyone says, but a couple more things that may help:
Select a column of cells {eg C5:C20}, then type in the formula in the first of these {eg =if(B5="P",A5,0)} and hold <ctrl> when pressing return: the formulas will copy themselves into all the selected cells as if the 'drag copy' function is used {ie C10 would contain the formula =if(B10="P",A10,0)}
If a cell uses a formula to be blank instead of containing a '0' value {eg =if(B10="P",A10,"")} and you have another formula in the row {eg F10} like =C10*1.175, then you will get an error. To resolve this, simply use =SUM(C10)*1.175 and it will ignore any text values.
To pretty things up, you could use "Conditional Formatting" and use roughly the same formula to highlight all those still to be paid {eg select rows5-20 and go to 'conditional formatting'. Insert a formula =if($B10="P",FALSE,TRUE) then select the format (eg a pink background) and hit OK.}
You can use 'names' to hold formulas or constants; you could name a cell (eg VAT) and any time the name is mentioned, the value in that cell is used (eg 1.175) so that you could have a formula of =C1*VAT and it would be the same as C1*1.175.
In a similar way, you could be on cell C5 and insert a name; call it "Paid" and in the bit that says "=Sheet1!$C$5" you simply insert the formula above "=if($B5="P",$A5,0)" {note- lock the column so that the formula can be used anywhere in the row}
Now in C5 you just say "=Paid" and the conditional formula could be =if(paid>0,FALSE,TRUE)
{If you deve deeper into this and are using formulas spanning pages and workbooks, there is a limit that requires a work-around; formulas entered in this way normally only work for the sheet they are entered on. If you need the work-around, PM me.}