Writing formula using Names from Name Manager

[GregM]

Hello friends! Unfortunately I am not permitted to upload the sample spreadsheet so I'll describe it.
I am practicing with a formula from a test table with Qtr1-Qtr4 on top and NE,SE,NW,SW on the left, Totals at the far right and bottom with sample numbers in the cells.
I have saved the Names in Name Manager so I can use =SUM(NW) and SUM(Qtr2) for example to list the Totals of those rows and columns.
My question is: How can I write a formula to address individual cells?
I want to subtract C2(Qtr2@NE) from D2(Qtr3@NE).
I would think

=SUM(Sheet1!NE[Qtr2] - Sheet1!NE[Qtr3])

would work, but not the case.
I don't want to have to use

=C2-D2

because I'm trying to learn to use the NAMES.
Any help will be appreciated. Thanks!

 

[Yongle]

Which version of Excel are you on?
It is not possible to created named range with names Qtr1,Qtr2,Qtr3,Qtr4

 

[Yongle]

I used 3 different named ranges
Regions (A2:A5)
Quarter (B1:E1)
Data (B2:E5)
and this formula (using Index and Match) in E8
=INDEX(Data,MATCH(A8,Regions,0),MATCH(B8,Quarters,0))

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td=bgcolor:#BDD7EE]Quarter1[/td][td=bgcolor:#BDD7EE]Quarter2[/td][td=bgcolor:#BDD7EE]Quarter3[/td][td=bgcolor:#BDD7EE]Quarter4[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#C6E0B4]NE[/td][td=bgcolor:#FFFF00]A[/td][td=bgcolor:#FFFF00]B[/td][td=bgcolor:#FFFF00]C[/td][td=bgcolor:#FFFF00]D[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#C6E0B4]SE[/td][td=bgcolor:#FFFF00]E[/td][td=bgcolor:#FFFF00]F[/td][td=bgcolor:#FFF2CC]G[/td][td=bgcolor:#FFFF00]H[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#C6E0B4]NW[/td][td=bgcolor:#FFFF00]I[/td][td=bgcolor:#FFFF00]J[/td][td=bgcolor:#FFFF00]K[/td][td=bgcolor:#FFFF00]L[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#C6E0B4]SW[/td][td=bgcolor:#FFFF00]M[/td][td=bgcolor:#FFFF00]N[/td][td=bgcolor:#FFFF00]O[/td][td=bgcolor:#FFFF00]P[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#C6E0B4]Region[/td][td=bgcolor:#FFFF00]Quarter[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]SE[/td][td]Quarter3[/td][td][/td][td][/td][td]G[/td][td] =INDEX(Data,MATCH(A8,Regions,0),MATCH(B8,Quarters,0))[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet9[/td][/tr][/table]

 

[Fluff]

Another option

Excel 2013/2016
	A	B	C	D	E	F
1	Column1	qrt1	qtr2	qtr3
2	ne	1	2	3		2
3	nw	4	5	6
4	sw	7	8	9
Sheet1

Cell Formulas
Range	Formula
F2	=SUM(ne qtr2_)

Named Ranges
Name	Refers To	Cells
ne	=Sheet1!$B$2:$D$2
qtr2_	=Table2[qtr2]

 

[Marcelo Branco]

Maybe something like this (observe the names Qtr_1, Qtr_2, ...)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Region​

[/TD]
[TD]

Qtr_1​

[/TD]
[TD]

Qtr_2​

[/TD]
[TD]

Qtr_3​

[/TD]
[TD]

Qtr_4​

[/TD]
[TD]

Qtr_Total​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

NE​

[/TD]
[TD]

10​

[/TD]
[TD]

12​

[/TD]
[TD]

14​

[/TD]
[TD]

16​

[/TD]
[TD]

52​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

SE​

[/TD]
[TD]

12​

[/TD]
[TD]

14​

[/TD]
[TD]

16​

[/TD]
[TD]

18​

[/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

NW​

[/TD]
[TD]

14​

[/TD]
[TD]

16​

[/TD]
[TD]

18​

[/TD]
[TD]

20​

[/TD]
[TD]

68​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

SW​

[/TD]
[TD]

16​

[/TD]
[TD]

18​

[/TD]
[TD]

20​

[/TD]
[TD]

22​

[/TD]
[TD]

76​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Reg_Total​

[/TD]
[TD]

52​

[/TD]
[TD]

60​

[/TD]
[TD]

68​

[/TD]
[TD]

76​

[/TD]
[TD]

256​

[/TD]
[/TR]
</tbody>[/TABLE]


You may use the intersection of named ranges, that is:
Range1 RangeB - Range1 RangeC(note the space between the two ranges)

In your case
=NE Qtr_2 - NE Qtr_3

M.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...sing-names-from-name-manager.html#post5105589

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[GregM]

Thanks for pointing it out - my bad.
I didn't do it out of impatience, I did it because at that site for whatever reason I was permitted to upload a sample spreadsheet and I thought that would make it easier since I'm sure no one likes to create a table for me when I should provide it. I wasn't allowed to upload the table at this site and I appreciate the folks, like yourself who took the time to make a table to demonstrate the solution.

 

[GregM]

Thanks for your time to reply.
You were correct and I didn't even catch it. Qtr1 was not allowed, but NameManager edited it without my knowledge by adding the underscore to make it Qtr1_.
I am Excel 2016 (Windows) 64-bit.

 

[GregM]

Thank you FLUFF for your reply and SOLUTION to the Question/Problem.
All it took was to use a SPACE delimiter instead of putting square brackets around the second argument.
Great demonstration and explanation!

 

[GregM]

Thank you Marcelo Branco for your reply and SOLUTION to the Question/Problem.
You demonstrated, as FLUFF did immediately before you, that all it took was to use a SPACE delimiter instead of putting square brackets around the second argument.
Great demonstration and explanation!.