• 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...

  • 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

marilynpert

New Member
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.
 
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?
 
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.
 

Attachments

  • Tonnage-Hourly Template.xlsx
    11.8 KB · Views: 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?
 
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
 
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.
 
Hi Narayan, do you think it could work with iterative calculations on?
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
 
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
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.
 
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.
 

Attachments

  • Copy of TEST3.xlsx
    13.7 KB · Views: 1
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.
 
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
 

Attachments

  • test1.xlsx
    10.4 KB · Views: 1
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
 
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.
 

Attachments

  • test1.xlsx
    10.5 KB · Views: 4
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
 
1. Data will be entered on a weekly basis from columns F and beyond. This data will be added to column E. Column B will not change and no data will be entered into this column.

2. If the value exceeds then this will alert us that the plant needs to be serviced. Once the service has been completed the value will then need to be transferred to column C. Column D will need to update as well, the value of column C plus the service interval hours which will be the new hours of when the next service is required.

3. When the value in column E equals or exceeds value in Column D then the plant will need to have a service completed. Once the service is completed the value in column E will then need to transfer to column C. Then in column D the value in column C plus the value in column B needs to be shown.

4. Yes this will be required periodically. Not sure what a VBA is but I'm open to your suggestions.

Again I appreciate your help and assistance.
 
Hi ,

See the attached file.

1. Try entering a value in column E cells which is greater than the value in column D cells.

2. With the value in column E well within the value in column D , enter values in column F and beyond.

Once you confirm that this is OK , we can decide how the summed up value in columns E through N will be transferred to the cell in column C.

Narayan
 

Attachments

  • Test.xlsm
    16.6 KB · Views: 5
Hi Narayan I've opened up your attachment but there seems not be to any formulae in any of the cells?
Hi ,

You are right ; there are no formulae used ; what ever is supposed to happen will be using VBA code.

Did you try out the two actions I had suggested you carry out to verify whether things work the way you want them to ?

Narayan
 
I'm not sure what you are exactly asking for but I have put in some of the hours of the machines. As the values in column E will be constantly changing on a weekly basis depending on what input is put in Column F and beyond I have left this blank. Column E needs to update each week as the new value is put in from Column F and beyond.

If this is not what you're asking can you explain in more detail please.
 

Attachments

  • Copy of Test.xlsm
    16.8 KB · Views: 4
Hi ,

Two points :

1. I understood that column E would have the Current Hours value ; in the workbook you have uploaded , the values which have been entered in cells F14 , F16 , G14 , G16 should possibly have been entered in column E ; is this correct ?

2. I understood that columns F and beyond would have the weekly usage values , which would be much smaller than the values entered in column E ; is this correct ?

Apart from the above , when you entered the values in F14 and G14 , or in F16 and G16 , did you observe any message being displayed ?

Narayan
 
Hi

1. Yes as you enter the values in column F14 it should update the value in column E14. If a value is then entered in cell G14 then the value in Column E14 should update to that value. The values from column F forward will increase as the machine works each week the hours of the machine will increase.

2. No the values from columns F and beyond will increase in values. Column E represents and should reflect where the machine is at that particular stage depending on the values entered in columns F and beyond.

3. no message displayed in any cells.

I hope I'm making sense.

Marilyn
 
Back
Top