Forum Discussion
Table with multiple Header Rows to make a SUMProduct formula work?
Hello smart excel users,
Tables are not my strong point and I have made one that is essentially a cost estimating spreadsheet.
-The row above the rainbow coloured columns (the row in grey and white) is the actual table header.
-The white cells are the editable cells that are in turn the lookup value for the Xlookup formula in the UOM and Rate rows so that my rates and units of measure can be calculated from a hidden table.
-The reason the editable cells aren't the table headers in the "Hour Count" portion of the table are because A: the names are too long to fit in a single row header and B: there are 3 types of hours for each rate (ST OT and OT2) (I have the table header names partially hidden and are actually written as (ST_1, OT_1 etc. but have greyed out the _# portion of the name to look better)
-The DTR Total column then does a sum product calculation for each row where it multiplies the sum of the units in each column by their corresponding rates in the rates row.
My questions are -
A: Is there a way to get the rates and units of measure etc. to be a part of the table to make life easier when I have to add or subtract columns to the table? At this point I always have to tweak a bunch of stuff every time I make a change.
B: Is there a better way that I should be doing things?
I'll tackle question B first: Yes, there is a better way: get rid of the colors, the merged cells up at the top, all of the things that you've done to make it "pretty." Those may be pleasing to the human eye, but they can inhibit Excel--merged cells in particular can be problematic. Basic point: hold off on those features until you get it working. Especially if this is where the "raw" or "input" data is going to go, you don't ever need to make it pretty. Save that for the output end of things, and even then, don't pour energy into beautification until you get the functionality you want.
I'd like to address question A as well, but frankly, at my advanced age (octogenarian) with only a laptop at my disposal at the moment, I can't quite make out the headings, sub-headings, sub-sub-headings. Since it's devoid of data anyway, it seems unlikely that there's anything totally confidential, would you be willing to share not an image but the actual workbook, so we could (1) look at it in real life, and (2) possibly demonstrate some ways that you could make each header cell a single cell with fully adequate info in it.
Could you also give the context here. That might make a difference. How is this going to be used? What's the business or professional purpose to be served? Will data be added every day, every week (etc)? in the various rows? Etc., etc.
I hinted at it above, but let me make this explicit: it's often useful to plan to separate the raw or input data from the output itself, actually placing those two "components" on separate sheets. You're asking about SUMPRODUCT, leading me to believe you will be extracting selected data to produce a summary of some kind...that would go along with creating a separate sheet in the workbook to display that summary.
