www.excelcampus... Free Gift - www.excelcampus... A solution for creating absolute or anchored references in formulas that contain structured reference table style notation. For Excel 2007, 2010, and 2013.
Firstly, great video and really useful, Got me thinking; Not sure if this is easier but another way is to use INDEX; so SUMIF(INDEX(sumup[#All],0,1),INDEX(Table6[#All],2,1),INDEX(sumup[#All],0,4)), so just select the entire table within index and then give the references, here 0 to get all the rows, to be searched or summed up , for the criteria if they need to be relative, say horizontally just put zero and the criteria will spill across the criteria table eg searching RED BLUE, GREEN , I have experimented with ROW and COLUMN but they don't work in arrays, or at least don't seem to . More experimentation.
It didn't work for me unfortunately, but what I did instead was to change the name from 't_Data[Color]' for example to $A$1, and did the same for the other columns to $A$2, $A$3 and so forth. And then at the end used 'Find and Replace' to revert it back to its original label. Works for me
@@Chris-im3ys another thing I tried was because I had so many rows in my situation, I wrote the first few columns and made absolute references for most cells then copy pasted into another sheet, dragged down to the right amount of rows. Then the hard part was copy pasting one by one into Microsoft Word, editing the formulas a bit like removing the extra '=' sign and then pasting back into Excel. A bit complicated tbh. I might make a video explaining it further
The collection of kludges that is Excel leads to even more imaginative kludges foisted upon us by the experts. This isn't wrong, it's just unfortunate.
Thanks so much! Copying and pasting the column names was driving me crazy. It's too bad Excel doesn't have a simpler way to deal with this, but your workaround is a huge help!
I Have the following formula using the Max function: MAX($B$11:B11). The Column Header is called "ID" and the Table is called TBL_Minor_Factions. How would I lock down the first cell in this column using Table Nomenclature to achieve the same result that I get from this formula MAX($B$11:B11) that does need Table Nomenclature??
An alternative method to utilize data in the structured reference tables would be MSQuery. However, this requires some very basic database skills. Still its already fully integrated in excel and is a ready made solution.
