Forum Discussion
Solved
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 bo...
- 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/
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).
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).