hi all..
i have excel formula this :at first, this formula is working, but after i closed the file then open again, the formula not working...
Please Login or Register to view this content.
after that...i delete formula in DV then copy again the formula is work...
how to fix it?
my target is cell N2 down (yellow areas)
any help much appreciated...
john m
This formula must be enter as an array formula by using Ctrl+Shift+Enter. Just make sure you save your file before you close it.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
You have to confirm the formula in DV with CTRL+SHIFT+ENTER.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
hi alkey....how to do it (Ctrl+Shitf+Enter) in DV ? cause i use in Data Validation|List
Same as you would normally - enter the formula in DV and then press CTRL+SHIFT+ENTER.
i have do it , Ali...still not work...mark {....} not showing in DV Formula Bar.
It won't show the {}, but it will work.
i don't know why?....in my computer still not working (after closed file DV not working)
It works here - what can I say?
Last edited by AliGW; 12-18-2017 at 01:18 PM.
FYI - It will lose array confirmation after save and re-opening workbook.
Easiest way to work around it is to create list on the worksheet (using array formula or other means) and then create dynamic named range off of it.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
hi ali...i just download your file...after open it.the formula is not working...
hi CK76....thanks...
but how to make it?
Correct - please see post #10. It works until the file is closed and reopened.
no...still not work...
It works for me
See attached file
Yes, it does work. However, as mentioned in post #10, as soon as you close the file, the array formula setting is lost, and you have to reapply it when you reopen the file.Originally Posted by Jhon Mustofa
i'm still confuse, why not working for me...i have do your suggestion...
Forget it - it's not going to be the way forward. Look at the advice in post #10 and go with that.
Removed by JT after reading post #10!
@ Jhon Mustofa
Have a read of following link about how to set up dependent data validation list.
http://www.contextures.com/xlDataVal02.html
If you have trouble applying the concept to your workbook, post your question and we can help.
hi CK76...thank your suggestion ...
If you are willing to have a VBA solution:
Click on tab==>View code==>Copy/Paste codePlease Login or Register to view this content.
Two named ranges PARM1 and PARM2 created and the above inserts values
In B2
=IFERROR(INDEX($AX$2:$AX$19,SMALL(IF(($AV$2:$AV$19=Parm1)*($AW$2:$AW$19=Parm2),ROW($AV$2:$AV$19)-ROW($AV$2)+1,""),ROWS($1:1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Creates named list called "DV_LIST"
in column N :DV
=List
Source: =DV_List
thank you John ....worked it well....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks