Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advice on a couple of things if possible.
Describing what im after is not going to be easy, please bear with me on
these!! Firstly I was wondering if its possible for a cell to auto populate with a formula if other cells have data added to them. For example, say I type Bob into C3 and Smith into D3, without having to do anything manually my self can I get excel (via VB or whatever) to automatically to populate E3 with a concatenate formula showing C3 & D3 together? Could I then do this for an entire column so when extra lines are inserted, the same thing occurs?? (Fingers crossed this makes sense) Secondly, I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advice on a couple of things if possible.
I understand the first part.
Put a formula in E3 of ... =C3 & " " & D3 Copy that formula down as far as you want. As long as there is nothing in C3 or D3, E3 will appear as a blank cell. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "Chiccada" wrote: Describing what im after is not going to be easy, please bear with me on these!! Firstly I was wondering if its possible for a cell to auto populate with a formula if other cells have data added to them. For example, say I type Bob into C3 and Smith into D3, without having to do anything manually my self can I get excel (via VB or whatever) to automatically to populate E3 with a concatenate formula showing C3 & D3 together? Could I then do this for an entire column so when extra lines are inserted, the same thing occurs?? (Fingers crossed this makes sense) Secondly, I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advice on a couple of things if possible.
1. Insert this event code into the worksheet module.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False n = Target.Row With Me If Range("C" & n).Value < "" _ And Range("D" & n).Value < "" Then Range("E" & n).Value = Range("C" & n).Value & " " & _ Range("D" & n).Value End If End With enditall: Application.EnableEvents = True End Sub 2. Not sure what you need Gord Dibben MS Excel MVP On Thu, 3 Jul 2008 03:07:00 -0700, Chiccada wrote: Describing what im after is not going to be easy, please bear with me on these!! Firstly I was wondering if its possible for a cell to auto populate with a formula if other cells have data added to them. For example, say I type Bob into C3 and Smith into D3, without having to do anything manually my self can I get excel (via VB or whatever) to automatically to populate E3 with a concatenate formula showing C3 & D3 together? Could I then do this for an entire column so when extra lines are inserted, the same thing occurs?? (Fingers crossed this makes sense) Secondly, I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advice on a couple of things if possible.
Thanks a bunch for assisting on part 1.
"Gord Dibben" wrote: 1. Insert this event code into the worksheet module. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False n = Target.Row With Me If Range("C" & n).Value < "" _ And Range("D" & n).Value < "" Then Range("E" & n).Value = Range("C" & n).Value & " " & _ Range("D" & n).Value End If End With enditall: Application.EnableEvents = True End Sub 2. Not sure what you need Gord Dibben MS Excel MVP On Thu, 3 Jul 2008 03:07:00 -0700, Chiccada wrote: Describing what im after is not going to be easy, please bear with me on these!! Firstly I was wondering if its possible for a cell to auto populate with a formula if other cells have data added to them. For example, say I type Bob into C3 and Smith into D3, without having to do anything manually my self can I get excel (via VB or whatever) to automatically to populate E3 with a concatenate formula showing C3 & D3 together? Could I then do this for an entire column so when extra lines are inserted, the same thing occurs?? (Fingers crossed this makes sense) Secondly, I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A couple of questions | Excel Discussion (Misc queries) | |||
Couple of Questions | Excel Discussion (Misc queries) | |||
A couple of questions... | Excel Discussion (Misc queries) | |||
Couple more questions... | New Users to Excel | |||
There are a couple of parts to this ???...Thank you for the help. | Excel Worksheet Functions |