We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x
Private

Restructure Sheet Columns

High Priority
90 Views
Last Modified: 2020-06-02
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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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

Author

Commented:
Martin,

I just notice that the subtotal on the OQ is adding the subtotal on Qty and not Total price.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
Oh ok. I didn’t realize that the license discount was needed.  
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
So is it acceptable to put it between SI Key and Service Item Status?

Author

Commented:
So is it acceptable to put it between SI Key and Service Item Status? 
Yes
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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. 



Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome. I will make those changes in the next question.

Author

Commented:
Martin,

I didn't see the Org Name on the User form?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
Ok. Thank you for clarifying.

Author

Commented:
I found some issues when adding quotes for RefID 8,9 & 10.  Please see attachment for details. 29182596.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Should Monthly Price and/or Quantity be required with a Total Price Discount SI?

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In that case should P40 just contain the Total Price value entered in the userform rather than being a formula?

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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".

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
Ok, understood.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
ok. Thanks Martin.

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
Thanks Martin!  I will retest

Author

Commented:
Martin,

The latest workbook no longer has the Org name on the user form
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
OK, I thought I saw it there on a previous workbook.  I will continue with the testing.

Author

Commented:
Martin,

My findings are in the attached under the issue tab.

29182596b.xlsm

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
There's no "Issues" tab in the workbook you posted,

Author

Commented:
its on the left side of the OQ.  You may have to drag the scroll bar to the left to make it visible.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I still don't see it.
2020-06-02_08-11-15.png

Author

Commented:
Try this one
29182596b.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
OK, that has it.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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"

Author

Commented:
  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.


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

2020-06-02_08-48-32.png

Author

Commented:
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.

Author

Commented:
I just tried it and got the same issue
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's a video.
2020-06-02_09-38-40--1-.mp4

Author

Commented:
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 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How would you like it to work?

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
"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?

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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?

 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:
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
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.


Author

Commented:
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.
 

Author

Commented:
I will close this question out and open up a new one.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.