Can any body help????
Bob Phillips kindly replyed to my original post with this

=IF(ISNUMBER(MATCH("value",A:A,0)),"value"&INDEX(B :B,MATCH("value",A:A,0)),"
")
But it didnt work, due almost certainly to a poor explaination from me.
Original post now lost in annals of new posting.

Layed out below is I think a better explaination.

What we are dealing with is monthly job sheets (1-12) for one customer
contained in Book1. There is 3 separate identical sections on each sheet
relating to 3 differant staff members, each section is Col A:Y by 25 rows.
Col A contains dates displayed as 1st, 3rd etc. Col B contains a text
description of work carried out. Col C:X contains financial break down of
work carried out. Col Y contains Total in £ of C:X

E.g

Book 1 (could be on any one sheet #1-12)

Col A Col B Col C:X Col Y
2nd aa 5+5 £10
6th bb 2+3+5 £10
17th cc 5+5 £10
-----------------------------------
1st dd 3+2+5 £10
6th ee 4+6 £10
10th ff 2+8 £10
----------------------------------
2nd gg 8+2 £10
6th hh 7+3 £10
17th ii 1+9 £10

I am trying to produce a edited monthly Invoice from the above data in Book 2
Sheets 1-12 but displayed as below

1st dd 10
2nd aagg 20
6th bbeehh 30
10th ff 10
17th ccii 20
Total £ 90

Hi Juls,

You may want to try another approach - user defined function.

First, create function Job_String in Book2:
ALT+F11, Insert - Module, and paste the following code:
'+++++++++++++++++++++
Function Job_String(aDate As String, DateRange As Range, JobRange As Range)
As String
Dim i As Integer
For i = 1 To DateRange.Count
If aDate = DateRange(i).Value Then Job_String = Job_String &
JobRange(i).Value
Next i
End Function
'+++++++++++++++++++++

Next, use the function on Sheet1 of Book2 to generate job string:
=Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25)

Finally, calculate your totals:
=SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25)

Hope it helps.
Ilya Yun



"Juls" wrote:

Can any body help????
Bob Phillips kindly replyed to my original post with this

=IF(ISNUMBER(MATCH("value",A:A,0)),"value"&INDEX(B :B,MATCH("value",A:A,0)),"
")
But it didnt work, due almost certainly to a poor explaination from me.
Original post now lost in annals of new posting.

Layed out below is I think a better explaination.

What we are dealing with is monthly job sheets (1-12) for one customer
contained in Book1. There is 3 separate identical sections on each sheet
relating to 3 differant staff members, each section is Col A:Y by 25 rows.
Col A contains dates displayed as 1st, 3rd etc. Col B contains a text
description of work carried out. Col C:X contains financial break down of
work carried out. Col Y contains Total in £ of C:X

E.g

Book 1 (could be on any one sheet #1-12)

Col A Col B Col C:X Col Y
2nd aa 5+5 £10
6th bb 2+3+5 £10
17th cc 5+5 £10
-----------------------------------
1st dd 3+2+5 £10
6th ee 4+6 £10
10th ff 2+8 £10
----------------------------------
2nd gg 8+2 £10
6th hh 7+3 £10
17th ii 1+9 £10

I am trying to produce a edited monthly Invoice from the above data in Book 2
Sheets 1-12 but displayed as below

1st dd 10
2nd aagg 20
6th bbeehh 30
10th ff 10
17th ccii 20
Total £ 90

Hi txilya

I must being doing something wrong as I am getting #Name? come up. It says
the formula contains unrecognized text

"txilya" wrote:

Hi Juls,

You may want to try another approach - user defined function.

First, create function Job_String in Book2:
ALT+F11, Insert - Module, and paste the following code:
'+++++++++++++++++++++
Function Job_String(aDate As String, DateRange As Range, JobRange As Range)
As String
Dim i As Integer
For i = 1 To DateRange.Count
If aDate = DateRange(i).Value Then Job_String = Job_String &
JobRange(i).Value
Next i
End Function
'+++++++++++++++++++++

Next, use the function on Sheet1 of Book2 to generate job string:
=Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25)

Finally, calculate your totals:
=SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25)

Hope it helps.
Ilya Yun



"Juls" wrote:

Can any body help????
Bob Phillips kindly replyed to my original post with this

