Listbox based on a criteria

Hello everyone, I have a table1 (see attch.) and a "form" to someone to fill.
Then I want to fill the "account" and based on the account choosed I display certain values to field "Value". I'm planning making this on VBA but im not with a clear mind on how doing this...could please someone help me out?
If you have any doubts regarding the excel file, please tell me and I will post the answer ASAP.
Thanks all!
example1.xlsx

sounds like you are after dependant DV?

http://www.contextures.com/xlDataVal02.html

Originally Posted by humdingaling

sounds like you are after dependant DV?

http://www.contextures.com/xlDataVal02.html

That's exactly what I need, but, I want something more dinamic, for example, If I a add a new "account", the named range will be automatically resized....can I resize name-range in VBA??
And thanks for the answer, I really appreciate it!!! * Only if it's simple, if it's not, just leave the problem..hehe, dont need to burn your brain.

it can be done, a little more complicated but not much

put comments into the spreadsheet so hope you understand what i have done

basically named range "Accounts" now increases/decreases according to the COUNTA in column L...up to 100...extend if you need more..decrease if you don't want so long

table 1 needs to be updated to match subsequent additions in accounts

Originally Posted by humdingaling

it can be done, a little more complicated but not much

put comments into the spreadsheet so hope you understand what i have done

basically named range "Accounts" now increases/decreases according to the COUNTA in column L...up to 100...extend if you need more..decrease if you don't want so long

table 1 needs to be updated to match subsequent additions in accounts

Thanks for you answer!
I have one question,
I've added 'QQ' to the table1 and a value to it, shouldn't in the Value of the Expected output if i selected 'QQ' should appear the value if put on table1? nothing is happening...sorry if i misunderstood.

Sorry forgot to add offset named range which would automatically add the DV

the formula behind it is

Formula:

Please Login or Register  to view this content.

its a bit scary to begin with but breaks down rather easily

basically
from B3 (start of your table)
find the first matching account (then minus 1)
offset to the right 1 column
then the countif counts how long you want the DV to be...which is the number of accounts

adjust the match length and Countif length array if its too long or short

now....you must sort the table to have all the same accounts in a row
its quite clear in the example i have highlighted