Been meaning to pose this for a while. What's the general view of Tables?
I know they offer advantages, like when adding data to the bottom of a table the new data is automatically incorporated in the table, and adding a formula replicates itself elsewhere in the column, but I don't find formula outside the table which refer to stuff inside it particularly easy to read, and when writing macros it just seems unnecessarily painful.
Interested to hear other views.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I quite like them, especially in VBA. They do have their quirks, but it's useful to be able to refer to specific columns by name and quickly grab chunks of the table. I don't really do a lot in the way of data entry so that side of it is irrelevant to me, but I like them as a source for pivot tables and of course they are integral to using power BI.
Remember what the dormouse said
Feed your head
The dynamic properties are great.
The structured referencing formula syntax sucks!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Nice and easy to deal with, but seem to really hammer performance when dealing with large volumes of data.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Agree with that, the main reason I refrain from using tablesThe structured referencing formula syntax sucks!
Life's a spreadsheet, Excel!
Say thanks, Click *
I quite like them and would generally use them where I can in preference to manually setting up a Dynamic Named Range or two.
That said, If you have long names for column headers, the length of the formula can be obscene ... especially if you need absolute references when dragging the formula across. And using them in Data Validation and Charts is unnecessarily difficult.
And, probably most significantly, if you protect the sheet, the Table magically loses the capability to automatically extend itself and incorporate new rows.
Great idea, not thought through and badly implemented. Shame
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I usually change everything into worksheet arrays, I haven't actually dealt with tables
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
I also find the structured a pain to deal with and often convert them back to a regular table when answering questions. I know they have their place and can be useful, but like everything...there is a time and a place. Bottom like, I dont like them
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Another draw back in using the Table References in formula is that it is not working with the closed workbooks like the range references
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
You can turn the structured referencing off, you know.
no I didnt??
Originally Posted by romperstomper
You can turn the structured referencing off, you know.
Are you referring to the Excel Option for formulas "Use Table Names In Formulas"? I manually overwrite the table nomenclature when "it gets in the way".
You can turn the structured referencing off, you know.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
ndm, I have often done that too - manually typed in ranges that make more sense to me
That is especially good when column headers are long and you want absolute references....man, they can sure get long using Table Nomenclature
yup, you got that right, sometinmes to te point of almost being jibberish lol
I've had pretty good luck just ignoring them and programming as usual!
I like them and encourage people at work to use them and the structured referencing as much as possible. Makes it easier to see where they are going wrong with their formula.
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Just for anecdotal value:
Recently in Redmond, Washington, during the Global MVP Summit. A room packed with Excel MVPs. Everybody is asked to introduce themselves by stating their name and their favourite Excel feature. Over half of the Excel MVPs say it's tables.
Zack Baresse and Kevin Jones have written a book about it. Some stuff can be seen on the companion web site www.exceltables.com
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks