Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values
I have a spreadsheet that I use the Subtotal function to total values in
Column G based on items in Column A. Column A contain branch names, and column G contains invoice totals for invoice numbers in column B. For example, after performing SUBTOTAL, I get the following results: Column A Column G Row 329 CTN Total 100 000 Row 562 DBN Total 50 000 Row 649 JHB Total 75 000 Row 683 DFM Total 95 000 What I need is to find CTN Total name in column A, then copy the invoice total (100 000) in column G into another cell (say T1). T2 would then a variation of the same formula to show the invoice total of DBN Total, and so on. I have been trying the MATCH, SUMIF, FIND, IF and INDEX functions, but cannot get it right. Obviously the row numbers of the branches changes regularly as invoices are produced. Can anyone help, please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values
Try this ..
List the branch names in say, S1:S4, i.e.: CTN DBN JHB DFM Then put in T1, copy down to T4: =IF(ISNA(MATCH(S1&" Total",A:A,0)),"", INDEX(G:G,MATCH(S1&" Total",A:A,0))) T1:T4 should return the desired results from col G -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chris Lane" wrote in message ... I have a spreadsheet that I use the Subtotal function to total values in Column G based on items in Column A. Column A contain branch names, and column G contains invoice totals for invoice numbers in column B. For example, after performing SUBTOTAL, I get the following results: Column A Column G Row 329 CTN Total 100 000 Row 562 DBN Total 50 000 Row 649 JHB Total 75 000 Row 683 DFM Total 95 000 What I need is to find CTN Total name in column A, then copy the invoice total (100 000) in column G into another cell (say T1). T2 would then a variation of the same formula to show the invoice total of DBN Total, and so on. I have been trying the MATCH, SUMIF, FIND, IF and INDEX functions, but cannot get it right. Obviously the row numbers of the branches changes regularly as invoices are produced. Can anyone help, please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values
Chris,
Here is a solution First, determine how many totals that you want to cater for and then select that many cells down from T1. Then, enter this formula into the formula bar =IF(ISERROR(SMALL(IF(ISNUMBER(FIND("Total",$A$1:$A $20)),ROW($A$1:$A$20),""), ROW($A$1:$A$20))),"", INDEX(A$1:A$20,SMALL(IF(ISNUMBER(FIND("Total",$A$1 :$A$20)),ROW($A$1:$A$20)," "),ROW($A$1:$A$20)))) as an array, commit with Ctrl-Shift-Enter. Then just copy and paste that T column block to the same size block in U. -- HTH RP (remove nothere from the email address if mailing direct) "Chris Lane" wrote in message ... I have a spreadsheet that I use the Subtotal function to total values in Column G based on items in Column A. Column A contain branch names, and column G contains invoice totals for invoice numbers in column B. For example, after performing SUBTOTAL, I get the following results: Column A Column G Row 329 CTN Total 100 000 Row 562 DBN Total 50 000 Row 649 JHB Total 75 000 Row 683 DFM Total 95 000 What I need is to find CTN Total name in column A, then copy the invoice total (100 000) in column G into another cell (say T1). T2 would then a variation of the same formula to show the invoice total of DBN Total, and so on. I have been trying the MATCH, SUMIF, FIND, IF and INDEX functions, but cannot get it right. Obviously the row numbers of the branches changes regularly as invoices are produced. Can anyone help, please? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values
Thanks Max, works perfectly
"Max" wrote in message ... Try this .. List the branch names in say, S1:S4, i.e.: CTN DBN JHB DFM Then put in T1, copy down to T4: =IF(ISNA(MATCH(S1&" Total",A:A,0)),"", INDEX(G:G,MATCH(S1&" Total",A:A,0))) T1:T4 should return the desired results from col G -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chris Lane" wrote in message ... I have a spreadsheet that I use the Subtotal function to total values in Column G based on items in Column A. Column A contain branch names, and column G contains invoice totals for invoice numbers in column B. For example, after performing SUBTOTAL, I get the following results: Column A Column G Row 329 CTN Total 100 000 Row 562 DBN Total 50 000 Row 649 JHB Total 75 000 Row 683 DFM Total 95 000 What I need is to find CTN Total name in column A, then copy the invoice total (100 000) in column G into another cell (say T1). T2 would then a variation of the same formula to show the invoice total of DBN Total, and so on. I have been trying the MATCH, SUMIF, FIND, IF and INDEX functions, but cannot get it right. Obviously the row numbers of the branches changes regularly as invoices are produced. Can anyone help, please? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values
Thanks very much, Bob
"Bob Phillips" wrote in message ... Chris, Here is a solution First, determine how many totals that you want to cater for and then select that many cells down from T1. Then, enter this formula into the formula bar =IF(ISERROR(SMALL(IF(ISNUMBER(FIND("Total",$A$1:$A $20)),ROW($A$1:$A$20),""), ROW($A$1:$A$20))),"", INDEX(A$1:A$20,SMALL(IF(ISNUMBER(FIND("Total",$A$1 :$A$20)),ROW($A$1:$A$20)," "),ROW($A$1:$A$20)))) as an array, commit with Ctrl-Shift-Enter. Then just copy and paste that T column block to the same size block in U. -- HTH RP (remove nothere from the email address if mailing direct) "Chris Lane" wrote in message ... I have a spreadsheet that I use the Subtotal function to total values in Column G based on items in Column A. Column A contain branch names, and column G contains invoice totals for invoice numbers in column B. For example, after performing SUBTOTAL, I get the following results: Column A Column G Row 329 CTN Total 100 000 Row 562 DBN Total 50 000 Row 649 JHB Total 75 000 Row 683 DFM Total 95 000 What I need is to find CTN Total name in column A, then copy the invoice total (100 000) in column G into another cell (say T1). T2 would then a variation of the same formula to show the invoice total of DBN Total, and so on. I have been trying the MATCH, SUMIF, FIND, IF and INDEX functions, but cannot get it right. Obviously the row numbers of the branches changes regularly as invoices are produced. Can anyone help, please? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values
Glad to helped, Chris !
Thanks for the feedback -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chris Lane" wrote in message ... Thanks Max, works perfectly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions | |||
finding cell values | Excel Discussion (Misc queries) | |||
finding values and displaying adjacent values | Excel Worksheet Functions | |||
Finding Values With more than 2 decimal Places | Excel Discussion (Misc queries) | |||
Finding the max of a column where values less than a specified number | Excel Worksheet Functions |