Auto-move data in a range w/o VBA?

[thewiseguy]

Here is a range of data where each cell references others from another sheet:

Proposal5.xlsm
	H	I	J	K	L	M
79						Includes:
80						Comprehensive Rebate Administration
81						Fixtures/Lamps
82
83
84						Labor
85						Recycling of Old Fluorescent Lamps
86						Rental Equipment (lifts and/or dumpsters)
87						5-Year Warranty on Fixtures/Lamps
88						10-Year Warranty on Fixtures/Lamps
89						Layout & Design w/ Photometric Data
Proposal

Cell Formulas
Range	Formula
M82	M82	=IF('Project Summary'!F4>0,"Auxiliary Products (see materials summary","")
M83	M83	=IF('Project Summary'!F5>0,"Additional Materials","")
M85	M85	=IF('Project Summary'!F6>0,"Recycling of Old Fluorescent Lamps","")
M86	M86	=IF('Project Summary'!F7>0,"Rental Equipment (lifts and/or dumpsters)","")
M87:M89	M87	=IF('Project Summary'!K16="yes",'Project Summary'!J16,"")

Depending upon the referenced cells, I would like the outputs to be condensed or have no gaps. Essentially, I would like the data to fill upward if there would be gaps, so something like this:

Proposal5.xlsm
	H	I	J	K	L	M
79						Includes:
80						Comprehensive Rebate Administration
81						Fixtures/Lamps
82						Labor
83						Recycling of Old Fluorescent Lamps
84						Rental Equipment (lifts and/or dumpsters)
85						5-Year Warranty on Fixtures/Lamps
86						10-Year Warranty on Fixtures/Lamps
87						Layout & Design w/ Photometric Data
Proposal

Cell Formulas
Range	Formula
M83	M83	=IF('Project Summary'!F6>0,"Recycling of Old Fluorescent Lamps","")
M84	M84	=IF('Project Summary'!F7>0,"Rental Equipment (lifts and/or dumpsters)","")
M85:M87	M85	=IF('Project Summary'!K16="yes",'Project Summary'!J16,"")

Is this possible without VBA?

 

[Toadstool]

Hi TheWiseGuy,

As the text moves up you may want to add some explaining text to the three numbers which appear.

If you cut & paste your existing formulae somewhere else (another tab, or like my example some cells off to one side) then you can just pull across the cells with values:

TheWiseGuy.xlsx
	M	N																																																																		CB	CC
79	Includes:																																																																				Includes:
80	Comprehensive Rebate Administration																																																																				Comprehensive Rebate Administration
81	Fixtures/Lamps																																																																				Fixtures/Lamps
82	Auxiliary Products (see materials summary																																																																				Auxiliary Products (see materials summary
83	Labor
84	Rental Equipment (lifts and/or dumpsters)																																																																				Labor
85	33
86	55																																																																				Rental Equipment (lifts and/or dumpsters)
87
88																																																																					33
89																																																																					55
Sheet1

Cell Formulas
Range	Formula
CC82	CC82	=IF('Project Summary'!F4>0,"Auxiliary Products (see materials summary","")
CC83	CC83	=IF('Project Summary'!F5>0,"Additional Materials","")
M80:M89	M80	=IFERROR(INDEX($CC$80:$CC$89,AGGREGATE(15,6,ROW($CC$80:$CC$89)-ROW($CC$79)/($CC$80:$CC$89<>""),ROW()-ROW($M$79))),"")
CC85	CC85	=IF('Project Summary'!F6>0,"Recycling of Old Fluorescent Lamps","")
CC86	CC86	=IF('Project Summary'!F7>0,"Rental Equipment (lifts and/or dumpsters)","")
CC87:CC89	CC87	=IF('Project Summary'!K16="yes",'Project Summary'!J16,"")