1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Basic excel user need help with calculations from dropdown list

Discussion in 'Ask an Excel Question' started by marilynpert, Jan 24, 2018.

  1. marilynpert

    marilynpert New Member

    Messages:
    7
    upload_2018-1-24_14-40-9.png

    Hi I'm a basic excel user. I have managed to create a dropdown list in column B3 to B6. Dependent on what selection is made in these cells I need to calculate from the corresponding rate from cells B11 to B18 multiply the Tonnage amount in cells C3 to C6 and provide a total in Cells C11 to C18 on a daily basis.



    If anyone could help me I'd appreciate it.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    Please upload your workbook with the data in it.

    Narayan
  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Hi marilynpert, can you upload a file? It is easier that way to help you.
    Did you try a SUMIF?
    in C11 = SUMIF ($B$3:$B$6,$A11,$C$3:$C$6)*$B11
    and drag down to C18.
    Is it that what you need?
  4. marilynpert

    marilynpert New Member

    Messages:
    7
    Hi Narayank991 & GraH - Guido. Firstly thank you for replying. I've uploaded the file for you to look at. I did paste in the formula you have provided which worked when the SMZ product was selected from the dropdown menu but when I changed it to a different product from the dropdown menu I didn't get anything. Your help is much appreciated.

    Attached Files:

  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    I am not sure whether this is what you want.

    Narayan

    Attached Files:

  6. marilynpert

    marilynpert New Member

    Messages:
    7
    Hi Narayan thank you for getting back to me and providing your test spreadsheet. The drop down list needs to be available for selection for each day as I noticed that if I choose a different product say on 06/02/18 it also changes the previous day as well. Any idea how to implement the selection for each day?
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    How is it possible ?

    If there is only one dropdown available , how can it be different for different dates ?

    If you want it done using formulae , then either you have dropdowns for each date , or when you are done for one date , copy paste the values on themselves so that the formulae no longer remain for that date.

    If you want it done using VBA , then we can do it so that when ever the dropdown is changed , it affects only those cells which are blank ; once a cell is populated with a result , it will remain unchanged even if a dropdown is changed.

    Narayan
  8. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Hi Narayan, do you think it could work with iterative calculations on? Note, that I'm not a huge fan of these. I only used it once for a colleague who organized a running race. And he needed to track start & finish time for each of the runners. But I'm curious to know your opinion on it.
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    I am not able to visualize how this can be done ; the dates are in different columns , and I am not able to see how a formula can refer to different columns in different iterations.

    When a dropdown selection is changed , it has to leave the already calculated columns alone , and recalculate only for the ones which do not already have a calculated result in them ; is this possible through iterative calculations ?

    Narayan
  10. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Yes, I forgot to think about those different dates. Furthermore it would require some discipline for filling out the sheet. I would not have advised it as a sustainable solution, just wondered about the technique. Thx for sharing your opinion.
  11. vletm

    vletm Well-Known Member

    Messages:
    3,249
    marilynpert
    This is interesting ...
    This would be less challenge to help You
    if You c/would write:
    a) What do You need?
    b) How would You do those 'manually'?
    Here some samples with calculations ... from dropdown.

    Attached Files:

    Thomas Kuriakose and Lasantha like this.
  12. marilynpert

    marilynpert New Member

    Messages:
    7
    Hi everyone thank you for your input I have been very busy and have only just managed to have a look at your responses. I have attached the spreadsheet which I have copied the drop down menu for each day which works well until as an example if I was to select the same product SMZ against each machine it does not add each calculation together in cell C11. Not sure if this is possible. Any help or suggestions would be appreciated.

    Attached Files:

  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  14. marilynpert

    marilynpert New Member

    Messages:
    7
    Hi Narayan Yes thank you so much exactly what I was after, your help is much appreciated. Hopefully my Director will be happy with it as well.
  15. marilynpert

    marilynpert New Member

    Messages:
    7
    Hi Narayan If I could get your assistance again with another spreadsheet I'd appreciate it. We have plant machinery and will be recording the current hours the machine has operated on a weekly basis. I have also included the maintenance interval required for each machine. What I need on the spreadsheet is able to record the hours for that week and identify once the hours reach when the next service is due. Also can you use a formula to add to the date 7 days instead of doing it manually?

    Marilyn

    Attached Files:

  16. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    Please mention the following :

    1. All the cells which should have formulae in them

    2. If possible , mention the output values in those cells , so that we can verify whether the formula outputs the correct values.

    3. What are the dates in cells E1 and F1 ?

    4. Are the service due dates to be based on 24 hours a day ?

    Narayan
  17. marilynpert

    marilynpert New Member

    Messages:
    7
    Hi Narayan
    I will try to explain more clearly what I'm trying to achieve.
    1. The cells likely to have formula in them are column C, D & E.
    3. The date from cells E onward will be the weekly dates where the hours the machine has done for the week will be recorded.

    What I'm trying to achieve is to record on a weekly basis the hours each machine has worked. This then will need to be added to the current hours column E. Once the current hours column E reach the next service due amount column D this the information I need to be able to identify.

    The service interval hours is purely for information only as to how often the machine needs to be serviced.

    Once the machine has reached the next service due figure column D and has been serviced this figure will then be transferred to the last service hours column C and the whole process is repeated again.

    FYI plant machinery is serviced on how many hours the machine has worked as opposed to a motor vehicle is serviced on how many kilometers it has travelled. Hope this helps a little and makes sense.

    Attached Files:

  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,075
    Hi ,

    I am still not fully clear on what you wish to do.

    1. Which cells have data entered in them ?

    Are these columns B , C and E , F , G and beyond ?

    If columns B and C are entered , column D can be calculated.

    2. When you enter data in column E , there are two possibilities - either the value entered already exceeds the Service Due value in column D , or it is less than the Service Due value.

    What is supposed to happen in the first case ?

    3. After the value in column E has been entered , suppose values are entered in columns F , G and beyond. There are 3 possibilities -
    • the sum of the value in column E and values entered in columns E , F , G and beyond is still within the Service Due value.
    • the sum of the value in column E and values entered in columns E , F , G and beyond is equal to the Service Due value.
    • the sum of the value in column E and values entered in columns E , F , G and beyond exceeds the Service Due value.
    What is supposed to happen in each of the above 3 cases ?

    4. If you want that periodically , the value in column E should replace the value in column C , then formulae cannot do the job ; you will need to use VBA.

    Is this acceptable ?

    Narayan

Share This Page