Jmagenta
05-09-2016, 09:14 AM
I have this table:
16135
I would like to serve as a reference to be able to populate a cell in a different Sheet. I wanted to do this by using a UserForm by having 2 listboxs or comboboxs where you would select the pipe size in one and the pipe type in the other, then by having both selected, picking the cell that correlates with them.
What is even more funner is that available Types depend on Size.
Please upload a workbook with that table in it.
May I suggest that he pipe selecting process begins when the User doubleclicks a cell?
LOCATION
1-1/2"
2"
3"
4"
5"
X
for example, when the User doubleckicks the cell under the "2 inch" heading, only the following list is provided to choose from
Cast Iron Service Wt. Single Hub Push-On Gasket
Cast Iron Extra Heavy Single Hub Push-On Gasket
Cast Iron Service Wt. No-Hub
Copper Type K Brazed
Copper Type L Brazed
Copper Type M Brazed
Black Steel Standard Threaded
Galvanized Standard Threaded
I notice that there is no provision for both pipe type and price in the Takeoff sheets.
Jmagenta
05-11-2016, 07:47 AM
Location you indicated is the linear footage of that spesific size, the sheet is made to sum the feet of pipe then price it out. Normally it would be done using 2 different sheets and manually flipping between the two. I was hoping to add a command button at the bottom of the column to activate the user form, where you would select the size then the pipe type then pull the corresponding price; for example
Location
1-1/12"
2"
3"
4"
5"
6"
8"
10"
12"
1st Fl
100
20
51
76
2nd Fl
50
10
34
21
Total
150
10
54
51
97
Price/ft
$58
$68
$96
$340
$929
$8700
$680
$5076
$17340
$90133
X
X
X
X
X
X
X
X
X
X is where I'd place the button. The userform would only have the two listboxes/comboboxes and an input button;
16160
I just don't know how to make the two boxes communicate together to extrapolate the information.
I have over 20 years in construction, from a toddler handing nails to my dad, to contracting and 14 years in Excel and VBA.
I don't know how you are handling the "Location" field on your Worksheet Forms, but I would use
Location
Type
Size
Qty
Price
(Including
Tx & M/U)
Then I would have a summary or Materials Order Sheet that consolidated the items from all take off sheets.
Pipe Type
Size
total Qty
Cost (w/o Tx or M/U)
And all calculations and prices taken from the hidden sheets in the workbook
From an experienced Excel and VBA user's perspective:
All 27 Worksheets, including the hidden ones, have their own separate copy of the Tishman logo picture. Since this logo is only really needed when printing the sheets, delete all copies, save one, put that one copy on a Print Header sheet and incorporate it by reference when printing.
Never, Never, Never Merge Cells. Except when absolutely necessary on printing sheets to properly format for business purposes.
Excel has three purposes:
To provide data for VBA and Worksheet Formulas
To display data to Users
To Provide Worksheet Forms for Printing
1: Data for VBA and Worksheet Formulas
This data should be kept in the simplest format possible, and should utilize Named Ranges. All these values can be referred to in Formulas by =Sum(A1,B1)*RangeName.
Use empty Columns and Rows ONLY to separate different tables or lists on the same sheet. Never inside a table or list
Example 1: Labor Rates, Taxes,Markups, Etc:
Sheet Name:CommonConstants
Constant
Value
Range Name For illustrative purposes
Fitting Allowance
0.5
Fitting_Allowance
Hanger Allowance
0.25
Hanger_Allowance
Sales Tax on Material
0.08875
Sales_Tax_on_Material OR Material_SalesTax
Labor Rate (Cost)
150.00
Labor_Rate
Lost Time
0.0
Lost_Time
Sales Tax on Labor
0.0
Sales_Tax_on_Labor
Escalation
0.0
Escalation
Markup (Overhead & Profit)
0.1
Markup
NYC Material Price Adjustment
0.0
NYC_Material_Price_Adjustment
Example 2: Materials Tables, Abbreviated
Sheet name: CastIron
Cast Iron, Service Weight, Single Hub, Push-On Gasket Joint
Material price based on Charlotte Pipe & Foundry, dated 01/01/09 (Less 25%)
NYC Material Price Adjustment:
NA
Effective Date:
01/01/09
Last Update:
01/15/09
Pipe
Accessories
Labor
Fittings
Hangers
Pipe Size
Unit Price
Unit Price
Qty Required
Unit Price
Qty Required
Piping
Hangers
Welding/Soldering
2
6.68
3.34
1
1.67
1
60
40
0
3
9.22
4.61
1
2.305
2
50
40
0
4
11.98
5.99
1
2.995
2
35
40
0
5
16.91
8.455
1
4.228
2
30
40
0
6
20.59
10.295
1
5.148
3
25
40
0
8
32.06
16.03
1
8.015
3
15
30
0
10
53.36
26.68
1
13.34
3
15
20
0
12
77.52
38.76
1
19.38
4
15
20
0
15
113.29
56.645
1
28.32
4
10
20
0
Range Name of Red Fonted Cells for this table: CastIron_Standard. (Red for illustrative purposes only.)
The green cells can be duplicated near each table as required and formatted any way desired
NB: the columns for all pipe types on all materials sheets should be identical. Only the Range Name changes. the Depth, or row count of each table is variable. The other material sheets can be named Copper and Other.
Nb: The only table or list formatting you should use is to Bold the headers, increase their Font size, and border the bottom of the header Rows. Use no formatting inside the Tables.
Now that you have the three Materials sheets, go back to the common Constant sheet, or. my preference, a VBA_Constants sheet and add a cross reference list:
Pipe Types
Table Name
Cast Iron, Standard
CastIron_Standard
Cast Iron, Extra Heavy
CastIron_XH
Cast-Iron, SV, Lead
CastIron_SV_Lead
Cast-Iron, XH, Lead
CastIron_XH_Lead
Ductile Iron, XH, Buried
DuctileIron_XH_Buried
List all Pipe types here
Other Names here
Range Name, Red Cells: PipeTypes, Pipe_Types, or Type_Pipe.
Now, when you invoke a Pipe selection UserForm and have the first ListBox with two columns, the User can select from a common Trade name, then the code can use the second (hidden) column of the list box to refer to the list of pipe sizes by Range Name and Table column number. Since all Pipe Type tables are identical in format, one piece of code will work for all types and sizes.
When designing a Workbook like this, don't hide any sheets, columns, rows, or anything else until the design is finalized. I generally format cells with formulas that refer to named ranges and other sheets with one color and other formula cells with another color. when the design is finalized, I will remove all that coloring.
I will also rough out the sheets in Excel Purposes 2 and 3, then finalize the sheets in Purpose 1. I have found that this is the fastest way for me to effectively design a workbook with a minimum of errors and omissions.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.