Tables: structured references (1 Viewer)

DanG

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2004
Messages
477
So I'm trying to understand working with tables by using structured references and need a push.

How would you write the formula below using only structured reference? The table name is "Activity" and the "L" column is "GA#" and the "N" column is "FlowingClients".

Code:
=IF(SUMPRODUCT(($L$2:$L2=L2)*($N$2:$N2=N2))>1,0,1)*N2

It comes down to being able to use absolute and relative references, which I'm having a hard time grasping related to tables.

Any help would be appreciated
 

DanG

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2004
Messages
477
Thank you June7.

I took your advice and renamed GA# to GANum...thank you!

Your example is on the right path, I have tried different variations of it, but can't get it to work. The best I can get is it will select the entire GA# range.

So just concentrating on the "$L$2:$L2" part of my sumproduct formula...the $L$2 part should anchor the range at "L2" and when the formula is dragged down, it should stay at "L2". The "$L2" part expands the range when dragged down to "L3", "L4"...

So in the end my sumproduct defines a range ""$L$2:$L2"" the $L$2 is absolute and must always be that way because that is the begining of the defined range.

Thank you!
 

June7

AWF VIP
Local time
Today, 05:49
Joined
Mar 9, 2014
Messages
5,423
Sorry, I've never utilized tables in Excel. Why use Excel? If you want 'tables', why not use Access?

If you don't want to reference entire range on each row, then I suspect cannot use structured reference syntax.
 

DanG

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2004
Messages
477
Not everyone has Access on their computers and it's easier to pass around a workbook. But I'm with you on that.

Tables seem to be very cool and have a lot going for them, but as I'm finding out they are very finicky. I totally had everything just the way I wanted with the tables thing, and then I tried sorting and adding records, that is where it all fell apart.
 

June7

AWF VIP
Local time
Today, 05:49
Joined
Mar 9, 2014
Messages
5,423
Users can install Access Runtime (freebie) and then run Access file. Just won't be able to do design changes. Possibly has to be an all-in-one file, not split.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:49
Joined
Jan 20, 2009
Messages
12,849
Sorry, I've never utilized tables in Excel. Why use Excel? If you want 'tables', why not use Access?

I often use Excel front ends with tables connected to SQL Server Views. Many of my users are very comfortable with Excel and it is good platform for them to easily build Pivots and graphs on the data I provide in the tables.

Excel also has presentation features not available in Access.

Over time I have developed a template with extensive automation that allows them to save a stand alone Excel report by just clicking Save.

There is a lot to be understood about the properties of data connections and the tables before they work really well.

My main complaint with Excel is the ActiveX combos are very primitive compared to Access and the Conditional Formatting using Expressions is very clunky.

However there are some very cool ConditionalFormatting features not in Access. Gradient filled Databars really impressed my boss.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:49
Joined
Jan 20, 2009
Messages
12,849
Users can install Access Runtime (freebie) and then run Access file. Just won't be able to do design changes. Possibly has to be an all-in-one file, not split.


Access runtime works fine with split databases. You can do everything except change the design of Access Objects but you can alter the properties of everything on them.
 

Users who are viewing this thread

Top Bottom