Tables: structured references

[DanG]

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

=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

 

[June7]

Does this help https://support.office.com/en-us/ar...l-tables-F5ED2452-2337-4F71-BED3-C8AE6D2B276E

Maybe:
=IF(SUMPRODUCT((Activity[[GA#]]=L2)*(Activity[FlowingClients]=N2))>1,0,1)*N2

Advise not to use special characters such as # in naming convention.

If you want to provide file for analysis, follow instructions at bottom of my post.

 

[DanG]

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]

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]

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]

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]

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]

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.