Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
I need to look up name and then id then return the salary to a
different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
Have the file that contains this data open:
Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message oups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
On Jun 4, 2:27 am, "T. Valko" wrote:
Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1*:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message oups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - Biff, I cannot thank u enough. You are just great! No explanation could be any exlicit than this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
On Jun 4, 2:27 am, "T. Valko" wrote:
Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1*:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message oups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
"Positive" wrote in message oups.com... On Jun 4, 2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1*:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message oups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - Biff, I cannot thank u enough. You are just great! No explanation could be any exlicit than this. You're welcome. Thanks for the feedback! Biff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
"Positive" wrote in message ups.com... On Jun 4, 2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1*:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message oups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is the lookup value = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
On Jun 4, 2:25 pm, "T. Valko" wrote:
"Positive" wrote in message ups.com... On Jun 4, 2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1**:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message roups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is the lookup value = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff- Hide quoted text - - Show quoted text - Biff, but if I want to know how much rent that Jack pays and that rent will be put in a DIFFERENT WORKBOOK, do I have to add file.xls,sheet... with the formula. If I have to how. I am so confused when it comes to complicated braces, brackets... parentheses... Many thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
"Positive" wrote in message ups.com... On Jun 4, 2:25 pm, "T. Valko" wrote: "Positive" wrote in message ups.com... On Jun 4, 2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1**:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message roups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is the lookup value = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff- Hide quoted text - - Show quoted text - ************************* Biff, but if I want to know how much rent that Jack pays and that rent will be put in a DIFFERENT WORKBOOK, do I have to add file.xls,sheet... with the formula. If I have to how. I am so confused when it comes to complicated braces, brackets... parentheses... Many thanks ************************* Anytime you reference another file you have to include the path. If you want to know the specific category for a particular person and the categories follow the pattern as posted in your sample: 1. utility, 2. rent, 3. phone Assume the source file is named file.xls In the file where you want this info: A2 = Jack Formula to lookup Jack's rent: =INDEX('[file.xls]Sheet1'!$B$2:$B$11,MATCH(A2,'[file.xls]Sheet1'!$A$2:$A$11,0)+1) What you have to do is adjust the category offset from the persons name. For example, the rent category is listed 2nd but it is offset from the persons name by 1 row so in the formula that's what the +1 means. If you want the persons utility that category is on the same row as the persons name so the offset is 0. In that case the formula would use +0. If you want the persons phone category that offset would be +2. Biff |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
On Jun 4, 10:24 pm, "T. Valko" wrote:
"Positive" wrote in message ups.com... On Jun 4,2:25 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4,2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1***:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message roups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is thelookupvalue = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff- Hide quoted text - - Show quoted text - ************************* Biff, but if I want to know how much rent that Jack pays and that rent will be put in a DIFFERENT WORKBOOK, do I have to add file.xls,sheet... with the formula. If I have to how. I am so confused when it comes to complicated braces, brackets... parentheses... Many thanks ************************* Anytime you reference another file you have to include the path. If you want to know the specific category for a particular person and the categories follow the pattern as posted in your sample: 1. utility,2. rent, 3. phone Assume the source file is named file.xls In the file where you want this info: A2 = Jack Formula tolookupJack's rent: =INDEX('[file.xls]Sheet1'!$B$2:$B$11,MATCH(A2,'[file.xls]Sheet1'!$A$2:$A$11*,0)+1) What you have to do is adjust the category offset from the persons name. For example, the rent category is listed 2nd but it is offset from the persons name by 1 row so in the formula that's what the +1 means. If you want the persons utility that category is on the same row as the persons name so the offset is 0. In that case the formula would use +0. If you want the persons phone category that offset would be +2. Biff- Hide quoted text - - Show quoted text - Biff, again THANKS A BUNCH. It works wonder and you have indirectlty helped me to consolidate data and solve a lot of problems in report automation at work. :-) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
"Positive" wrote in message oups.com... On Jun 4, 10:24 pm, "T. Valko" wrote: "Positive" wrote in message ups.com... On Jun 4,2:25 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4,2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1***:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message roups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is thelookupvalue = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff- Hide quoted text - - Show quoted text - ************************* Biff, but if I want to know how much rent that Jack pays and that rent will be put in a DIFFERENT WORKBOOK, do I have to add file.xls,sheet... with the formula. If I have to how. I am so confused when it comes to complicated braces, brackets... parentheses... Many thanks ************************* Anytime you reference another file you have to include the path. If you want to know the specific category for a particular person and the categories follow the pattern as posted in your sample: 1. utility,2. rent, 3. phone Assume the source file is named file.xls In the file where you want this info: A2 = Jack Formula tolookupJack's rent: =INDEX('[file.xls]Sheet1'!$B$2:$B$11,MATCH(A2,'[file.xls]Sheet1'!$A$2:$A$11*,0)+1) What you have to do is adjust the category offset from the persons name. For example, the rent category is listed 2nd but it is offset from the persons name by 1 row so in the formula that's what the +1 means. If you want the persons utility that category is on the same row as the persons name so the offset is 0. In that case the formula would use +0. If you want the persons phone category that offset would be +2. Biff- Hide quoted text - - Show quoted text - ********** Biff, again THANKS A BUNCH. It works wonder and you have indirectlty helped me to consolidate data and solve a lot of problems in report automation at work. :-) ********** You're welcome. Thanks for the feedback! Biff |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
On Jun 6, 1:45 pm, "T. Valko" wrote:
"Positive" wrote in message oups.com... On Jun 4, 10:24 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4,2:25 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4,2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1****:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message roups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is thelookupvalue = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff- Hide quoted text - - Show quoted text - ************************* Biff, but if I want to know how much rent that Jack pays and that rent will be put in a DIFFERENT WORKBOOK, do I have to add file.xls,sheet... with the formula. If I have to how. I am so confused when it comes to complicated braces, brackets... parentheses... Many thanks ************************* Anytime you reference another file you have to include the path. If you want to know the specific category for a particular person and the categories follow the pattern as posted in your sample: 1. utility,2. rent, 3. phone Assume the source file is named file.xls In the file where you want this info: A2 = Jack Formula tolookupJack's rent: =INDEX('[file.xls]Sheet1'!$B$2:$B$11,MATCH(A2,'[file.xls]Sheet1'!$A$2:$A$11**,0)+1) What you have to do is adjust the category offset from the persons name. For example, the rent category is listed 2nd but it is offset from the persons name by 1 row so in the formula that's what the +1 means. If you want the persons utility that category is on the same row as the persons name so the offset is 0. In that case the formula would use +0. If you want the persons phone category that offset would be +2. Biff- Hide quoted text - - Show quoted text - ********** Biff, again THANKS A BUNCH. It works wonder and you have indirectlty helped me to consolidate data and solve a lot of problems in report automation at work. :-) ********** You're welcome. Thanks for the feedback! Biff- Hide quoted text - - Show quoted text - I have another situation e.g. Jack 100 124 Jill 231 235 Tom 0 200 Henry 0 321 I need to look up the value in column B and put it in a DIFFERENT WORBOOK, if column B shows 0 value,then it needs to take value in column C. E.g For Tom and Henry i will need to put down 200 and 321 in A DIFFERENT WORKBOOK. If it is in the same WORBOOK,then I would know how to use IF function, but in this case I don'r know how to combine VLOOKUP and IF funtions. Please help Thanks -Positive |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
"Positive" wrote in message ups.com... On Jun 6, 1:45 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4, 10:24 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4,2:25 pm, "T. Valko" wrote: "Positive" wrote in message oups.com... On Jun 4,2:27 am, "T. Valko" wrote: Have the file that contains this data open: Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 In a cell in the file where you want the formula start typing in the formula: =SUMPRODUCT(--( Now, navigate to the open source file and select the sheet and range for the name. Excel will add the file name for you. It'll look something like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10 Now, start typing again and add the criteria for the name: ="Jack"),--( Now, point to the range for the ID# then start typing again and add the criteria for the ID#: =3), Now, point to the range for the salary then finish the formula by typing a closing ). When you're done the formula should look like this: =SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1****:$B$10=3),[file.xls]Sheet1!$C$1:$C$10) Pointing to the ranges in the open source file is *much* easier than typing all that stuff in. Biff "Positive" wrote in message roups.com... I need to look up name and then id then return the salary to a different workbook. e.g Jack 0 $35,000 Allen 2 $45,000 Jack 3 $46,000 Mike 4 $72,000 I want to look up Jack who has id #3 and put his salary to a DIFFERENT WORKBOOK. Please help- Hide quoted text - - Show quoted text - **************************** Biff, I cannot thank you enough. No explanation could be more explicit than this. I have another question. I have a spreadsheet e.g Jack 20 (utility) 600 (rent) 50 (phone) Jill 50(utility) 1000(rent) 70(phone) Tom 40 (utility) 700 (rent) 45 (phone) This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know how to use VLOOKUP for 1 item but for 3 items per person, I don't know how. Please help Positive ************************** You can't use VLOOKUP for this. Assume the names are in column A, amounts in column B. Try this: Cell E2 is thelookupvalue = some name Enter this formula F2 and copy down a total of 3 cells: =INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1) Biff- Hide quoted text - - Show quoted text - ************************* Biff, but if I want to know how much rent that Jack pays and that rent will be put in a DIFFERENT WORKBOOK, do I have to add file.xls,sheet... with the formula. If I have to how. I am so confused when it comes to complicated braces, brackets... parentheses... Many thanks ************************* Anytime you reference another file you have to include the path. If you want to know the specific category for a particular person and the categories follow the pattern as posted in your sample: 1. utility,2. rent, 3. phone Assume the source file is named file.xls In the file where you want this info: A2 = Jack Formula tolookupJack's rent: =INDEX('[file.xls]Sheet1'!$B$2:$B$11,MATCH(A2,'[file.xls]Sheet1'!$A$2:$A$11**,0)+1) What you have to do is adjust the category offset from the persons name. For example, the rent category is listed 2nd but it is offset from the persons name by 1 row so in the formula that's what the +1 means. If you want the persons utility that category is on the same row as the persons name so the offset is 0. In that case the formula would use +0. If you want the persons phone category that offset would be +2. Biff- Hide quoted text - - Show quoted text - ********** Biff, again THANKS A BUNCH. It works wonder and you have indirectlty helped me to consolidate data and solve a lot of problems in report automation at work. :-) ********** You're welcome. Thanks for the feedback! Biff- Hide quoted text - - Show quoted text - ********** I have another situation e.g. Jack 100 124 Jill 231 235 Tom 0 200 Henry 0 321 I need to look up the value in column B and put it in a DIFFERENT WORBOOK, if column B shows 0 value,then it needs to take value in column C. E.g For Tom and Henry i will need to put down 200 and 321 in A DIFFERENT WORKBOOK. If it is in the same WORBOOK,then I would know how to use IF function, but in this case I don'r know how to combine VLOOKUP and IF funtions. Please help Thanks -Positive ********** Try something like this: =VLOOKUP(A1,[file.xls]Sheet1!$A$1:$C$4,IF(VLOOKUP(A1,[file.xls]Sheet1!$A$1:$C$4,2,0)=0,3,2),0) Biff |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up 2 values, return result in a different workbook
When I use SUMPRODUCT formula, especially to look up data from
different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my other formulas e.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I could protect this SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Return result only if.... | Excel Worksheet Functions |