Forum Discussion
error msg when using IF( .when the colum reference is text
not sure how to add a screen shot of a excel page yet so describing my problem
col.D has a currency value as a debit Col.E has a code preferably [TR] col.Q requires the value of -D when it occurs.
col.N has a currency value as a credit, Col.O has the TR code and col.Q requires the value of +N
as a' tr' may occur only 4 to 6 times in 50 rows of data
at the bottom is the sum each column so that to get a balance col.Q should be zero.
there are several cross checks for variance in the data entry in the work book which has 12 month pages and a summary page. TR could be a numeric but as it indicates a transfer of funds from one account to another and is neither expense nor income it is easier for the data entry person to understand
The formula i have tried which gives 'error' is in col.Q
| =IF(E2=>"TR",(D2),(0))) |
or this in a different column
| =IF(E2=>"TR",(Q2=e2),(0))) |
Seems I can't tell it to place a value in a column other than the one i have the formula
your help in correcting this is appreciated
Riny_van_Eekelen thank you for the reply, i will go through the workbook you recommenced shortly, sorry if i was not more explicit with the problem, which as my second post tried to state i found one solution in the formulae. what i was trying to achieve was have the amount in column D to use the trigger in column G to make the amount in Column D auto place the amount in Column AI.
i am not sure i can create an array or a table as the number of rows in each month will vary depending on the number of transactions each month (may be 15 up to 60 rows).
3 Replies
having spent several hours on this i have one solution used this function in column J
=IF(d2="tr",C2." ") which, when copied down picks up all debit amounts showing them in red as negative currency
appears i can not have different col. for the income within the one formula
so created col.K with function -> IF(H2="tr",g2," ")
with copy down it also picks all ' tr' references then it just means total of J +total of K = 0 to give me a balanced answer
would have preferred to do this in one column but don't seem to be able to get AND or OR to work with the different(debit / credit) column's.
if anyone has a better solution i would love to hear it
i don't seem to be able to copy anything from this cloud based excel ( some sync error), can't take a screen shot either.
redi_to_learn Perhaps you'll find the attached workbook helpful. Have tried to follow the logic from your latest post, though, I may have misunderstood.
Riny_van_Eekelen thank you for the reply, i will go through the workbook you recommenced shortly, sorry if i was not more explicit with the problem, which as my second post tried to state i found one solution in the formulae. what i was trying to achieve was have the amount in column D to use the trigger in column G to make the amount in Column D auto place the amount in Column AI.
i am not sure i can create an array or a table as the number of rows in each month will vary depending on the number of transactions each month (may be 15 up to 60 rows).
