this is very complicated, but I'm sure there's a simple way to do it. I have 7 identically layed-out sheets (tho each contains different numerical data), each of which feeds its data to a compilation sheet in the same workbook. each of the 7 "data" sheets has named cells, and the same cell on each sheet has the same name (albeit with the sheet name preceding it when referenced). I created one sheet as a master, and then copied it 7 times; excel automatically updated the cell names to reflect the sheet name. each set of same-name cells are then added together and the sum is fed into a cell on the complication sheet. if the sum is zero, the cell appears blank. here's the formula I use for one of the cells on the compilation sheet:

=(IF((IF('Trek 1'!sun_d="Headquarters", 'Trek 1'!trek_total, 0)+IF('Trek 2'!sun_d="Headquarters", 'Trek 2'!trek_total, 0)+IF('Trek 3'!sun_d="Headquarters", 'Trek 3'!trek_total, 0)+IF('Trek 4'!sun_d="Headquarters", 'Trek 4'!trek_total, 0)+IF('Trek 5'!sun_d="Headquarters", 'Trek 5'!trek_total, 0)+IF('Trek 6'!sun_d="Headquarters", 'Trek 6'!trek_total, 0)+IF('Trek 7'!sun_d="Headquarters", 'Trek 7'!trek_total, 0))=0,"",(IF('Trek 1'!sun_d="Headquarters", 'Trek 1'!trek_total, 0)+IF('Trek 2'!sun_d="Headquarters", 'Trek 2'!trek_total, 0)+IF('Trek 3'!sun_d="Headquarters", 'Trek 3'!trek_total, 0)+IF('Trek 4'!sun_d="Headquarters", 'Trek 4'!trek_total, 0)+IF('Trek 5'!sun_d="Headquarters", 'Trek 5'!trek_total, 0)+IF('Trek 6'!sun_d="Headquarters", 'Trek 6'!trek_total, 0)+IF('Trek 7'!sun_d="Headquarters", 'Trek 7'!trek_total, 0))))

it's important that the cell appear blank if the sum=zero. this formula works perfectly on the compilation sheet.

the problem is that I have over 150 of these "sum" cells on the compilation sheet, so editing this formula for each sum cell would take days. my feeling is that there must be a way to re-do the master data sheet and rename each named cell using a dynamic name instead of a static name.

basically, I'd like to find a way to use the same formula in each sum cell on the compilation but have the formula's cell position on the compilation sheet dictate the names of the named worksheet cells. I hope that makes sense!

## Bookmarks