+ Reply to Thread
Results 1 to 5 of 5

dynamic naming of cells

  1. #1
    Registered User
    Join Date
    07-29-2008
    Location
    New York, USA
    Posts
    6

    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!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145
    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

  3. #3
    Registered User
    Join Date
    07-29-2008
    Location
    New York, USA
    Posts
    6

    sounds good...

    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.

  4. #4
    Registered User
    Join Date
    07-29-2008
    Location
    New York, USA
    Posts
    6

    one other thing I tried

    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

  5. #5
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1