Link to home
Start Free TrialLog in
Avatar of Sharmal Butler
Sharmal ButlerFlag for United States of America

asked on

Restructure Sheet Columns

Experts,

I need help rearranging the column headers on all the sheets to match the original Quote sheet headers.  In addition, I need to add a new column call Org Name to the user form and have it function exactly like the license type and license 2..
29182230b.xlsm
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

It looks like you left out the 'License Discount' column in your new arrangement. Should I put it between V and W?
Avatar of Sharmal Butler

ASKER

It looks like you left out the 'License Discount' column in your new arrangement. Should I put it between V and W?
Since we have the Discount % showing in col O we don't need the License Discount column anymore
Martin,

I just notice that the subtotal on the OQ is adding the subtotal on Qty and not Total price.
Since we have the Discount % showing in col O we don't need the License Discount column anymore
Yes, we do. It's required when there's a License Discount. If you don't want to see it all the time we can hide it along with the green columns.

I just notice that the subtotal on the OQ is adding the subtotal on Qty and not Total price.
In the workbook I last created for you that's not true.

By the way, this is not the simple add-a-new-column change that I thought it was and it will take me several days to do and test. You will need to spend more time on testing too.
Oh ok. I didn’t realize that the license discount was needed.  
So is it acceptable to put it between SI Key and Service Item Status?
So is it acceptable to put it between SI Key and Service Item Status? 
Yes
In this version I
  • Added the Org Name column to the worksheets and reordered most of the rest of the columns
  • Added the Org Name column to the List sheet
  • Grouped columns V to Z
  • Changed all column and ListView references in the code to match the new order (this involved MANY changes to the code)
  • Fixed a couple of bugs

I tested everything I could think of but there's a lot going on in the workbook and I may have missed something so test everything.

A question - Are the "Original Quotes" and "Raw Data" captions that appear in the first row of their respective sheets of any use to you?

A couple of suggestions for the future
  1. Currently when you hover over the license type combobox in the userform you see a tip that says "Select or type in the License Type" and that could be changed so that it shows the full description of the license type since many are visually cut off until you click on the dropdown arrow.
  2. If you have a very long comment, all of it will not show up in the userform's comment box and the Comment column in the various sheets will be annoyingly wide. I suggest that a vertical scroll bar in the userform's comment box would be useful and that the comment in the various sheet be changed to multi-line with a fixed width of perhaps 40.
29182596.xlsm
Thanks Martin.

A question - Are the "Original Quotes" and "Raw Data" captions that appear in the first row of their respective sheets of any use to you?
No, these captions have no value to me

 suggest that a vertical scroll bar in the userform's comment box would be useful and that the comment in the various sheet be changed to multi-line with a fixed width of perhaps 40.
I’m ok with this also thank you for the new Col add and the work you put in to rearrange the columns. 



You're welcome. I will make those changes in the next question.
Martin,

I didn't see the Org Name on the User form?
We had a discussion where I said "I'd like to add the Org Name column to the various sheets but not actually do anything with that column in this question" and what you are seeing reflects that.

Basically I want to make sure the rearrangement works before we go on.
Ok. Thank you for clarifying.
I found some issues when adding quotes for RefID 8,9 & 10.  Please see attachment for details.29182596.xlsm
I'll look at everything you mentioned but I don't agree with your "Font size does match rows 4 - 41" comment. Every cell in column 'D' that has data is formatted as Arial Narrow 10.
I'll look at everything you mentioned but I don't agree with your "Font size does match rows 4 - 41" comment. Every cell in column 'D' that has data is formatted as Arial Narrow 10. 

you right!  So scratch that issue.  Although, it looks bigger than the others, apparently my eyesight is deceiving me.
Should Monthly Price and/or Quantity be required with a Total Price Discount SI?
Should Monthly Price and/or Quantity be required with a Total Price Discount SI? 
Total price discount only requires the discount% and the  the total price row its being applied to
In that case should P40 just contain the Total Price value entered in the userform rather than being a formula?
In that case should P40 just contain the Total Price value entered in the userform rather than being a formula?
Unless I misunderstood your ask. I thought selecting the Total price checkbox triggers the total price row the discount should get applied to. The discount of 5% with no Qty would get applied to either P38 or P39 or both. If one then the formula would look like this =SUM(P39)*O40/100 and if both =SUM(P38, P39)*O41/100
I'm talking about the 3rd SI in quote Q-99999999. What you're describing when you say "get applied to either P38 or P39 or both" sounds like the 4th SI which is a License Discount and it's formula is =SUM(P38, P39)*O41/100. So please consider the 3rd SI again (which I believe is a Total Price discount rather than a License Discount) and tell me if any other field is required and what should be in P40. I'm assuming it should not be a formula but rather the Total Price value directly from the userform
I'm talking about the 3rd SI in quote Q-99999999. What you're describing when you say "get applied to either P38 or P39 or both" sounds like the 4th SI which is a License Discount and it's formula is =SUM(P38, P39)*O41/100. So please consider the 3rd SI again (which I believe is a Total Price discount rather than a License Discount) and tell me if any other field is required and what should be in P40. I'm assuming it should not be a formula but rather the Total Price value directly from the userform

