Formula Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-1245369%22%20slang%3D%22en-US%22%3EFormula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245369%22%20slang%3D%22en-US%22%3E%3CP%3EI%20got%20a%20formula%20for%20the%20current%20balance%20on%20performance%20sheet%20and%20tried%20to%20use%20on%20money%20sheet%20and%20it%20does%20not%20work%20can%20someone%20help%20me%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1245369%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-1245391%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245391%22%20slang%3D%22en-US%22%3EYou%20need%20to%20attach%20your%20workbook%20sample%20for%20experts%20here%20to%20understand%20and%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246407%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246407%22%20slang%3D%22en-US%22%3E%3CP%3Ehere%20is%20my%20document%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246561%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590312%22%20target%3D%22_blank%22%3E%40Latrisa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%20I%20have%20seen%20your%20workbook%20but%20I%20don't%20understand%20the%20question%20clearly%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1584970714158.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178860iEBE60BDD818EF483%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1584970714158.png%22%20alt%3D%22Abiola1_0-1584970714158.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_1-1584970745207.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178862iA181738B87BF00FE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_1-1584970745207.png%22%20alt%3D%22Abiola1_1-1584970745207.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246580%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590312%22%20target%3D%22_blank%22%3E%40Latrisa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20is%20working%20but%20the%20obstacle%20is%20that%20you%20have%20a%20duplicate%20date%20-%20the%20issue%20is%20that%20your%20VLOOKUP%20is%20set%20to%20FALSE%20(0)%2C%20which%20returns%20the%20value%20pertaining%20to%20the%20first%20instance%20of%20the%20latest%20date.%26nbsp%3B%20By%20changing%20the%20FALSE%20(0)%20in%20the%20VLOOKUP%20to%20TRUE%20(1)%2C%20you%20will%20get%20the%20last%20instance%20of%20any%20duplicated%20latest%20date%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(MAX(%24A%3A%24A)%2CA%3AG%2C7%2C%3CSTRONG%3E1%3C%2FSTRONG%3E)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1247033%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1247033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590312%22%20target%3D%22_blank%22%3E%40Latrisa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20variant%2C%20assuming%20that%20you%20enter%20transactions%20chronologically.%20Then%20you%20would%20always%20want%20to%20show%20the%20content%20of%20the%20right-most%20bottom%20corner%20in%20the%20table%20as%20your%20current%20balance.%20This%20formula%20will%20do%20exactly%20that.%20No%20need%20to%20look%20up%20dates%20as%20the%20latest%20entry%20will%20always%20be%20at%20the%20bottom%20of%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(G%3AG%2CAGGREGATE(14%2C6%2C(ROW(G%3AG)*NOT(ISBLANK(G%3AG)))%2C1)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BIf%20my%20assumptions%20were%20wrong%2C%20kindly%20ignore%20my%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I got a formula for the current balance on performance sheet and tried to use on money sheet and it does not work can someone help me

5 Replies
Highlighted
You need to attach your workbook sample for experts here to understand and help
Highlighted

here is my document 

Highlighted

@Latrisa 

 

Hello, I have seen your workbook but I don't understand the question clearly

Abiola1_0-1584970714158.png

 

Abiola1_1-1584970745207.png

 

Highlighted

@Latrisa 

 

The formula is working but the obstacle is that you have a duplicate date - the issue is that your VLOOKUP is set to FALSE (0), which returns the value pertaining to the first instance of the latest date.  By changing the FALSE (0) in the VLOOKUP to TRUE (1), you will get the last instance of any duplicated latest date:

 

=VLOOKUP(MAX($A:$A),A:G,7,1)

Highlighted

@Latrisa 

As a variant, assuming that you enter transactions chronologically. Then you would always want to show the content of the right-most bottom corner in the table as your current balance. This formula will do exactly that. No need to look up dates as the latest entry will always be at the bottom of the table.

 

=INDEX(G:G,AGGREGATE(14,6,(ROW(G:G)*NOT(ISBLANK(G:G))),1),1)

 

 If my assumptions were wrong, kindly ignore my post.