Forum Discussion
Defined names change when looking them up in the name box
Hi,
I'm having some trouble when defining a name to a search function.
I go to defined names, add name, let's say ANA that refers to =MAX(A1:A32). When I click in another cell and in the name box try to find ANA, it automatically assigns the value of ANA to the cell I was at.
Has anyone had the same problem? Or is it something I'm doing wrong?
Thank you.
- In Spanish Excel, the formula should be:
=DESREF(Hoja1!$A$1:$A$1000;COINCIDIR(MAX(Hoja1!$A$1:$A$1000);Hoja1!$A$1:$A$1000;0)-1;0;1;1)
See: https://en.excel-translator.de/translator/
12 Replies
What if you would set ANA to be "=MAX($A$1:$A$32)". That will fix the range to A1:A32. Whenever you call for ANA in a formula, it will give you the highest value in that range.
Yes sorry, I forgot to put the dollar signs, but that is not my problem. If I set ANA to =MAX($B$1:$B12), and then I click on cell C4 and in the name box I look for ANA. The value of ANA gets changed to C4.
I was successful in one Excel book, but whenever I try to do that again, it does not work.
- What are you trying to achieve exactly?
- You've actually defined a named *calculation* rather than a range name. Moreover, you've defined the calculated name with realtive references, meaning what cells are used by that name depends on two things: 1 Which cell was active when you defined the name and 2. Which cell you are using the name in.
Suppose you were in cell A33 when you defined the name, if you then use the name in B33 it will act as if it contains =MAX(B1:B32). If you use the name in D35, the name calculates as =MAX(D3:D34).
