Populating a Cell with a Table

[Jmagenta]

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.

[SamT]

What is even more funner is that available Types depend on Size.

Please upload a workbook with that table in it.

[Jmagenta]

here you go

16159

[SamT]

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]

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.

[SamT]

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.