Creating a Quote sheet

[SimplyKry]

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.

 

[shyy]

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]

This is what I was afraid of. I have no idea what either of those are...

"PRICE MATRIX" Sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]OVEN[/TD]
[TD]STANDARD[/TD]
[TD]2[/TD]
[TD]MONTHLY[/TD]
[TD]$240[/TD]
[/TR]
[TR]
[TD]RANGE[/TD]
[TD]GAS[/TD]
[TD]1[/TD]
[TD]QUARTERLY[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD]DISHWASHER[/TD]
[TD]ELECTRIC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOASTER[/TD]
[TD]POPUP[/TD]
[TD]1[/TD]
[TD]WEEKLY[/TD]
[TD]$520[/TD]
[/TR]
</tbody>[/TABLE]




I want the Quote sheet to show this:

"QUOTE" Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]OVEN[/TD]
[TD]STANDARD[/TD]
[TD]2[/TD]
[TD]MONTHLY[/TD]
[TD]$240[/TD]
[/TR]
[TR]
[TD]RANGE[/TD]
[TD]GAS[/TD]
[TD]1[/TD]
[TD]QUARTERLY[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD]TOASTER[/TD]
[TD]POPUP[/TD]
[TD]1[/TD]
[TD]WEEKLY[/TD]
[TD]$520[/TD]
[/TR]
</tbody>[/TABLE]

 

[shyy]

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"

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

 

[MickG]

Try this:-

[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]

Try this:-

[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]

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]

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]

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]

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