you can always use tables.
table ranges adjust automatically
=sumif([temp1], “GoA”, [temp2])
if temp1 and temp2 are the headers of A and B
you can always use tables.
table ranges adjust automatically
=sumif([temp1], “GoA”, [temp2])
if temp1 and temp2 are the headers of A and B
Are those different than named ranges?
edit: I think someone here introduced me to them not too long ago. This might be the way to go. As I recall, they self-embiggen when more data is added.
A perfectly cromulent word.
nope, just select the data you want (with headers), go to the ribbon, Insert => Table.
Yes, they self-drag-down when more data is added, however, they don’t self-shrink when there’s less data.
Tables also autofill formulas and if you add a new column to the right hand side of the table and put in a formula, it also autofills the entire column. Add rows, formulas fill down too.
Qurious Cuestions: Why do Tables, upon being created, automatically apply the data-filter? Is there a usefulness to this? Do most people want to filter their newly created tables? I certainly don’t. If I wanted to filter I’d Alt-D-F-F.
Who told you to put the filter on? I didn’t tell you to put the filter on. Why’d you put the filter on? You haven’t even been to see the doctor. If you’re gonna put a filter on, let a doctor put a filter on.
“You’re gonna have that filter . . . and you’ll LIKE it!”
leave it to boomers to complain about having extra benefits!
This “extra benefit” covers part of the heading of the column. So, anti-benefit.
…but definitely not for “SUM” or “SUMIF[S]”. I used it for one or more of those and, while Excel was recalculating, I was able to save up to 15% or more on my car insurance.
I do Ctrl+Shift+L.
I agree data tables are the way to go. They make formulas so much easier to understand/review.
I’ll allow it.
Alt-D-F is a new one for me… I always Ctrl-Shift-L. Alt-D-F doesn’t seem to unfilter when I do it again like I’d want. It’s being really finicky for me, let me know if Ctrl-Shift-L seems worse or at least equivalent?
Edit: read more
Pardon me…you need to Alt-d-f-f (two f’s) in order to turn it both on & off.
This is a perfectly cromulent keyboard shortcut. If you’re happy with your shortcut, you can keep your shortcut.
=SUMIF(OFFSET(B1,0,0,COUNT(A:A),1),“GoA”,OFFSET(A1,0,0,COUNT(A:A),1))
or make C1
=SUMIF($B$1:$B$1000,“GoA”,$A$1:$A$1000)
I have now implemented a bunch of tables in my workbook. They’re pretty slick…except for updating when the new month has fewer rows than the previous month.
The old-extraneous data can be cleared via the delete key but the table then has a bunch of blank “rows” at the end. How do I easily get that cleaned up? I know I can right-click-delete-table-rows or something similar form the menu, but I’ve got a whole bunch of disjointed tables that I need to do this to and figure “There’s got to be a better way!”
Excel balks when I try to delete the entire row because something along the lines of “There’s a table there! What are you doing?!?!?!”
You can always use a macro to cycle through the tables to delete the extra rows, that’s how I used to do it to ensure nothing breaks when new data has fewer rows than before.
No easy alternative that I’m aware of.
Yes, I figured that was possible and the best choice…do you, by chance, have some sample code? So far, all I’ve found assigns a specifically specific data range to the revised table. As in…
Sub ResizeList()
Dim wrksht As Worksheet
Dim objListObj As ListObject
Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
Set objListObj = wrksht.ListObjects(1)
objListObj.Resize Range("A1:B10")
End Sub
I don’t want to have to update that A1:B10 reference every month. I want Excel to just figure it out.