Hello,
I would like to create a sheet with certain code out of an other sheet with a lot of codes..
To explain it better I'll joint what I mean
So this is the sheet where all the codes would be
I would need to create something like that:
Techniquely all the labour codes (the ones that ends by .02) should be in this sheet
The problem I'm having is that it's not always regular
- Division 01 has 2 but division 02 only has 1 for example.
The division name would be taken of an other sheet with a VLOOKUP
I hope i made it clear enough if not let me know
Thanks a lot for whoever will take the time !
I would like to create a sheet with certain code out of an other sheet with a lot of codes..
To explain it better I'll joint what I mean
| ESTIMATE LABOUR CODES.xlsm | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Cost Code | Description | Cost Type | BudgetAmount | ||
| 2 | 01-0102 | Site Superintendent | L | xxx | ||
| 3 | 01-0122 | Bonding | O | xxx | ||
| 4 | 01-0131 | Courier costs | S | xxx | ||
| 5 | 01-0135 | Liability Insurance .40 / K | O | xxx | ||
| 6 | 01-0137 | Telephone/Data | O | xxx | ||
| 7 | 01-0145 | Builders Risk Insurance | O | xxx | ||
| 8 | 01-0166 | Vehicles | O | xxx | ||
| 9 | 01-0171 | Meal allowances | M | xxx | ||
| 10 | 01-0181 | Accommodations | M | xxx | ||
| 11 | 01-0209 | Container | M | xxx | ||
| 12 | 01-0211 | Consumables | M | xxx | ||
| 13 | 01-5001 | Temporary washrooms | M | xxx | ||
| 14 | 01-5101 | Job site offices | M | xxx | ||
| 15 | 01-5113 | Temporary power hook up | S | xxx | ||
| 16 | 01-5201 | Printing | M | xxx | ||
| 17 | 01-5611 | Temporary fencing (MAT) | M | xxx | ||
| 18 | 01-5612 | Temporary fencing (LAB) | L | xxx | ||
| 19 | 01-6011 | Winter heat (MAT) | M | xxx | ||
| 20 | 01-5004 | Concrete testing | SVC | xxx | ||
| 21 | 02-4203 | Demolition dump fees | S | xxx | ||
| 22 | 02-7401 | Clean up (MAT) | M | xxx | ||
| 23 | 02-7402 | Clean up (LAB) | L | xxx | ||
UPLOAD 1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C23 | C2 | =VLOOKUP(A2,'Cost Code Types'!A:C,3,FALSE) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Cost Code Types'!_FilterDatabase | ='Cost Code Types'!$A$1:$F$391 | C2:C23 |
So this is the sheet where all the codes would be
I would need to create something like that:
| E20-084 ESTIMATE.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 11 | Cost Code | Description | Budget | Spent | Remaining | |||
| 12 | ||||||||
| 13 | 01 | General Requirements | ||||||
| 14 | 01.01.02 | Site Superintendent | xxx | |||||
| 15 | 01.56.12 | Temporary barricades (LAB) | xxx | |||||
| 16 | ||||||||
| 17 | 02 | Demolition | ||||||
| 18 | 02.74.02 | Demolition (MAT) | xxx | |||||
| 19 | ||||||||
Labour Codes for SS | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B17 | B17 | =LEFT(B18,2) |
| C17 | C17 | =VLOOKUP(B17,'Estimate Worksheet'!$A$8:$B$1000,2,0) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Estimate Worksheet'!_FilterDatabase | ='Estimate Worksheet'!$A$67:$Z$1046 | C17 |
Techniquely all the labour codes (the ones that ends by .02) should be in this sheet
The problem I'm having is that it's not always regular
- Division 01 has 2 but division 02 only has 1 for example.
The division name would be taken of an other sheet with a VLOOKUP
I hope i made it clear enough if not let me know
Thanks a lot for whoever will take the time !
