Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isnumber Match Index help request
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 "txilya" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index and match | Excel Worksheet Functions | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |