Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Writing a formula which dynamically generates results
Can I write a dynamic formula based on the different kind of data present in
a particular column and which generates results dynamically depending on the data being added? Eg; Suppose my coulmn contains a list of items. Emp_name Earning A 10 A 20 A 20 B 20 C 30 D 5 D 10 I want to write a formula which dynamically generates the earnings of employees by taking unique names from the column Say If a some adds one more Employee E, the total earnings for employee E should automatically get generated at some cell along with his name. Is this possible in excel? Thanks in advance, Sandeep |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Writing a formula which dynamically generates results
A simple formulas play which should deliver it for you
Assuming source data in cols A & B, in row 2 down In C2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW())) Leave C1 blank In D2: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1)))) In E2: =IF(D2="","",SUMIF(A:A,D2,B:B)) Select C2:E2, copy down to cover the max expected extent of data in col A, say down to E500? Minimize/hide away col C. Col D will return a dynamic unique list of the names from col A, while col E returns the corresponding total earnings -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sandeep singh" wrote: Can I write a dynamic formula based on the different kind of data present in a particular column and which generates results dynamically depending on the data being added? Eg; Suppose my coulmn contains a list of items. Emp_name Earning A 10 A 20 A 20 B 20 C 30 D 5 D 10 I want to write a formula which dynamically generates the earnings of employees by taking unique names from the column Say If a some adds one more Employee E, the total earnings for employee E should automatically get generated at some cell along with his name. Is this possible in excel? Thanks in advance, Sandeep |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Writing a formula which dynamically generates results
Thanks alot !
Can you also tell me how to modify formula, if I want to put the formula in some other sheet say sheet3 and data is present in sheet1? Regards, Sandeep "Max" wrote: A simple formulas play which should deliver it for you Assuming source data in cols A & B, in row 2 down In C2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW())) Leave C1 blank In D2: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1)))) In E2: =IF(D2="","",SUMIF(A:A,D2,B:B)) Select C2:E2, copy down to cover the max expected extent of data in col A, say down to E500? Minimize/hide away col C. Col D will return a dynamic unique list of the names from col A, while col E returns the corresponding total earnings -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sandeep singh" wrote: Can I write a dynamic formula based on the different kind of data present in a particular column and which generates results dynamically depending on the data being added? Eg; Suppose my coulmn contains a list of items. Emp_name Earning A 10 A 20 A 20 B 20 C 30 D 5 D 10 I want to write a formula which dynamically generates the earnings of employees by taking unique names from the column Say If a some adds one more Employee E, the total earnings for employee E should automatically get generated at some cell along with his name. Is this possible in excel? Thanks in advance, Sandeep |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Writing a formula which dynamically generates results
With source data in Sheet1's cols A & B, from row 2 down
In Sheet3, In A2: =IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet 1!A2)1,"",ROW())) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) In C2: =IF(B2="","",SUMIF(Sheet1!A:A,B2,Sheet1!B:B)) Select A2:C2, copy down to cover the max expected extent of data in Sheet1's col A. Hide away col A. Cols B & C returns the required results, as before. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sandeep singh" wrote: Thanks alot ! Can you also tell me how to modify formula, if I want to put the formula in some other sheet say sheet3 and data is present in sheet1? Regards, Sandeep |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Writing a formula which dynamically generates results
Hey Max,
Thanks alot, I need a little more help, How can i use your formula with COUNTIF in place of SUMIF? Instead of Summing up the elements i simply want to count them? Rest all things remain as earlier, i.e. data in sheet1, formula in sheet3 etc. Eg: for col A & B A B sa h sa h sa y ba y baba h sa y Result: The result in sheet3 should be Count_of_h Count_of_Y sa 2 2 ba 0 1 baba 1 0 Kindly do help me Thanks again, Regards, Sandeep "Max" wrote: With source data in Sheet1's cols A & B, from row 2 down In Sheet3, In A2: =IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet 1!A2)1,"",ROW())) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) In C2: =IF(B2="","",SUMIF(Sheet1!A:A,B2,Sheet1!B:B)) Select A2:C2, copy down to cover the max expected extent of data in Sheet1's col A. Hide away col A. Cols B & C returns the required results, as before. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sandeep singh" wrote: Thanks alot ! Can you also tell me how to modify formula, if I want to put the formula in some other sheet say sheet3 and data is present in sheet1? Regards, Sandeep |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Writing a formula which dynamically generates results
In Sheet3,
With cols A and B populated as before .. List in C1 across the uniques from col B in Sheet1, ie: h, Y Then place in C2: =IF($B2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$B2)* (Sheet1!$B$2:$B$100=C$1))) Copy C2 across/fill down. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sandeep Singh" wrote: Hey Max, Thanks alot, I need a little more help, How can i use your formula with COUNTIF in place of SUMIF? Instead of Summing up the elements i simply want to count them? Rest all things remain as earlier, i.e. data in sheet1, formula in sheet3 etc. Eg: for col A & B A B sa h sa h sa y ba y baba h sa y Result: The result in sheet3 should be Count_of_h Count_of_Y sa 2 2 ba 0 1 baba 1 0 Kindly do help me Thanks again, Regards, Sandeep |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a formula dynamically. | Excel Worksheet Functions | |||
Office 2007 Bizarre =@sum() formula BUG - generates mailto: hyperl | Excel Worksheet Functions | |||
Using offset within a formula dynamically | Excel Worksheet Functions | |||
Writing macro results to user defined areas within excel | Excel Discussion (Misc queries) | |||
PPMT formula that generates equal payments | Excel Worksheet Functions |