Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Could someone please tell me if there is a formula (and what the formula would be please ) for me to be able to do the following:

    If data is entered into a cell I want it to add another cell with existing data with it. So, the cell will be blank. If a figure is entered in to the cell, I want it to automatically pick up data that is in another cell on the same spreadsheet.

    Does anyone have this magic answer? I've tried formulas starting 'IF' and 'COUNTIF' but they don't seem to work...although I may not be writing the formula correctly.

    Thanks ever so for your help

    Penny

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you give a specific, detailed example of what you want to accomplish?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Penny

    I think (if I read you question right) be looking for an IF statement, have a look at the attachment to see if this is what you are looking for
    Jerry

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Wayland, Michigan
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Does the response from the other cell need to change based on what value they enter? What are the values you want entered. If the list is limited then maybe a Vlookup function and table would be easier.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I know of no way to do what you're asking with a formula, but it can be done in vba. *Below is code that I modified from www.ozgrid.com. *Copy and paste it into your Worksheet_Change event.

    I hope this helps.

    Mark Trevithick

    '---------------------------------------------------------------------------------------
    ' Procedure : Worksheet_Change
    ' Author : http://www.ozgrid.com/VBA/run-macros-change.htm
    ' Date : December 03, 2009
    ' Purpose : Add Target plus Offset Cell
    '---------------------------------------------------------------------------------------
    '
    'modified by Mark Trevithick

    Private Sub Worksheet_Change(ByVal Target As Range)

    'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    'Set your Range to the appropriate Range _
    A Named Range would be best

    'If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    If Not Intersect(Target, Range("myRange")) Is Nothing Then

    'Ensure target is a number before multiplying by 2
    If IsNumeric(Target) Then

    'Stop any possible runtime errors and halting code
    On Error Resume Next

    'Turn off ALL events so the Target * 2 does not _
    put the code into a loop.
    Application.EnableEvents = False

    'Add the Target plus the cell to the right
    Target = Target + Target.Offset(0, 1)

    'Turn events back on
    Application.EnableEvents = True

    'Allow run time errors again
    On Error GoTo 0

    End If

    End If

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub




  6. #6
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jezza. Thanks for your response. This pretty much sounds like what I want to do but when I type in the amount it seems to delete the formula I've entered....why would that be?

    Thanks everyone else...if I can't get this to work from Jezza I will come back to you!

    Penny

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by PennyToulson View Post
    Jezza. Thanks for your response. This pretty much sounds like what I want to do but when I type in the amount it seems to delete the formula I've entered....why would that be?

    Thanks everyone else...if I can't get this to work from Jezza I will come back to you!

    Penny
    I think I see what you are doing now! In response to what you are trying to do then you can't do what you are doing as you will be overwriting your formula when you type a value, I think using my method( that will require an interim cell in your design) to be the way forward.

    If you have any problems it may be worth attaching a cut down version of your workbook with any sensitive data removed to your next reply and we can work on it for you.
    Jerry

  8. #8
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Jezza

    That sounds great. Thank you

    I've attached the spreadsheet and basically when I populate vehicle 1 in January with a figure I want it to add the 'service only' figure to the figure I input....are you able to help me with that?

    Thanks ever so much - they will think I'm clever!

    Penny[attachment=87013:RM Cost Comparison 2010.xls]
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by PennyToulson View Post
    Hi Jezza

    That sounds great. Thank you

    I've attached the spreadsheet and basically when I populate vehicle 1 in January with a figure I want it to add the 'service only' figure to the figure I input....are you able to help me with that?

    Thanks ever so much - they will think I'm clever!

    Penny[attachment=87013:RM Cost Comparison 2010.xls]
    What exactly you are trying to achieve? do u want to simply add the cost or want it to auto populate depends on vehicle/month??
    Attached Files Attached Files
    Regards
    Prasad

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by PennyToulson View Post
    Hi Jezza

    That sounds great. Thank you

    I've attached the spreadsheet and basically when I populate vehicle 1 in January with a figure I want it to add the 'service only' figure to the figure I input....are you able to help me with that?

    Thanks ever so much - they will think I'm clever!

    Penny[attachment=87013:RM Cost Comparison 2010.xls]
    Hi Penny

    I am assuming that everytime you service a car there will always be a service charge

    In the worksheet "Cost Breakdown" in cell O3 type this formula =SUM(B3:M3)+COUNTA(B3:M3)*N3 and copy down
    Jerry

  11. #11
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Morning Jezza

    That's great thank you ever so much!

    Out of interest is there any way we can get 'N3' to add to the monthly total so that it sets out each month as to how much in total has been spent or will this totally over complicate things?

    Also could you please tell me what the formula would be to make cell R3 autopopulate with the figure in Q3 each time another month is populated....?

    Thanks so far for your help!

    Penny

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Penny

    Sorry for the delay in getting back to you so late but I have been tucked up at work for most of the day. Your explanation of your requirement is not very clear to me as I am not actually sure what you want to do.

    Values in range N3:N10 seem to be arbitarary values that look like the cost of a vehicle service, Yes? I am assuming that when Vehicle 1 gets a service in January (B3) I add the value B3 + N3

    Now you want:

    Out of interest is there any way we can get 'N3' to add to the monthly total so that it sets out each month as to how much in total has been spent or will this totally over complicate things?
    Monthly total of what? maybe if you mocked up an excel worksheet with comments it would be easier to understand the business requirement
    Jerry

  13. #13
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Jezza

    No need to apologise

    OK, when I populate cell B3 with an amount, i.e. 50 - is there any way we can get the amount in cell N3 (149) to add itself to the amount added in B3 thereby making a total of 199?

    Also, when cell B3 is populated, is there any way of getting cell R3 to add another amount from Q3 (356.23) as the data in cell Q3 is the regularly monthly cost and the amounts being entered into columns B to M are the additional unexpected costs.

    Hope this makes sense? The spreadsheet is attached to an earlier posting from me.

    Thanks again for your help.

    Penny

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Hi Penny
    I'm coming late to this thread, and I might not be on the same wavelength. But does the attached file do what you want?[attachment=87190:RM Cost Comparison 2010.xlsx]
    As a general thought - I set up the formulas for this type of spreadsheet to include the empty cells. Then as the data is entered, the formulas update automatically.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi WebGenii

    Thanks for your reply. No, sorry that's not quite what I want to do, but thanks anyway.

    I think I'm going to leave it there. With everyone's help I've done the majority of it.

    Thanks again. As always - very helpful

    Penny
    x

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •