Return Value based on value in previous cell

[clemons171]

This may not be possible. What I am looking for is this:

I need a macro that returns values in the cells to the left of the white cells that have text. The value i need returned is the value in the previous darker blue cell. I have included what I would like the end result to look like. the catch is that the amount of rows will vary as you go down the sheet....so it would need to look for the previous cell with that color or the word "Division". Thank You

BEFORE:

	Division 05 - Metals
	Misc. Metals
	Stair - Pan Fill with Landings
	Stair Railings - Interior
	Wall mounted railing
	Bollards
	Head Knockers
	Misc. Metals
	Division 06 - Wood, Plastics, and Composites
	Rough Carpentry
	Blocking of Door Jambs in CMU
	Blocking around Storefront at CMU
	Plywood Backerboards
	Door Installation
	Specialty Installation
	Division 07 - Thermal and Moisture Protection
	Siding
	4" Fiber Cement Siding
	Waterproofing and Moisture Barriers
	Vehicular Traffic Coating in Garage?
	Building Insulation
	Insulation under conditioned space
	Misc. Spray Foam Throughout as required
	Caulking
	Misc. Caulking
	Division 8 - Openings
	Door & Hardware
	Single Door HM Frame & Door
	Double Door HM Frame & Door
	Overhead Doors and Grilles
	Overhead Sectional Doors
	Glass and Glazing
	Storefront
	Louvers and Vents
	Metal Louver Panels

AFTER:

	Division 05 - Metals
Division 05 - Metals
Division 05 - Metals	Misc. Metals
Division 05 - Metals
Division 05 - Metals	Stair - Pan Fill with Landings
Division 05 - Metals
Division 05 - Metals	Stair Railings - Interior
Division 05 - Metals
Division 05 - Metals	Wall mounted railing
Division 05 - Metals
Division 05 - Metals	Bollards
Division 05 - Metals
Division 05 - Metals	Head Knockers
Division 05 - Metals
Division 05 - Metals	Misc. Metals
Division 05 - Metals
	Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites	Rough Carpentry
Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites	Blocking of Door Jambs in CMU
Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites	Blocking around Storefront at CMU
Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites	Plywood Backerboards
Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites	Door Installation
Division 06 - Wood, Plastics, and Composites
Division 06 - Wood, Plastics, and Composites	Specialty Installation
Division 06 - Wood, Plastics, and Composites
	Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Siding
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	4" Fiber Cement Siding
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Waterproofing and Moisture Barriers
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Vehicular Traffic Coating in Garage?
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Building Insulation
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Insulation under conditioned space
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Misc. Spray Foam Throughout as required
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Caulking
Division 07 - Thermal and Moisture Protection
Division 07 - Thermal and Moisture Protection	Misc. Caulking
Division 07 - Thermal and Moisture Protection
	Division 8 - Openings
Division 8 - Openings
Division 8 - Openings	Door & Hardware
Division 8 - Openings
Division 8 - Openings	Single Door HM Frame & Door
Division 8 - Openings
Division 8 - Openings	Double Door HM Frame & Door
Division 8 - Openings
Division 8 - Openings	Overhead Doors and Grilles
Division 8 - Openings
Division 8 - Openings	Overhead Sectional Doors
Division 8 - Openings
Division 8 - Openings	Glass and Glazing
Division 8 - Openings
Division 8 - Openings	Storefront
Division 8 - Openings
Division 8 - Openings	Louvers and Vents
Division 8 - Openings
Division 8 - Openings	Metal Louver Panels
Division 8 - Openings

 

[ValuationMan]

You could do a macro, which I could right, but I'd probably do it as a formula using a helper column. Let's say your data is in Column B starting at row 1. In A2, I would do this.

=IF(LEFT(B1,8)="Division",B1,A1)

You could then copy/paste that formula down and then paste as values. There are many variations on this so let me know if you have more specific needs.

 

[clemons171]

You could do a macro, which I could right, but I'd probably do it as a formula using a helper column. Let's say your data is in Column B starting at row 1. In A2, I would do this.

=IF(LEFT(B1,8)="Division",B1,A1)

You could then copy/paste that formula down and then paste as values. There are many variations on this so let me know if you have more specific needs.

Thank you. So is there an easyway to modify the formula to fill in the 0's with the previous up to where it changes to the next division?

Division 05 - Metals	Division 05 - Metals
0
0	Misc. Metals
0
0	Stair - Pan Fill with Landings
0
0	Stair Railings - Interior
0
0	Wall mounted railing
0
0	Bollards
0
0	Head Knockers
0
0	Misc. Metals
0
Division 06 - Wood, Plastics, and Composites	Division 06 - Wood, Plastics, and Composites
0
0	Rough Carpentry
0
0	Blocking of Door Jambs in CMU
0
0	Blocking around Storefront at CMU
0
0	Plywood Backerboards
0
0	Door Installation
0
0	Specialty Installation
0
Division 07 - Thermal and Moisture Protection	Division 07 - Thermal and Moisture Protection
0
0	Siding
0
0	4" Fiber Cement Siding
0
0	Waterproofing and Moisture Barriers
0
0	Vehicular Traffic Coating in Garage?
0
0	Building Insulation
0
0	Insulation under conditioned space
0
0	Misc. Spray Foam Throughout as required
0
0	Caulking
0
0	Misc. Caulking
0
Division 8 - Openings	Division 8 - Openings
0
0	Door & Hardware
0
0	Single Door HM Frame & Door
0
0	Double Door HM Frame & Door
0
0	Overhead Doors and Grilles
0
0	Overhead Sectional Doors
0
0	Glass and Glazing
0
0	Storefront
0
0	Louvers and Vents
0
0	Metal Louver Panels
0

 

[ValuationMan]

My formula should adjust for that. That's what the A1 is for in the false result. Remember, this formula assumes your column data starts in B1, but the formula is in A2.