Forum Discussion
Defined names change when looking them up in the name box
- 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/
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.
To be able to find with the name box my desired value, so when I have a thousand results I can just get the cell where that value is.
- Aha, I get it. Suppose your values are in A1:A1000, then this formula in ANA should do it:
=OFFSET(Blad1!$A$1:$A$1000,MATCH(MAX(Blad1!$A$1:$A$1000),Blad1!$A$1:$A$1000,0)-1,0,1,1)
Now instead of clicking in the name box, typing ANA and hitting enter, press the F5 key, type ANA and click OK.I don't know if it's different in the Spanish version but I need semicolons to separate the parameters.
I register a new name AN refering to your formula.
When I press F5 and introduce AN it gives me this message, reference not valid. There are values on all cells, so I don't know what the problem might be.
PD: When you have a defined name refering to a cell, you can find it with the name box as well, so my problem is if this is possible and I'm doing it wrong rather than using the name box.