Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #13  
Old 09-25-2018, 11:06 PM
ArviLaanemets ArviLaanemets is offline accessing individual row data in named ranges Windows 8 accessing individual row data in named ranges Office 2016
Expert
 
Join Date: May 2017
Posts: 949
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

[@FieldName] is used in Table formula syntax only - you can't use it outside of defined Table!

When you have numbers in cells A2 and B2 (and column headers in row 1), and you want sum of those numbers, then:
1. Without a Table defined, you can e.g. in cell C2 (i.e. in same row for this example) use formulas
Code:
= $A2 + $B2
or
= SUM($A2,$B2)
or
=SUM($A2:$B2)
2. With a Table defined, you can e.g. in cell C2 (i.e. in same row for this example) use formulas
Code:
=[@ColAName] + [@ColBName]
or
=SUM([@ColAName],[@ColBName])
or
=SUM([[@ColAName]:[@ColBName]])
or
= $A2 + $B2
or
= SUM($A2,$B2)
or
=SUM($A2:$B2)
All those formulas return same result. A difference for defined Table is, that as long you have a single formula in column (no values instead formulas entered, and no different formulas entered), the formula is automatically expanded over this column. And of-course is it much easier to interpret Table formulas - in case you are reasonable with column names.

Named ranges mostly are useful when you need calculations over range.

Of-course you can define also a dynamic named ranges to imitate common Excel formulas behavior, but with current example I don't see any reason for this. An example anyway:
Select any cell in row 2. Define a name eg.
Code:
dnMyRange =Results!$A2:$B2
NB! Be aware, that column reference is absolute, the row reference is not! And you definitely must have a cell in row 2 activated - egal in your table or outside of it!
Now into column sum into cell E2 (or into any cell in row 2, even on different worksheet) enter formula
Code:
=SUM(dnMyRange)
and copy it down!
Reply With Quote
 

Tags
named ranges



Similar Threads
Thread Thread Starter Forum Replies Last Post
accessing individual row data in named ranges Named Ranges Help SavGDK Excel 5 05-01-2017 09:41 AM
accessing individual row data in named ranges Data Validation List based on Two Named Ranges Rich18144 Excel 4 01-14-2016 04:37 AM
How to use named ranges in excel vba? bosve73 Excel Programming 4 01-25-2012 09:26 AM
Dynamic Named Ranges using text hannu Excel 0 06-22-2010 04:42 PM
Can't import home adresses in outlook 2010 from excel named ranges eekie Outlook 0 05-14-2010 02:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft