SOLVED

error msg when using IF( .when the colum reference is text

Copper Contributor

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

 

 

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.

best response confirmed by redi_to_learn (Copper Contributor)
Solution

@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).

1 best response

Accepted Solutions
best response confirmed by redi_to_learn (Copper Contributor)
Solution

@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).

View solution in original post