dynamic naming of cells

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!

Hi,

Since the sheets are identically laid out, can't you use a simple 2D range with the general format

=SUM(Sheet1:Sheet7!A10)

HTH

this is very promising. but there's a conditional IF statement that I might not have explained in the first post. the formula looks to a named cell on each sheet and if that cell contains certain text, then it takes the number in another named cell on the same sheet and adds it to the number in the same named cell on another sheet that also matches the text test. complicated, yes!

so: if 'sheet1'cell1="red" and 'sheet2'cell1="red" then add 'sheet1'cell2 and 'sheet2'cell2 together and put the total in a cell on the compilation sheet. if one of the cell1 values does not equal "red" then do not include the cell2 value from that sheet in the compilation. if none of them equal "red" then leave the compilation cell blank.

was to create a 3D SUM statement that looks like this:

=SUM('Trek1:Trek7'!trek_total)

since my sheets are named Trek1 through Trek7 and the same cell on each sheet is named trek_total. but when I do that, I get a #NAME? error in the compilation cell.

grrrr

Hello

One way to do this is to have your sheet names (all those that you want to sum) listed in a range somewhere (eg Z1:Z7) then use:

=SUMPRODUCT(N(INDIRECT("'" & $Z$1:$Z$7 & "'!trek_total")))

Richard