LinkBack URL
About LinkBacks
Not looking to copy and paste into each new column. Looking to drag to copy and paste because I have over 50 columns.
Not looking to copy and paste into each new column. Looking to drag to copy and paste because I have over 50 columns.
Try this:
One column in the same or other table: table1[[column1]:[column1]]
One cell in the same row as the formula: table1[@[column1]:[column1]]
Table names must be used even if the reference and formula cell are in the same table. You must drag these formulas across columns to maintain the absolute reference (copy & paste does not work).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I actually figured it out! There is a relatively simple way, using the INDIRECT formula
Example:
=COUNTIFS(INDIRECT("Table1[Column1]"),$A2)
If you have a lot of data, the excessive use of INDIRECT will slow you up BIG time.
Oh, I wasn't aware of that. Fortunately the spreadsheet I was using didn't have enough to the point where it was affected. But I do have plenty of spreadsheets with thousands of rows, so this is good info for future use of the formula.
I used to never use table ranges because of the lack of ability to use absolute ranges, but I love the convenience of my formulas showing the names of the references. The problem with using A:A though was the same as INDIRECT, where using too many lookups of an entire column was slowing down my spreadsheets BIG time. Seems like it's unavoidable in 2010 lol.
Does Excel 2013 allow absolute references using Table Ranges? If not, why would MS not make this possible? Seems like such a necessity to me
I have mixed feelings about absolute references. certainly they make it easy to follow if the Table is on another sheet. A:A is a disaster, even though we all do it. Dynamic named rages are another good way round that. So far as I know 2013 has not made any changes to the way absolue ranges are handled.
Regarding INDIRECT, take a look at this article
http://chandoo.org/wp/2014/03/03/han...-are-dynamite/
I see I wrote Dynamic Named Rages - yep. that summarises my relationship with Excel perfectly!!
Named RAGES are definitely a good substitute, but I think depending on how many rages you create, the amount of time it takes could make the method redundant.
Maybe MS will smarten up in the next Office update
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks