Excel Absolute References in Structured Reference Table Formulas

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.

[@nif82]

This is a lifesaver! I have a insanely complicated formula to fill over wayyy too many rows to fix the column number every time. Thank you!

[@ExcelCampus]

Amazing, glad we could help!

[@singaravelusouppourayen2367]

If I had seen this video, I would have saved lots of time. Thanks a lot.

[@rennertnyc1379]

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!

[@rafapareja21]

Great help Jon, thanks a lot. I have chaged two files to Table references using your information and they are faster and smaller

[@Richardhuang70]

What calculation is performed quicker, column reference or table reference?

[@wayneedmondson1065]

Hi John. Found this oldie but goodie tip. Saved the day :)) Thanks for sharing! Thumbs up!!

[@vincentturcotte4584]

MERCI! Des tonnes de minutes gagnées grâce à vous!

[@Syleco]

This is absolutely a lifesaver. Thanks !

[@ExcelCampus]

Glad it helped Syleco! 😀

[@williamarthur4801]

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.

[@rafolsmorales]

Thank you. This is an extremely useful and concise explanation.

[@nestoraalda]

I do not understand your language but I managed to understand the explanation anyway. Thank you!!

[@001dajudge]

Thanks Jon!!! I've been struggling with this for a long long time.

[@Mahmoud-mf8hn]

Wow!!! you saved my life man!
Big thanks!

[@alphaone4557]

How to link variable cells to another worksheets?
For e.g.FROM sheet1 A1, A2, A3 etc TO sheet2 B1, B2, B3 etc?

[@avenewconsulting1118]

hi can you please tell me when to use the braquet and the @? I don t understand those 2 points, thx

[@DzLilian]

WOW!!!!! thank you soooooo much!!!!!!!! saved my life literally

[@todorowael]

Thank you for your help! Keep up with the great tutorials!

[@xcaliber5592]

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??

[@dammitjames]

This is a fantastic trick, thank you.

[@ExcelCampus]

Glad you liked it, James! :)

[@Doofenshmirtz1088]

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]

Its original label? What did you find and what did you replace with?

[@Chris-im3ys]

Like, for example.
Find: $A10
Replace: @[Price]:[Price]]
Then drag it down and across?

[@Doofenshmirtz1088]

@@Chris-im3ys 't_Data[Color]' to $A$1 and then back to 't_Data[Color]' in the example

[@Doofenshmirtz1088]

@@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

[@debjitchakraborty1711]

what about mixed cell ref in table?

[@ruicarmo6371]

That was a huge help, thank you so much!!!

[@ExcelCampus]

Glad it helped!

[@singaravelusouppourayen2367]

How to create absolute reference to a cell in table? Is it even possible?

[@shihabkunnummal3152]

Thanks a lot, It was very useful tip..

[@John-qt5em]

How do you reference/lock a SPECIFIC cell? (e.g. E12)

[@yegirish]

Ah my god, this is perfect. Thank you!

[@kiriltodorov2192]

great explanation, thank you!

[@trishakumarasinghe]

Life saver !!!! Thank you ;-)

[@yeoyeodere1]

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.

[@ikar2k]

Great! Thx!

[@nitishkankal]

Woow :) very nicely explained ...Thank you

[@JorgePicco]

Life saver.

[@ChromeStriker]

Briliant thanks!

[@mchllwoods]

I just find it easier to give each column is own name range.

[@Digital-Dan]

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.

[@alexrosen8762]

Great! Thanks :-)

[@porjarlearnchaiyong7519]

give= get i donal your with my mind

[@nayhem]

mmm, arcana