+ Reply to Thread
Results 1 to 10 of 10

Calculate data based on data in another cell

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Calculate data based on data in another cell

    Please take a look at the following:

    2015-03-29 10_11_04-Microsoft Excel - Budget Management.xlsx.jpg

    I have a spreadsheet for calculating finance where every line looks like this. I want to know if there is an easy way to control the calculation of data in the money cells based on a comparison to the current date and the first date of each line. In my example it is the 19th of march. What I mean is, I don't want any of the money cells to have any data, except maybe a "$ - ", unless the current day is on or after that first day. To continue with my example, since today is the 29th of March, which is after the 19th, there should be data in nearly all the cells, as you see in my example. However, a later line in my spreadsheet should have no data yet because the 9th of april hasn't passed, like in the manufactured example below:

    2015-03-29 10_29_07-Microsoft Excel - Budget Management.xlsx.jpg

    Lastly, if the only way I can do this is with adding a formula to each of my cells I don't really want to do that because it would making working with those cells kind of messy and inefficient for me. Is it possible to do that kind of conditional data entry for a group of cells? If so, of course, how do I do it?

    Thank you for the help, I hope this is clear enough.

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Calculate data based on data in another cell

    Assume the first day is in cell A5.
    Then the money cell should have this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then you can just copy it down to all the other rows. Yes, each money cell will need a formula.
    No hard labour and nothing messy about that.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Calculate data based on data in another cell

    Hey TSjallie

    Thanks for the reply but unfortunately even including an if statement as a formula in the cell is messy because there are times where i will need to enter a specific value into each cell and not the same one repeating down a column. So it would be annoying for me to click a million times just to enter some numbers and update my file.

    What i'm asking is if it is possible to do the same formula or method on a group of cells, such that the formula need not be in each cell.

  4. #4
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Calculate data based on data in another cell

    You can not have a formula pushing data to other cells. You can however have a bunch of cells "listening" to a few other cells, but that would still require formulas in that bunch of cells.
    Alternatively, you could have an automatic macro distributing data to other cells based on you entering (or changing) a value somewhere or on a cell being (re)calculated. For this, however, you would need some vba. Question is would you be comfortable with that.
    If that sounds what you're looking for (and I believe it is) and you need any help setting this up, then could you pls upload your workbook (or part of it) with more detailed info on your requirements and what you're trying to achieve.

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Calculate data based on data in another cell

    hmmm....if i made a clone of the sheet and put the data into the clone and then used formulas in the cells of the original could i get the same effect?

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Calculate data based on data in another cell

    Having the same effect depends on what you're trying to achieve, which is still pretty unclear to me.
    But yes, you can have a clone sheet with literal data in it and you can have the original sheet having formulas refering ("listening") to the corresponding cells in the clone.
    That way the original sheet would in fact become a copy of the clone, but I'm not sure if that would take you any further (unless you're importing the data). On the other hand in the original sheets you can also have a mix of formulas; some just collecting data from the clone and others doing calculations either conditional or not.

  7. #7
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Calculate data based on data in another cell

    The clone thing works great...now I need only enter my data into the data sheet and the presented sheet only shows the data based on the formula you gave me above, thanks for your help!

    The Presented sheet:
    2015-03-30 21_34_26-Microsoft Excel - Budget Management.xlsx.jpg

    The Data Sheet:
    2015-03-30 21_36_02-Microsoft Excel - Budget Management.xlsx.jpg

  8. #8
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Calculate data based on data in another cell

    Good to here that it works.
    If you're problem is solved pls mark this thread as Solved.

  9. #9
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Calculate data based on data in another cell

    Sure but uh...how do I do that?

  10. #10
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Calculate data based on data in another cell

    Click "Thread Tools" in the Thread menu (top-right) and select the bottom option "Mark this thread as solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate based on age of data
    By SteveLaird in forum Excel General
    Replies: 10
    Last Post: 10-08-2014, 08:32 AM
  2. Replies: 3
    Last Post: 05-12-2014, 12:41 PM
  3. Replies: 21
    Last Post: 09-20-2013, 01:38 PM
  4. Replies: 5
    Last Post: 09-12-2012, 07:00 PM
  5. Replies: 2
    Last Post: 05-24-2010, 10:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1