Still a bit confused, if this is a total price discount count.  Then what are the steps to enter to calculate in the userform if this is a total price discount?

 I never once put in a total amount into the total price field on the userform. I thought the steps to get the total price discount is doing the following:
1. Leave Order term blank
2. Leave Monthly Price blank
3. Leave Qty blank
4. Within discount frame.  Total price is checked
5. click on "Create License Discount" button
6. a list of SI are presented
7. check the  SI from list you want discount applied to
8. add the discount % in the discount field.

What am I missing here. If this what you mean by no formula and total price value from the userform then yes, there should not be a formula.


I may have become confused but I'm assuming there are there three discount types
  • Quantity Discount - based on quantity
  • Total Price Discount - based on total price of the SI being entered or updated and you would not click the "Create License Discount" button, but rather the "Add Service Item" or "Update" button to create or modify it
  • License Discount - based on total price of one or more SIs and you would click the "Create License Discount" button in order to create it.

If there are only two: Quantity and License, then the "Create License Discount" button is not needed and instead checking the "Total Price" option would display the secondary form that's used to create the "License Discount".
I may have become confused but I'm assuming there are there three discount types
  • Quantity Discount - based on quantity
  • Total Price Discount - based on total price of the SI being entered or updated and you would not click the "Create License Discount" button, but rather the "Add Service Item" or "Update" button to create or modify it
  • License Discount - based on total price of one or more SIs and you would click the "Create License Discount" button in order to create it.
If there are only two: Quantity and License, then the "Create License Discount" button is not needed and instead checking the "Total Price" option would display the secondary form that's used to create the "License Discount".

There is only two. 
  • Quantity discount -based on quantity
  • License Discount - based on total price of one or more SIs and you would click the "Create License Discount" button in order to create it.
However, I need in the secondary form the SI row selection list with the Total price to determine which SI total price row gets the discount.
Okay, I will remove the "Create License Discount" button and move its functionality to the "Total Price" option button. In other words when you check that option you will be shown the "Select Service Items" subform.
Ok, understood.
Please check this version in which I've removed the Create License Discount button. Please note that you now need to enter the discount for the Total Price discounts in the maintenance form rather than the sub-form.

I've corrected the problems with the breakdown of the Total Price discounts, but note that the Total Price for a Total Price discount in the RAW_DATA is now a value rather than a formula. I needed to do that because of a problem with the design of the Total Price discount that I guess should be fixed. The problem is that we currently place a comment in the Total Price discount like "License Discount based on rows 38 and 39", but we have the ability to add or delete SIs prior to those rows and the comment (or comments) below the new or deleted SIs are not being updated. Having the Total Price in the RAW_DATA be a value allows us to work around that problem.
29182596a.xlsm
ok. Thanks Martin.
In testing the Add quote the following issues occurred when I clicked on the "Add Service Item" button.

Note that the validation verification for the amount was correct, but when I clicked on the "Yes" to proceed button I encountered issues. see attachment run-time error tab sheet for screen shot of issue.
29182596a.xlsm
From the pictures it looked to me like you were trying to add a "plain vanilla" SI with no Total Price (or Quantity) discount and the discount sub-form showed up anyhow which caused problems. My apologies for that. My focus was on the Total Discount discount process and I didn't test anything without one.

In this version I
  • Corrected that problem
  • Modified the loading of the listview a bit so that columns 'T' and 'U' show up
  • Corrected a bug where if either of the discount options were selected and then unselected, some fields were left disabled when they shouldn't be
  • Corrected a bug where if frmDiscount was shown but no SIs were selected, the code went head and added the SI anyhow,
29182596b.xlsm
Thanks Martin!  I will retest
Martin,

The latest workbook no longer has the Org name on the user form
It never had it. Here's what I said above:
We had a discussion where I said "I'd like to add the Org Name column to the various sheets but not actually do anything with that column in this question" and what you are seeing reflects that.

Basically I want to make sure the rearrangement works before we go on.
OK, I thought I saw it there on a previous workbook.  I will continue with the testing.
Martin,

My findings are in the attached under the issue tab.

