SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1689829%22%20slang%3D%22en-US%22%3Eerror%20msg%20when%20using%20IF(%20.when%20the%20colum%20reference%20is%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1689829%22%20slang%3D%22en-US%22%3E%3CP%3Enot%20sure%20how%20to%20add%20a%20screen%20shot%20of%20a%20excel%20page%20yet%20so%20describing%20my%20problem%3C%2FP%3E%3CP%3Ecol.D%20has%20a%20currency%20value%20as%20a%20%3CU%3Edebit%3C%2FU%3E%20Col.E%20has%20a%20code%20preferably%20%5BTR%5D%26nbsp%3B%26nbsp%3B%20col.Q%20requires%20the%20value%20of%20-D%20when%20it%20occurs.%3C%2FP%3E%3CP%3Ecol.N%20has%20a%20currency%20value%20as%20a%20%3CU%3Ecredit%2C%20%3C%2FU%3ECol.O%20has%20the%20TR%20code%20and%20col.Q%20requires%20the%20value%20of%20%2BN%3C%2FP%3E%3CP%3E%26nbsp%3Bas%20a'%20tr'%20may%20occur%20only%204%26nbsp%3B%20to%206%20times%20in%2050%20rows%20of%20data%3C%2FP%3E%3CP%3Eat%20the%20bottom%20is%20the%20sum%20each%20column%20so%20that%20to%20get%20a%20balance%20col.Q%20should%20be%20zero.%3C%2FP%3E%3CP%3Ethere%20are%20several%20cross%20checks%20for%20variance%20in%20the%20data%20entry%20in%20the%20work%20book%20which%20has%2012%20month%20pages%20and%20a%20summary%20page.%20TR%20could%20be%20a%20numeric%20but%20as%20it%20indicates%20a%20transfer%20of%20funds%20from%20one%20account%20to%20another%20and%20is%20neither%20expense%26nbsp%3B%20nor%20income%20it%20is%20easier%26nbsp%3B%20for%20the%20data%20entry%20person%20to%20understand%3C%2FP%3E%3CP%3EThe%20formula%20i%20have%20tried%20which%20gives%20'error'%20is%20in%20col.Q%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DIF(E2%3D%26gt%3B%22TR%22%2C(D2)%2C(0)))%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3Bor%20this%20in%20a%20different%20column%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DIF(E2%3D%26gt%3B%22TR%22%2C(Q2%3De2)%2C(0)))%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ESeems%20I%20can't%20tell%20it%20to%20place%20a%20value%20in%20a%20column%20other%20than%20the%20one%20i%20have%20the%20formula%3C%2FP%3E%3CP%3Eyour%20help%20in%20correcting%20this%20is%20appreciated%3C%2FP%3E%3CPRE%3E%26nbsp%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1689829%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1689994%22%20slang%3D%22en-US%22%3ERe%3A%20error%20msg%20when%20using%20IF(%20.when%20the%20colum%20reference%20%20is%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1689994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F800711%22%20target%3D%22_blank%22%3E%40redi_to_learn%3C%2FA%3E%26nbsp%3BPerhaps%20you'll%20find%20the%20attached%20workbook%20helpful.%20Have%20tried%20to%20follow%20the%20logic%20from%20your%20latest%20post%2C%20though%2C%20I%20may%20have%20misunderstood.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1689974%22%20slang%3D%22en-US%22%3ERe%3A%20error%20msg%20when%20using%20IF(%20.when%20the%20colum%20reference%20%20is%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1689974%22%20slang%3D%22en-US%22%3E%3CP%3Ehaving%20spent%20several%20hours%20on%20this%20i%20have%20one%20solution%20used%20this%20function%20in%20%3CU%3Ecolumn%20J%3C%2FU%3E%3C%2FP%3E%3CP%3E%3DIF(d2%3D%22tr%22%2CC2.%22%20%22)%20which%2C%20when%20copied%20down%20picks%20up%20all%20debit%20amounts%20showing%26nbsp%3B%20them%20in%20red%20as%20negative%20currency%3C%2FP%3E%3CP%3Eappears%20i%20can%20not%20have%20different%20col.%20for%20the%20income%26nbsp%3B%20within%20the%20one%20formula%3C%2FP%3E%3CP%3E%26nbsp%3Bso%20created%20%3CU%3Ecol.K%3C%2FU%3E%20with%20function%20-%26gt%3B%26nbsp%3B%20IF(H2%3D%22tr%22%2Cg2%2C%22%20%22)%3C%2FP%3E%3CP%3Ewith%20copy%20down%20it%20also%20picks%20all%20'%20tr'%20references%20then%20it%20just%20means%20%3CU%3Etotal%20of%20J%20%2Btotal%20of%20K%20%3D%200%3C%2FU%3E%20to%20give%20me%20a%20balanced%20answer%3C%2FP%3E%3CP%3E%26nbsp%3Bwould%20have%20preferred%20to%20do%26nbsp%3B%20this%20in%20one%20column%20but%20don't%20seem%20to%20be%20able%20to%20get%20AND%20or%20OR%20to%20work%20with%20the%20different(debit%20%2F%20credit)%20column's.%3C%2FP%3E%3CP%3E%26nbsp%3Bif%20anyone%20has%20a%20better%20solution%20i%20would%20love%20to%20hear%20it%3C%2FP%3E%3CP%3E%26nbsp%3Bi%20don't%20seem%20to%20be%20able%20to%20copy%20anything%20from%20this%20cloud%20based%20excel%20(%20some%20sync%20error)%2C%20can't%20take%20a%20screen%20shot%20either.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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).