=IF(ISNUMBER(MATCH("value",A:A,0)),"value"&INDEX(B :B,MATCH("value",A:A,0)),"
")
But it didnt work, due almost certainly to a poor explaination from me.
Original post now lost in annals of new posting.

Layed out below is I think a better explaination.

What we are dealing with is monthly job sheets (1-12) for one customer
contained in Book1. There is 3 separate identical sections on each sheet
relating to 3 differant staff members, each section is Col A:Y by 25 rows.
Col A contains dates displayed as 1st, 3rd etc. Col B contains a text
description of work carried out. Col C:X contains financial break down of
work carried out. Col Y contains Total in £ of C:X

E.g

Book 1 (could be on any one sheet #1-12)

Col A Col B Col C:X Col Y
2nd aa 5+5 £10
6th bb 2+3+5 £10
17th cc 5+5 £10
-----------------------------------
1st dd 3+2+5 £10
6th ee 4+6 £10
10th ff 2+8 £10
----------------------------------
2nd gg 8+2 £10
6th hh 7+3 £10
17th ii 1+9 £10

I am trying to produce a edited monthly Invoice from the above data in Book 2
Sheets 1-12 but displayed as below

1st dd 10
2nd aagg 20
6th bbeehh 30
10th ff 10
17th ccii 20
Total £ 90

Juls,

Error #Name? indicates that Excel cannot find the fuction you created.
1) Make sure that put VBA code in Book2.
2) Check spelling
3) Hit F9 to recalcultae Book2


"Juls" wrote:

Hi txilya

I must being doing something wrong as I am getting #Name? come up. It says
the formula contains unrecognized text

"txilya" wrote:

Hi Juls,

You may want to try another approach - user defined function.

First, create function Job_String in Book2:
ALT+F11, Insert - Module, and paste the following code:
'+++++++++++++++++++++
Function Job_String(aDate As String, DateRange As Range, JobRange As Range)
As String
Dim i As Integer
For i = 1 To DateRange.Count
If aDate = DateRange(i).Value Then Job_String = Job_String &
JobRange(i).Value
Next i
End Function
'+++++++++++++++++++++

Next, use the function on Sheet1 of Book2 to generate job string:
=Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25)

Finally, calculate your totals:
=SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25)

Hope it helps.
Ilya Yun



"Juls" wrote:

Can any body help????
Bob Phillips kindly replyed to my original post with this

=IF(ISNUMBER(MATCH("value",A:A,0)),"value"&INDEX(B :B,MATCH("value",A:A,0)),"
")
But it didnt work, due almost certainly to a poor explaination from me.
Original post now lost in annals of new posting.

Layed out below is I think a better explaination.

What we are dealing with is monthly job sheets (1-12) for one customer
contained in Book1. There is 3 separate identical sections on each sheet
relating to 3 differant staff members, each section is Col A:Y by 25 rows.
Col A contains dates displayed as 1st, 3rd etc. Col B contains a text
description of work carried out. Col C:X contains financial break down of
work carried out. Col Y contains Total in £ of C:X

E.g

Book 1 (could be on any one sheet #1-12)

Col A Col B Col C:X Col Y
2nd aa 5+5 £10
6th bb 2+3+5 £10
17th cc 5+5 £10
-----------------------------------
1st dd 3+2+5 £10
6th ee 4+6 £10
10th ff 2+8 £10
----------------------------------
2nd gg 8+2 £10
6th hh 7+3 £10
17th ii 1+9 £10

I am trying to produce a edited monthly Invoice from the above data in Book 2
Sheets 1-12 but displayed as below

1st dd 10
2nd aagg 20
6th bbeehh 30
10th ff 10
17th ccii 20
Total £ 90

hi txilya
Thanks for your help and I hope patience.
Oh am I having fun and games with this. Not only am I a relative Excel
novice I have never dealt with VBA. Refollowed your instructions, but when
hit F9 a Compile Error Syntax Error came up. When debugging, first line of
code is highlighted yellow with yellow arrow in margin, second line
highlighted blue. Can (or is there any need?) your instructions be
simplified further for this novice.

Frustrated Juls!!

"txilya" wrote:

Juls,

Error #Name? indicates that Excel cannot find the fuction you created.
1) Make sure that put VBA code in Book2.
2) Check spelling
3) Hit F9 to recalcultae Book2


"Juls" wrote:

Hi txilya

I must being doing something wrong as I am getting #Name? come up. It says
the formula contains unrecognized text

"txilya" wrote:

Hi Juls,

You may want to try another approach - user defined function.

First, create function Job_String in Book2:
ALT+F11, Insert - Module, and paste the following code:
'+++++++++++++++++++++
Function Job_String(aDate As String, DateRange As Range, JobRange As Range)
As String
Dim i As Integer
For i = 1 To DateRange.Count
If aDate = DateRange(i).Value Then Job_String = Job_String &
JobRange(i).Value
Next i
End Function
'+++++++++++++++++++++

Next, use the function on Sheet1 of Book2 to generate job string:
=Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25)

Finally, calculate your totals:
=SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25)

Hope it helps.
Ilya Yun



"Juls" wrote:

Can any body help????
Bob Phillips kindly replyed to my original post with this

