Can Excel defy accounting programs in this issue?

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
Hello ,

If in my sheet named "Entries" i list all my transactions,what will be the formulas in sheet "Tom"or "John" under D12 &D19;E12&E19;F12&F19 whereas each time the word tom or john in the explanation column in"Entries" appears, the respective row data from under Date,Explanations and Debit values will be copied accordingly to each person's sheet name,if not it remains blank !
Actually,i have many persons sheets..

Could Excel solve such a problem or am i dreaming ? :confused:
Book1
CDEFGH
3
4DateExplanationsPlaceDebitvalue
5
622.02.2004paidtojohnasloan1100
722.02.2004cashtotompartofsalary123
822.02.2004
9Entries
10
11DateExplanationsDebitvalue
1222.02.2004cashtotompartofsalary23
13
14
15
16Tom
17
18DateExplanationsDebitvalue
1922.02.2004paidtojohnasloan100
20
21
22
23John
24
Sheet1
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

headtoadie

Board Regular
Joined
Aug 1, 2003
Messages
219
You could do it with a vlookup or sumif formula. However, you'll need to establish a firm naming convetion. I would suggest you put the persons name at the begining or at the end of the description. Then you can do a RIGHT (text,#) or LEFT (text, #) type of thing to match the name.

By far the easiest way to do it is to add an additional column that only contains the persons name, then use that as the basis for matching your vlookup or sumif formula.

HT
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gaftalik:

You can do this with EXCEL. I suggest you use tighten up your specs and adjust your layout to minimize chance errors. However, in the following illustration, I am going to consider 2 items in Explanation column in reference to John, in real world application, there could definitely be more than 1 such reference. So, here is part of your Entries sheet ...
y040222h1a.xls
CDEF
4
5DateExplanation
622-Feb-04paidtoJohnasloan
722-Feb-04cashtoTompartofsalary
827-Feb-04paidtoJohnasbonus
927-Feb-04
1027-Feb-04
1127-Feb-04
1228-Feb-04
1329-Feb-04
14
Entries


and here is John's sheet, where AdvancedFilter has been applied to extract John's records from the Entries sheet ...
y040222h1a.xls
ABCDEF
1C:\MrExcel\[y040222h1a.xls]John
2John
3
4
5
6
7
8
9
10FALSE
11
12DateExplanation
1322-Feb-04paidtoJohnasloan
1427-Feb-04paidtoJohnasbonus
15
John


Is this the sort of thing you are looking for?
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
Dear Yogi,

Thank you very much, it is one of the good solutions but what i need exactly is once i type 'john' somewhere in "entries" explanations column, the sheet "john" will copy automatically in the same time the date ,explanation and the amount in order to record it in John's sheet and so far for Tom or whatever, i mean it should be on spot not extracting information later on.

i appreciate your interest.
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

i think it is it a kind of a formula which i cant figure:

If(isnumber(find("tom";E6:E8));vlookup(.........????


any one can help please ?
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gaftalik:

The operation using AdvancedFiter is a straight forward approach especially when you may have more than one entry for John, or Tom, and so on . Also the bit I showed you using AdvancedFilter can be automated to perform how you want it.

If you do want a formula based approach, please tell us ...

1. what formula have you tried?
2. simple use of VLOOKUP will extract the first entry for John, or Tom, or whoever -- would extracting just the first entry work for you
3. are you sure you will not have more than one entry for each of the employees

and then let us take it from there.
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Yogi!

I think i was not clear,what i am trying to do is to avoid double work..
before, i used to register my payments transactions and then look what i gave for each person and then copy his account to his sheet,and i wondered if i could do this simultaneously.
As you guessed, there are more than one entry for each person, but i thought involving a cell address formula in it or something for each entry found in order not to repeat it , do you think that is absurd for a formula approach?

Thank you Yogi
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gaftalik:

If you may have more than one entry for the same person, I personally would use AdvancedFilter to do it -- and I would also automate it as you require that on entering the name of the person or something to that effect, the data for that person is copied to the person's own area or sheet, etc.
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
Please allow me to show you where i reached with my trials :oops: , if you see it useless can you try showing me an example of that advanced filter automated thing? :rolleyes:
P.S : instead of CELL("contents";D8) could be a simple D8
accounting.xls
BCDEFG
3
4
5DateExplanationsPlaceDebitvalue
6
722.02.2004paidtojohnasloan1100
822.02.2004cashtotompartofsalary123
923.02.2004
10Entries
11
12DateExplanationsDebitvalue
1322.02.2004cashtotompartofsalary23
14
15
16
17Tom
18
19
20
tom
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gaftalik:

I am going to use the following Entries sheet as my list (database) ...
y040222h1a.xls
CDEF
4
5DateExplanation
622-Feb-04paidtoJohnasloan
722-Feb-04cashtoTompartofsalary
827-Feb-04paidtoJohnasbonus
927-Feb-04
1027-Feb-04
1127-Feb-04
1228-Feb-04
1329-Feb-04
14
Entries


Now you will notice I have two entries for John, that I would like carried over to worksheet named John. So let us look at John's worksheet before his entries are ported over to his sheet ...
y040222h1a.xls
ABCDEF
1C:\MrExcel\[y040222h1a.xls]John
2John
3
4
5FALSE
6
7DateExplanation
8
9
10
John


Please note in cell A1 I have the formula ... =CELL("filename",A1)
to get the file name

in cell A2 I have the formula ... =MID(A1,FIND("]",A1)+1,255)
to programmatically extract John's name from is worksheet

Now using Forms Toolbar, I have created a Button near cell B2, and assigned the following macro to the Button ...
Code:
Sub y_gaftalik()
    Sheets("Entries").Range("D5:E13").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("E4:E5"), CopyToRange:=Range("D7:E7"), Unique:= _
        False
End Sub
In the area I have prepared for extracting John's information, my criteria is range E9:E10 where formula in cell E10 is ... =ISNUMBER(SEARCH($A$2,Explanation))

Now on clicking the Button I had created near cell B2 (executing the macro y_gaftalik), data for John would be extracted as shown in ...
y040222h1a.xls
ABCDEF
1C:\MrExcel\[y040222h1a.xls]John
2John
3
4
5FALSE
6
7DateExplanation
822-Feb-04paidtoJohnasloan
927-Feb-04paidtoJohnasbonus
10
John


I hope this helps!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,332
Messages
5,769,470
Members
425,552
Latest member
learnerrr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top