Isnumber Match Index help request

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi txilya

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

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

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

 

[Guest]

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

Hi txilya

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

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

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

 

[Guest]

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!!

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

Hi txilya

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

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



:

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

 

[Guest]

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

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!!

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

Hi txilya

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

:

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



:

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

 

[Guest]

Hi txilya!!!!!!
Well I am getting there, it is takeing an age as only working on things for
short spells in the evenings.
Your last post did correct the Syntax error, thank you. BUT.
Put =Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25) in
A1,Sheet1,Book2, which returned 0
..++++++++++++++++++++++++++
Put =SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25) in
E8,Sheet1,Book2, which returnd the value it should have untill I changed a
value in Book1,Sheet1,$y$1:$y$25. E8,Sheet1,Book2 should have changed but
continued to return the original result. Any ideas greatly received
(I have Auto calculation on)

I am obviously still not getting things right. Have you succeded in running
this with the example I gave in my earlier post?

Ageing, frustrated Juls

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

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!!

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


:

Hi txilya

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

:

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



:

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

 

[Guest]

Hi Juls,
I understand your frustration, but you are getting there. You just need to
put the two formulas in Sheet1, Book2 in the right place.
Column A should contain 1st (in A1), 2nd (A2) and so on.
Column D contains job string, so put
=Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25) in D1 and
copy the formula down.
Column E contains totals, so put
=SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25) in E1 and copy
the formula down.
This works in my test files and should work with yours.

Of course, on Sheet1 in Book2 you can put your list of dates and the
formulas any place you want, just make sure you adjust the formulas
accordingly - they are written to get the lookup date from the cell A1.

If you still have problems with this task, email me directly (e-mail address removed)
and will email you my test files.

Regards,
txilya

Hi txilya!!!!!!
Well I am getting there, it is takeing an age as only working on things for
short spells in the evenings.
Your last post did correct the Syntax error, thank you. BUT.
Put =Job_String(A1,[Book1]Sheet1!$A$1:$A$25,[Book1]Sheet1!$B$1:$B$25) in
A1,Sheet1,Book2, which returned 0
.++++++++++++++++++++++++++
Put =SUMIF([Book1]Sheet1!$A$1:$A$25,A1,[Book1]Sheet1!$Y$1:$Y$25) in
E8,Sheet1,Book2, which returnd the value it should have untill I changed a
value in Book1,Sheet1,$y$1:$y$25. E8,Sheet1,Book2 should have changed but
continued to return the original result. Any ideas greatly received
(I have Auto calculation on)

I am obviously still not getting things right. Have you succeded in running
this with the example I gave in my earlier post?

Ageing, frustrated Juls

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

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!!

:

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


:

Hi txilya

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

:

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



:

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