The col name defs are as follows...

colC: "Sheetname" RefersTo: =Summary!$C9
colD: "LabHrs" RefersTo: =Summary!$D9
colE: "LabCost" RefersTo: =Summary!$E9
colF: "MatlCost" RefersTo: =Summary!$F9
colG: "Sell" RefersTo: =Summary!$G9
colI: "SellPlus" RefersTo: =Summary!$CI9

...as defined after selecting any cell in row9.

The total (Q36) is named "Total". Its RefersTo is fully absolute.

The col formulas for the fixed table are as follows:

colD: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!G7"), "")
colE: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!H7"), "")
colF: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!I7"), "")
colG: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!J7"), "")
colI: =IF(LEN(Sell),Sell+(Total/24),"")

Given the structure of this sheet 'as is', adding new rows to the table
will need to be done manually. I probably would have designed this
sheet so a blank row could be stored (hidden) at the top of the sheet
so I can insert as needed for adding more names. Unfortunately, for
this to work would require relocating the area to the right of the
table so it's above the table in frozen pane area. This approach would
make removing names a simple matter of deleting entire rows, leaving no
required extraneous cleanup processing to do. If you're interested to
review a working copy of this let me know where to send/upload a file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion