• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Capturing details with 1 input

Gaurang Mhatre

Gaurang Mhatre

Member
Hi Friends,

Need your help in this file.
I want to input the name of the client in E11 of snapshot sheet and the rest details should follow as per the data inputed in YTD sheets but the problem I am facing is account numbers. As there are 14 to 15 account numbers per client at times. How will I show them in drop down list as per the name inputed, since different clients got 2-4 account numbers. Also there are duplicate names of client since their products are different. So finding it difficult to do Vlookup as well since no unique value.

There are two separate sheets which contains YTD data. So how will I get data of a client for 2013-2014 and 2014-2015 In one snapshot sheet??

Is there a formula which will capture rest of the details by inputed client name??

Thanks for the help in advance

** The data contains dummy account numbers and names.

Regards,

GAURANG
 

Attachments

Hi Gaurang,

What if there are two product for same client? What product should come in Product of snapshot sheet and also which account number to be chosen? Just explain with a example.

Regards,
 
Hi Soumendra,

Thanks for the reply.

For example: if I type client name as “POWER FINANCE CORP LTD” so whatever details pertaining to this client should reflect in the boxes mentioned in snapshot sheet. if there are multiple details then it should come in drop down list. For example, power corp company got 2 products in 2013-2014 and in 2014-2015 it got 4 then it should come in drop down list.

Guess instead of putting name manually. We can make drop down list of clients in 2013-2014 and clients in 2014-2015 in such a way that if I add more clients in 2014-2015, client drop down list should get updated automatically. So now I can select client in snapshot sheet with help of drop down list and remaining formula should work as per the client selected.

Hope you got what i want to say.

Regards,
Gaurang
 
@Gaurang Mhatre

See the attached file. I had added Sheet1 plus some named range. I had worked only Client Name & Account No. if this is going right than we can work for others 2, but let me remind you all are array formulas and using volatile functions , so this may slow down your spreadsheet and further you will add more data plus more outputs which are still not fetched so more slow. If you are OK with VBA than tell, I will see how best I can help you. If you are OK with formula approach than tell whether the results are OK or not?

Regards,
 

Attachments

You must log in or register to reply here.
Back
Top