Can Excel defy accounting programs in this issue?

[gaftalik]

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 ?

Book1
	C	D	E	F	G	H
3
4		Date	Explanations	Place	Debitvalue
5
6		22.02.2004	paidtojohnasloan	1	100
7		22.02.2004	cashtotompartofsalary	1	23
8		22.02.2004
9		Entries
10
11		Date	Explanations	Debitvalue
12		22.02.2004	cashtotompartofsalary	23
13
14
15
16		Tom
17
18		Date	Explanations	Debitvalue
19		22.02.2004	paidtojohnasloan	100
20
21
22
23		John
24
Sheet1

 

[headtoadie]

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]

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
	C	D	E	F
4
5		Date	Explanation
6		22-Feb-04	paidtoJohnasloan
7		22-Feb-04	cashtoTompartofsalary
8		27-Feb-04	paidtoJohnasbonus
9		27-Feb-04
10		27-Feb-04
11		27-Feb-04
12		28-Feb-04
13		29-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
	A	B	C	D	E	F
1	C:\MrExcel\[y040222h1a.xls]John
2	John
3
4
5
6
7
8
9
10					FALSE
11
12				Date	Explanation
13				22-Feb-04	paidtoJohnasloan
14				27-Feb-04	paidtoJohnasbonus
15
John

Is this the sort of thing you are looking for?

 

[gaftalik]

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]

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]

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]

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]

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]

Please allow me to show you where i reached with my trials , if you see it useless can you try showing me an example of that advanced filter automated thing?
P.S : instead of CELL("contents";D8) could be a simple D8

accounting.xls
	B	C	D	E	F	G
3
4
5		Date	Explanations	Place	Debitvalue
6
7		22.02.2004	paidtojohnasloan	1	100
8		22.02.2004	cashtotompartofsalary	1	23
9		23.02.2004
10		Entries
11
12		Date	Explanations	Debitvalue
13		22.02.2004	cashtotompartofsalary	23
14
15
16
17		Tom
18
19
20
tom

 

[Yogi Anand]

Hi gaftalik:

I am going to use the following Entries sheet as my list (database) ...

y040222h1a.xls
	C	D	E	F
4
5		Date	Explanation
6		22-Feb-04	paidtoJohnasloan
7		22-Feb-04	cashtoTompartofsalary
8		27-Feb-04	paidtoJohnasbonus
9		27-Feb-04
10		27-Feb-04
11		27-Feb-04
12		28-Feb-04
13		29-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
	A	B	C	D	E	F
1	C:\MrExcel\[y040222h1a.xls]John
2	John
3
4
5					FALSE
6
7				Date	Explanation
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 ...

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
	A	B	C	D	E	F
1	C:\MrExcel\[y040222h1a.xls]John
2	John
3
4
5					FALSE
6
7				Date	Explanation
8				22-Feb-04	paidtoJohnasloan
9				27-Feb-04	paidtoJohnasbonus
10
John

I hope this helps!