Creating a Quote sheet

SimplyKry

New Member
Joined
Jan 6, 2014
Messages
4
Ok, I will try to give as much information as possible. I am an expert personal user for excel, but apparently a complete dummy when it comes to professional or "commercial" use.

My husband and I are starting a business for servicing commercial appliances. The customers have the option for an annual service plan based on the quantity of a specific piece of equipment (the customer may have multiple items on each plan)...

For example, a place has a dishwasher, a mixer, and an oven, and wants to have a yearly service plan.
I have a sheet that lists each item (A), the item type (B), the quantity (C), frequency of service (E), and the annual price (F). My husband will then go through the sheet "PRICE MATRIX" and enter a quantity into column C next to all items that are to be included. I already have the formulas that give us a price with payplan information etc.

QUESTION:I want a separate sheet "QUOTE" that will show ONLY the items that have a quantity in column C other than (Blank) in a list form with all the information above. Columns A,B,C,E, & F. I don't want blank lines, just the information which has a quantity.

I have read many of the other posts, but honestly, it all sounds like gibberish. I am not sure how to work the codes I see people giving out, and I have done much research. I can get to where the code needs to be put in, but I don't know what else to do with it to make it do what I need it to. So as much detail as possible would be great... If we need to PM or I can send you the EXACT sheet, that is ok with me.
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
Welcome to the board!

I suggest you look into pivot tables or vba macro. Once you have the data source, you can create a pivot table and put in conditions. The annoying thing is that you would have to refresh your pivot table everytime to reflect the changes. Better if you post a sample of your data and the end result and someone can maybe create a vba solution for you.
 

SimplyKry

New Member
Joined
Jan 6, 2014
Messages
4
This is what I was afraid of. I have no idea what either of those are...

"PRICE MATRIX" Sheet

ABCEF
OVENSTANDARD2MONTHLY$240
RANGEGAS1QUARTERLY$40
DISHWASHERELECTRIC
TOASTERPOPUP1WEEKLY$520

<tbody>
</tbody>




I want the Quote sheet to show this:

"QUOTE" Sheet
ABCEF
OVENSTANDARD2MONTHLY$240
RANGEGAS1QUARTERLY$40
TOASTERPOPUP1WEEKLY$520

<tbody>
</tbody>
 

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
I don't know enough about Pivot tablets to be of help but I know this can be done.

I was thinking how about you referernce the Price Matrix sheets. Give it a defined large range. On the Quote sheet you can use the macro recorder and record yourself filtering the Quantity greater than 0. This will filter to what you want. With the macro recorded, you can assign it to a button or keyboard short cut and run the macro everytime you or your husband update the price matrix sheet. Would that work for you?

The macro code should look something like this.

C1 is the quantity column header
change this range to a larger range ex: "$A$1:$E$5000"

Code:
    Range("C1").Select
    ActiveSheet.Range("$A$1:$E$5").AutoFilter Field:=3, Criteria1:=">0", _
        Operator:=xlAnd
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

Try this:-
Code:
[COLOR=navy]Sub[/COLOR] MG07Jan50
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Price Matrix")
    [COLOR=navy]Set[/COLOR] Rng = Sheets("Price Matrix").Range("C:C").SpecialCells(xlCellTypeConstants)
[COLOR=navy]End[/COLOR] With
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        c = c + 1
        Dn.Offset(, -2).Resize(, 5).Copy Sheets("Quote").Range("A" & c)
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 

SimplyKry

New Member
Joined
Jan 6, 2014
Messages
4
Try this:-
Code:
[COLOR=navy]Sub[/COLOR] MG07Jan50
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Price Matrix")
    [COLOR=navy]Set[/COLOR] Rng = Sheets("Price Matrix").Range("C:C").SpecialCells(xlCellTypeConstants)
[COLOR=navy]End[/COLOR] With
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        c = c + 1
        Dn.Offset(, -2).Resize(, 5).Copy Sheets("Quote").Range("A" & c)
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Thank you Mick and Shy, but please be patient with me when I ask... What do I do with this codes? Do I just put them in the "code box"? And if so, on which page? The matrix or the quote sheet?
 

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484

ADVERTISEMENT

hold ALT + F11 to get into the VBA environment

Click Insert - Module and paste the code in

Back in Excel hold ALT + F8 and you should see the macro. You can rename the macro to something else instead of Sub MG07Jan50() ex: Sub macrotest()
 

SimplyKry

New Member
Joined
Jan 6, 2014
Messages
4
I am so sorry.. but I still dont understand. I did what you said, but it does not show anything. :( Maybe this is just not something I am going to be able to do?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try running through this again:-
To Save and Run code:-
Copy code from Thread
In Data sheet , Click "Alt+F11",:- Vb Window appears.
From the Vb window toolbar, Click "Insert" , " Module":- New Vb window appears .
Paste Code into this window.
Close VB Window
On Active sheet select "Developer" tab.
From Ribbopn , select "Macro":- Macro dialog box Appears.
Select Code Name from list.
On right of Dialog Box , Select "Run".
The sheet should now be Updated.
Regrds Mick
 

badphnx1

New Member
Joined
Oct 10, 2015
Messages
2
Mick, you are the best! Any chance on getting a variation (or instructions to make one) for creating a quote sheet from multiple sheets?

Also, Id love to learn just enough to understand the code and learn to make these on my own. Any suggestions for where to start?

Thanks! ~AP
 

Forum statistics

Threads
1,137,062
Messages
5,679,392
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top