Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-17-2019, 02:35 PM   #1
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Tables: structured references

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

__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 06-17-2019, 02:41 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,950
Thanks: 0
Thanked 461 Times in 457 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Tables: structured references

Does this help https://support.office.com/en-us/art...3-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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-17-2019 at 02:49 PM.
June7 is offline   Reply With Quote
Old 06-18-2019, 12:51 PM   #3
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Tables: structured references

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!

__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 06-18-2019, 01:05 PM   #4
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,950
Thanks: 0
Thanked 461 Times in 457 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Tables: structured references

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-18-2019, 01:27 PM   #5
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Tables: structured references

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.
__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 06-18-2019, 02:25 PM   #6
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,950
Thanks: 0
Thanked 461 Times in 457 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Tables: structured references

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-18-2019, 03:57 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,545
Thanks: 88
Thanked 1,472 Times in 1,389 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Tables: structured references

Quote:
Originally Posted by June7 View Post
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 is offline   Reply With Quote
Old 06-18-2019, 04:00 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,545
Thanks: 88
Thanked 1,472 Times in 1,389 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Tables: structured references

Quote:
Originally Posted by June7 View Post
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.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Structured data Rupa Queries 1 01-30-2011 09:04 AM
Question How to convert one mdb to another differently structured mdb GrandiJoos General 9 06-24-2009 06:37 AM
Question Name chnage of tables, queries and autom. corresponding change of references to those panchitocarioca General 3 09-14-2008 04:23 AM
need to see structured organization with query raydan Queries 2 11-14-2005 05:42 PM
lookup tables references maxmangion Tables 2 04-12-2004 10:35 PM




All times are GMT -8. The time now is 10:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World