Excel return range reference

you can always use tables.

table ranges adjust automatically

=sumif([temp1], “GoA”, [temp2])

if temp1 and temp2 are the headers of A and B

2 Likes

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.

1 Like

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.

1 Like

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!”
:older_man:

1 Like

leave it to boomers to complain about having extra benefits!

This “extra benefit” covers part of the heading of the column. So, anti-benefit.

1 Like

…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.

1 Like

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)

:bump:

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.

1 Like

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.