Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing a formula dynamically. mg_sv_r Excel Worksheet Functions 2 August 20th 07 01:10 PM
Office 2007 Bizarre =@sum() formula BUG - generates mailto: hyperl funnybroad Excel Worksheet Functions 5 April 22nd 07 04:04 AM
Using offset within a formula dynamically S Davis Excel Worksheet Functions 6 July 28th 06 03:11 PM
Writing macro results to user defined areas within excel gauss1976 Excel Discussion (Misc queries) 1 July 27th 06 01:35 PM
PPMT formula that generates equal payments ExcelMonkey Excel Worksheet Functions 3 February 15th 05 08:39 PM


All times are GMT +1. The time now is 10:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"