Hi,



I've looked and looked and cannot find any reference that this capability even exists.

I want to have named ranges and then access the individual row/col. It works, sort of. I'd like to know if it can use other functions.

This is just a dummy example: I have two named ranges: Col_1 & Col_2, and I've used them in a few formulas and here is the result and the formulas:

Col_1 Col_2 1 + 2 1 * 2 sum max
1 5 6 5 138 12
2 5 7 10 138 12
3 5 8 15 138 12
4 5 9 20 138 12
5 5 10 25 138 12
6 5 11 30 138 12
7 5 12 35 138 12
8 5 13 40 138 12
9 5 14 45 138 12
10 5 15 50 138 12
11 5 16 55 138 12
12 5 17 60 138 12





Col_1 Col_2 1 + 2 1 * 2 sum max
1 5 =Col_2+Col_1 =Col_2*Col_1 =SUM(Col_2,Col_1) =MAX(Col_2,Col_1)
2 5 =Col_2+Col_1 =Col_2*Col_1 =SUM(Col_2,Col_1) =MAX(Col_2,Col_1)

I apologize for the above, but my attempts to copy this were spectacularly bad.

I hope you can see that: "+" and "*" work fine and "sum" and "max" do not. I know I can change this into a table but for various reasons I do not wish to.

I was wondering if there is something that functions similarly to "@" for named ranges.

Any ideas?

Please post a sample sheet with your data, the above is a bit "messy"

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I apologize, but any attempt I made to post this as a table, even doing the spacing manually, failed.

I hope this csv version is understandable.

Col_1,Col_2,1 + 2,1 * 2,sum,max
1,5,6,5,138,12
2,5,7,10,138,12
3,5,8,15,138,12
4,5,9,20,138,12
5,5,10,25,138,12
6,5,11,30,138,12
7,5,12,35,138,12
8,5,13,40,138,12
9,5,14,45,138,12
10,5,15,50,138,12
11,5,16,55,138,12
12,5,17,60,138,12

Much better if you did as Pecoflyer suggests - upload the Excel file with the data and state manually examples of expected results

I had been trying unsuccessfully to cut and paste my tables. Now I have attached a file.

Attached Files

Mary test 9-24-18.xlsx (11.9 KB, 12 views)

Not understood.
Isn't column E the same as column C.
Max of what in column F?

Yes, the answer should be the same. C=A+B, E=sum(a,b)
F=max(a,b)

sum and max work correctly if I use A1, B1, etc but not when I use the name Col_1, Col_2. E & F sum and max the entire column not just one row.

That is why I wondered if there was something comparable to "@" to use "this" row.

Maybe this can be of some help.

Attached Files

Mary_2.xlsx (14.1 KB, 10 views)

Thank you, but I had asked to not have to use a table. I could not find a way of using @ with named ranges. As I said, I looked over the internet for an example of using a named range(column) a row at a time. I tried several combinations of @ with or without brackets, and got error messages.

I very much appreciate your efforts.

You can't use @ without a table.

What's the problem using for example

MAX(Results!$A2,Results!$B2)?

I have ways of getting what I want. I was trying to do without using COL/ROW. I just thought it was strange that I could use certain operators and it works fine, but not those functions (sum & max). And I could not find any documentation that talks about what I was doing; it's almost like no one knows the functionality exists.

Again, thank you.

I don't know what functionality you are talking about.

Attached Files

Mary_3.xlsx (14.3 KB, 8 views)

[@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
2. With a Table defined, you can e.g. in cell C2 (i.e. in same row for this example) use formulas
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.
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
and copy it down!

I really appreciate everyone's help,

I know trying to do this with my example does not make sense. I was actually trying to do something across "similar" but somewhat different large sheets, which is why I would prefer to use a "name" rather than a col/row reference. Again, I know how do this using excel tables, but as I noted I have reasons why I would rather not use tables.

My actual tables are much more complicated than my example. I want to add multiple columns and find the max of multiple columns in "each" row and place the result in the same row.

In my example, my non-table has 12 rows and I have named Col_1 and Col_2. What I found was that I can have "= Col_1 + Col_2" or "= Col_1 * Col_2" in a cell and it will add or multiply just the Col_1 and Col_2 for that row - works great.

However, when I use sum to add - "= sum (Col_1 , Col_2) - it sums the entire column of each. Same with max "= max ( Col_1 , Col_2)"; it finds the max of 12 rows of both columns. What I wanted was it to sum Col_1 & Col_2 in just that one row and find the max of Col_1 & Col_2 in just that one row.

As I wrote, there is zero documentation that my first examples would work, but they do. But "@" does not work on a non-table sheet. So it seems that if I want to compare 5 columns, I must use a complicated "if" statement.

Again, thanks for your suggestions, but they will not help in this case.

See implicit intersection.