=IF(ISNUMBER(MATCH("value",A:A,0)),"value"&INDEX(B :B,MATCH("value",A:A,0)),"
")
But it didnt work, due almost certainly to a poor explaination from me.
Original post now lost in annals of new posting.

Layed out below is I think a better explaination.

What we are dealing with is monthly job sheets (1-12) for one customer
contained in Book1. There is 3 separate identical sections on each sheet
relating to 3 differant staff members, each section is Col A:Y by 25 rows.
Col A contains dates displayed as 1st, 3rd etc. Col B contains a text
description of work carried out. Col C:X contains financial break down of
work carried out. Col Y contains Total in £ of C:X

E.g

Book 1 (could be on any one sheet #1-12)

Col A Col B Col C:X Col Y
2nd aa 5+5 £10
6th bb 2+3+5 £10
17th cc 5+5 £10
-----------------------------------
1st dd 3+2+5 £10
6th ee 4+6 £10
10th ff 2+8 £10
----------------------------------
2nd gg 8+2 £10
6th hh 7+3 £10
17th ii 1+9 £10

I am trying to produce a edited monthly Invoice from the above data in Book 2
Sheets 1-12 but displayed as below

1st dd 10
2nd aagg 20
6th bbeehh 30
10th ff 10
17th ccii 20
Total £ 90

Hi Juls,

I didn't realize that this text editor automatically wraps text creating
hard line breaks. So it turned my six line code into 8 lines with breaks in
wrong places. Below is a modified code with shorter lines (a space followed
by an underscore at the end of the line indicates that code continues on the
next line):
'*********
Function Job_String(aDate As String, DateRange As Range, _
JobRange As Range) As String
Dim i As Integer
For i = 1 To DateRange.Count
If aDate = DateRange(i).Value Then _
Job_String = Job_String & JobRange(i).Value
Next i
End Function
'*********

Indentation is optional.
Good luck!
Ilya


"Juls" wrote:

hi txilya
Thanks for your help and I hope patience.
Oh am I having fun and games with this. Not only am I a relative Excel
novice I have never dealt with VBA. Refollowed your instructions, but when
hit F9 a Compile Error Syntax Error came up. When debugging, first line of
code is highlighted yellow with yellow arrow in margin, second line
highlighted blue. Can (or is there any need?) your instructions be
simplified further for this novice.

Frustrated Juls!!

"txilya" wrote:

Juls,

Error #Name? indicates that Excel cannot find the fuction you created.
1) Make sure that put VBA code in Book2.
2) Check spelling
3) Hit F9 to recalcultae Book2


"Juls" wrote:

Hi txilya

I must being doing something wrong as I am getting #Name? come up. It says
the formula contains unrecognized text

"txilya" wrote:

Hi Juls,

You may want to try another approach - user defined function.

First, create function Job_String in Book2:
ALT+F11, Insert - Module, and paste the following code:
'+++++++++++++++++++++
Function Job_String(aDate As String, DateRange As Range, JobRange As Range)
As String
Dim i As Integer
For i = 1 To DateRange.Count
If aDate = DateRange(i).Value Then Job_String = Job_String &
JobRange(i).Value
Next i
End Function
'+++++++++++++++++++++

Next, use the function on Sheet1 of Book2 to generate job string:
=Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25)

Finally, calculate your totals:
=SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25)

Hope it helps.
Ilya Yun



"Juls" wrote:

Can any body help????
Bob Phillips kindly replyed to my original post with this

=IF(ISNUMBER(MATCH("value",A:A,0)),"value"&INDEX(B :B,MATCH("value",A:A,0)),"
")
But it didnt work, due almost certainly to a poor explaination from me.
Original post now lost in annals of new posting.

Layed out below is I think a better explaination.

What we are dealing with is monthly job sheets (1-12) for one customer
contained in Book1. There is 3 separate identical sections on each sheet
relating to 3 differant staff members, each section is Col A:Y by 25 rows.
Col A contains dates displayed as 1st, 3rd etc. Col B contains a text
description of work carried out. Col C:X contains financial break down of
work carried out. Col Y contains Total in £ of C:X

E.g

Book 1 (could be on any one sheet #1-12)

Col A Col B Col C:X Col Y
2nd aa 5+5 £10
6th bb 2+3+5 £10
17th cc 5+5 £10
-----------------------------------
1st dd 3+2+5 £10
6th ee 4+6 £10
10th ff 2+8 £10
----------------------------------
2nd gg 8+2 £10
6th hh 7+3 £10
17th ii 1+9 £10

I am trying to produce a edited monthly Invoice from the above data in Book 2
Sheets 1-12 but displayed as below

1st dd 10
2nd aagg 20
6th bbeehh 30
10th ff 10
17th ccii 20
Total £ 90