29182596b.xlsm

There's no "Issues" tab in the workbook you posted,
its on the left side of the OQ.  You may have to drag the scroll bar to the left to make it visible.
I still don't see it.
User generated image
Try this one
29182596b.xlsm
OK, that has it.
This is an add to apply 25% discount to total price on Row 60 col P of the OQ sheet.  After entering the  discount the selection from for total price did not show.
  1. Check "Add New"
  2. Select a License Type
  3. Check "Total Price"
  4. Enter 25  NOT .25 in "Discount %"
  5. Click "Add Service Item"
  1. Check "Add New"
  2. Select a License Type
  3. Check "Total Price"
  4. Enter 25  NOT .25 in "Discount %"
  5. Click "Add Service Item"

This what I got.

User generated image
In the maintenance form I selected the single SI in quote Q-21212121, followed the steps I mentioned above and got this.

User generated image
Not sure why I am getting something different as i followed the exact same steps.  Also, I  notice that as soon as an entry is made the form doesn't like, it produces a run-time error.  I will try to capture that in screen shot next time.  

I will clear everything out and try again.

I just tried it and got the same issue
Thanks for the video and yes it works doing it that way, but I have hundred of rows of data.  so I will have to use the search button to locate the SI row, highlight and then add 
How would you like it to work?
I would like to be able to select total price, enter the percent the way you have it now, but once the percent has been enter a selection box showing all SI rows under the same quote appears at which I can select the SI row the discount should be applied to.
Actually if you just wanted to add a new SI to an existing quote and you couldn't find the quote by scrolling, you would still have to  "use the search button to locate the SI row, highlight and then add". I assume however that adding a discount or a new SI to an existing quote is a rare occurrence so in that respect is t a problem?
"I would like to be able to select total price, enter the percent the way you have it now, but once the percent has been enter a selection box showing all SI rows under the same quote appears at which I can select the SI row the discount should be applied to."

How do I know which quote to use?
How do I know which quote to use?

When you had the create button and it worked.  

If the only way is to search and do this then I have to live with that.
When you had the create button and worked.
The process was the same in that you still had to first select an SI in the maintenance form.
The process was the same in that you still had to first select an SI in the maintenance form

But I never had to search for the SI that I wanted to apply the discount to.
Well if you were in the process of creating the SIs for a new quote then an SI in the quote in the userform would already be selected so you didn't need to find it.
Well if you were in the process of creating the SIs for a new quote then an SI in the quote in the userform would already be selected so you didn't need to find it.
What is the difference between creating a new and adding to and existing. I have to select add new in both cases when creating the SI for a new quote or Adding to the an existing quote.  I have to enter the quote that I am working with be it new or existing.  Am I miss understanding what you are saying?

 
Okay, imagine that today you receive a new quote. To enter it in the workbook, you would open the workbook to the OQ sheet and click the Quote Maintenance button which would open up with the first SI in the userform selected. Since this is a new quote you would check Clear Form and then Add New. You would then enter the data and click the Add Service Item button. At that point if everything you entered was valid and you indicated that you wanted to add it, the SI would be added to the OQ sheet and then selected in the userform. Since the SI in the new quote is selected, to add a second SI all you need to do is to click Add New, enter the few new values and add that SI just like you did the first one. If one of the SIs you need to enter is a Total Price discount, you would logically add it last and the process for doing that is just like the  previous SIs plus the addition of selecting the pertinent SIs from the Select Service Items subform.

But let's say you started adding the SIs late in the workday and you didn't have time to add the discount, so you close the maintenance userform and go home. The next day when you want to add the discount SI, the code has no idea which quote you want to add it to so you have to find/select one of the new quote's SIs first.

Do you see the difference?
What I don't understand is why would the code be structured that way?

So basically I have to complete the an entire quote to include any discounts without closing out of the userform or I have to start over
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I just thought of this as a possible change (in a new question) that might help. Currently when the maintenance userform is opened the first SI is selected. What if I changed that so that the fist SI in a quote that has the highest RefID is selected. That way in the go-home-come-back-next-day scenario, an SI in the new quote would be selected and you wouldn't have to search for it.
Ok. I think I understand.  I went back and cleared out everything and tried again and it worked with no problems.  Thank you for the thorough explanations. I didn't have any other issues.


I just thought of this as a possible change (in a new question) that might help. Currently when the maintenance userform is opened the first SI is selected. What if I changed that so that the first SI in a quote that has the highest RefID is selected. That way in the go-home-come-back-next-day scenario, an SI in the new quote would be selected and you wouldn't have to search for it.
Yes, that would be helpful.
 
I will close this question out and open up a new one.