Need a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2415108%22%20slang%3D%22en-US%22%3ENeed%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415108%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20your%20help%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Excel%20file%20which%20is%20daily%20debitor%20report.%20This%20file%20has%20two%20sheets%20.%20Sheet%201%20and%20Sheet%202.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20want%20the%20data%20in%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Esheet%202%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E(here%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Esales%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Ebank%3C%2FSTRONG%3E)%20to%20be%20added%20to%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esheet%201%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eautomatically.%20One%20of%20my%20friend%20applied%20a%20VBA%20code%20for%20this%20and%20it%20works%20(%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Eattached%20file%20possess%20this%20VBA%20code%3C%2FSTRONG%3E).%20However%2C%20I%20want%20it%20with%20formula%20(not%20with%20Pivot%20table).%20I%20hope%20anyone%20can%20help%20me%20with%20this%20situation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2415108%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2415312%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1057535%22%20target%3D%22_blank%22%3E%40ismat1950%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20Sheet%201%20in%20range%20I14%3AJ19%20I've%20applied%20an%20array%20(CSE)%20formula%2C%2C%2C%20and%20you%20need%20to%20copy%20it%20for%20other%20dates%2C%2C%20but%20be%20careful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMy%20observation%20%26amp%3B%20suggestions%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20used%20VBA%20macro%20method%20is%20the%20best%20suitable%20for%20the%20job%20since%2C%2C%20for%20every%20month%20you%20have%20at%20least%2030%20data%20ranges%20need%20to%20work%20with%20an%20array%20(CSE)%20formula%2C%20as%20well%20as%20you%20need%20to%20protect%20them.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EMy%20suggestion%20is%20keep%20working%20with%20the%20VBA%20macro%20method.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2418636%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2418636%22%20slang%3D%22en-US%22%3EAt%20my%20end%20not%20getting%20any%20error%20%2C%2C%20I've%20tried%20so%20many%20new%20data%2C%2C%20plzz%20check%20it%20properly%20and%20remember%20the%20formula%20I've%20suggested%20is%20am%20Array%20(CSE)%20formula%20%26amp%3B%20need%20to%20finish%20with%20Ctrl%2BShift%2BEnter%20simultaneously%20!!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 

Hello,

 

I need your help guys,

 

I have a Excel file which is daily debitor report. This file has two sheets . Sheet 1 and Sheet 2. 

So, I want the data in the sheet 2 (here sales and bank) to be added to sheet 1 automatically. One of my friend applied a VBA code for this and it works ( attached file possess this VBA code). However, I want it with formula (not with Pivot table). I hope anyone can help me with this situation. 

 

5 Replies

@ismat1950 

 

Check the Sheet 1 in range I14:J19 I've applied an array (CSE) formula,,, and you need to copy it for other dates,, but be careful.

 

My observation & suggestions: 

 

  • The used VBA macro method is the best suitable for the job since,, for every month you have at least 30 data ranges need to work with an array (CSE) formula, as well as you need to protect them.

 

  • My suggestion is keep working with the VBA macro method.
Mr. Rajesh-S thanks for your help.
However there is still a problem. Remianing balance values is looking like this #VALUE! when add new data. Any solution for this?
coming to VBA code it is not working properly (
At my end not getting any error ,, I've tried so many new data,, plzz check it properly and remember the formula I've suggested is am Array (CSE) formula & need to finish with Ctrl+Shift+Enter simultaneously !!
I mean that remaining balance is only appers in cells where there is a transaction (bank and sales).
In other cells where there is no entries is looking as #VALUE!. I need they all appears regardless of transaction made or not.
Plzz reload the File !!